Problems inserting records into non dbo schema

I have a basic data flow which tries to insert data from an excel spreadsheet to a loading table (sql server 2005). I have created this table in a non dbo schema. I have used the schema owner as the sql server login for this loading step.

The problem is SSIS seems to throw a strange error when I do this:

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid.

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1073450982,0x,component "OLE DB Destination" (5195) failed the pre-execute phase and returned error code 0xC0202025.

When I create this table in the dbo it seems to work ok. I have tried giving the schema owner sa rights on the sql server and it still doesnt work. Im wondering if this is a known bug in ssis.

Does anyone have any ideas?This turned out to be a conflict between using Nvarchar(max) and Nvarchar(255). Even though the data would fit ssis didnt seem to like it.|||

Of course not. they're two different data types!

If I understand correctly varchar(max) is not the same as an infinitely long "normal" varchar. Or it helps to not think about it that way anyway.

I'm sure you'd get a better answer on the T-SQL forum

-Jamie

sql

Problems Creating Report

Hi,

I have only recently started playing around with Reporting Services (2005), and I am having some problems creating a basic report.

What I want to display is a table which shows product names in the first column, sales for a specific period this year in the second column, and sales for the same period last year in the third column. The 'specific period' is determined by specifying a start week and an end week which I have set up as parameters.

I have managed to produce the first 2 column with no trouble. However, for the third column I need to subtract 52 weeks from the parameters and use them as new parameter in the third column.

Can anyone offer any suggestions?

Dave

Sounds like a T-SQL issue. Do you have a sample of the query?|||My apologies, I just realised I neglected to mention that the source data was in a cube. What I have in the cube is a product dimension, a time dimension (which goes down to week level), and a measure for sales amount.|||Anybody got any ideas?|||

In MDX I would suggest using ParallelPeriod. The following is an AdventureWorks query showing an example. You just need to replace the expicit time members with parameters:

WITH
MEMBER [Measures].[Selection] as '
Sum(
[Date].[Calendar].[Month].&[2003]&[1]
:
[Date].[Calendar].[Month].&[2003]&[5]
, [Measures].[Sales Amount]
)'
MEMBER [Measures].[SamePeriodLastYear] as '
Sum(
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[1])
:
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[5])
, [Measures].[Sales Amount]
)'

SELECT
{ [Measures].[Selection]
, [Measures].[SamePeriodLastYear]
} ON 0,

NON EMPTY [Product].[Product Categories].Members ON 1

FROM [Adventure Works]

|||Thanks very much, that works a treat.|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Try constructing a MemberUniqueName string and passing that to the StrToMember functiuon

Sum(
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)

|||

Hi Adam,

Again, that works fine in SQL Studio with integer values instead of the parameters. However, when I try it in Reporting Services with my parameters I get the following error:

"The 'ThisYear' calculated member cannot be created because of the following error: Parser: The StartWeek parameter could not be resolved because it was referenced in ann inner subexpression"

My calculated member field is now:

"Sum(
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)"

|||

Some options:

try removing the CStr() call and see if that works
OR

Problems Creating Report

Hi,

I have only recently started playing around with Reporting Services (2005), and I am having some problems creating a basic report.

What I want to display is a table which shows product names in the first column, sales for a specific period this year in the second column, and sales for the same period last year in the third column. The 'specific period' is determined by specifying a start week and an end week which I have set up as parameters.

I have managed to produce the first 2 column with no trouble. However, for the third column I need to subtract 52 weeks from the parameters and use them as new parameter in the third column.

Can anyone offer any suggestions?

Dave

Sounds like a T-SQL issue. Do you have a sample of the query?|||My apologies, I just realised I neglected to mention that the source data was in a cube. What I have in the cube is a product dimension, a time dimension (which goes down to week level), and a measure for sales amount.|||Anybody got any ideas?|||

In MDX I would suggest using ParallelPeriod. The following is an AdventureWorks query showing an example. You just need to replace the expicit time members with parameters:

WITH
MEMBER [Measures].[Selection] as '
Sum(
[Date].[Calendar].[Month].&[2003]&[1]
:
[Date].[Calendar].[Month].&[2003]&[5]
, [Measures].[Sales Amount]
)'
MEMBER [Measures].[SamePeriodLastYear] as '
Sum(
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[1])
:
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[5])
, [Measures].[Sales Amount]
)'

SELECT
{ [Measures].[Selection]
, [Measures].[SamePeriodLastYear]
} ON 0,

NON EMPTY [Product].[Product Categories].Members ON 1

FROM [Adventure Works]

|||Thanks very much, that works a treat.|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Try constructing a MemberUniqueName string and passing that to the StrToMember functiuon

Sum(
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)

|||

Hi Adam,

Again, that works fine in SQL Studio with integer values instead of the parameters. However, when I try it in Reporting Services with my parameters I get the following error:

"The 'ThisYear' calculated member cannot be created because of the following error: Parser: The StartWeek parameter could not be resolved because it was referenced in ann inner subexpression"

My calculated member field is now:

"Sum(
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)"

|||

Some options:

try removing the CStr() call and see if that works
OR

Problems Creating Report

Hi,

I have only recently started playing around with Reporting Services (2005), and I am having some problems creating a basic report.

What I want to display is a table which shows product names in the first column, sales for a specific period this year in the second column, and sales for the same period last year in the third column. The 'specific period' is determined by specifying a start week and an end week which I have set up as parameters.

I have managed to produce the first 2 column with no trouble. However, for the third column I need to subtract 52 weeks from the parameters and use them as new parameter in the third column.

Can anyone offer any suggestions?

Dave

Sounds like a T-SQL issue. Do you have a sample of the query?|||My apologies, I just realised I neglected to mention that the source data was in a cube. What I have in the cube is a product dimension, a time dimension (which goes down to week level), and a measure for sales amount.|||Anybody got any ideas?|||

In MDX I would suggest using ParallelPeriod. The following is an AdventureWorks query showing an example. You just need to replace the expicit time members with parameters:

WITH
MEMBER [Measures].[Selection] as '
Sum(
[Date].[Calendar].[Month].&[2003]&[1]
:
[Date].[Calendar].[Month].&[2003]&[5]
, [Measures].[Sales Amount]
)'
MEMBER [Measures].[SamePeriodLastYear] as '
Sum(
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[1])
:
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[5])
, [Measures].[Sales Amount]
)'

SELECT
{ [Measures].[Selection]
, [Measures].[SamePeriodLastYear]
} ON 0,

NONEMPTY [Product].[Product Categories].MembersON 1

FROM [Adventure Works]

|||Thanks very much, that works a treat.|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Try constructing a MemberUniqueName string and passing that to the StrToMember functiuon

Sum(
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)

|||

Hi Adam,

Again, that works fine in SQL Studio with integer values instead of the parameters. However, when I try it in Reporting Services with my parameters I get the following error:

"The 'ThisYear' calculated member cannot be created because of the following error: Parser: The StartWeek parameter could not be resolved because it was referenced in ann inner subexpression"

My calculated member field is now:

"Sum(
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)"

|||

Some options:

try removing the CStr() call and see if that works
OR

Problems Creating Report

Hi,

I have only recently started playing around with Reporting Services (2005), and I am having some problems creating a basic report.

What I want to display is a table which shows product names in the first column, sales for a specific period this year in the second column, and sales for the same period last year in the third column. The 'specific period' is determined by specifying a start week and an end week which I have set up as parameters.

I have managed to produce the first 2 column with no trouble. However, for the third column I need to subtract 52 weeks from the parameters and use them as new parameter in the third column.

Can anyone offer any suggestions?

Dave

Sounds like a T-SQL issue. Do you have a sample of the query?|||My apologies, I just realised I neglected to mention that the source data was in a cube. What I have in the cube is a product dimension, a time dimension (which goes down to week level), and a measure for sales amount.|||Anybody got any ideas?|||

In MDX I would suggest using ParallelPeriod. The following is an AdventureWorks query showing an example. You just need to replace the expicit time members with parameters:

WITH
MEMBER [Measures].[Selection] as '
Sum(
[Date].[Calendar].[Month].&[2003]&[1]
:
[Date].[Calendar].[Month].&[2003]&[5]
, [Measures].[Sales Amount]
)'
MEMBER [Measures].[SamePeriodLastYear] as '
Sum(
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[1])
:
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].[Month].&[2003]&[5])
, [Measures].[Sales Amount]
)'

SELECT
{ [Measures].[Selection]
, [Measures].[SamePeriodLastYear]
} ON 0,

NON EMPTY [Product].[Product Categories].Members ON 1

FROM [Adventure Works]

|||Thanks very much, that works a treat.|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Ok,

I managed to get the MDX query working in SQL Studio but when I try to parameterize it in Reporting Services, I don't get any rows returned.

I have created 2 parameters (StartWeek + EndWeek) which are both integers, and have used these in 2 calculated fields. The code for these is:

ThisYear

Sum(
[Dim Week No].[Week No].&[@.StartWeek]
:
[Dim Week No].[Week No].&[@.EndWeek]
, [Measures].[Rate]
)

LastYear

Sum(
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.StartWeek] )
:
ParallelPeriod([Dim Week No].[Week in Year].[Year Name], 1,[Dim Week No].[Week in Year].[Week No].&[@.EndWeek] )
, [Measures].[Rate]
)

I have then just added these 2 fields, and the product field to my report's dataset. Could somebody please tell me where I am going wrong as this is driving me crazy!!

Dave

|||

Try constructing a MemberUniqueName string and passing that to the StrToMember functiuon

Sum(
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)

|||

Hi Adam,

Again, that works fine in SQL Studio with integer values instead of the parameters. However, when I try it in Reporting Services with my parameters I get the following error:

"The 'ThisYear' calculated member cannot be created because of the following error: Parser: The StartWeek parameter could not be resolved because it was referenced in ann inner subexpression"

My calculated member field is now:

"Sum(
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.StartWeek) + "] ", CONSTRAINED)
:
StrToMember("[Dim Week No].[Week in Year].[Week No].&[" + CStr(@.EndWeek) + "] ", CONSTRAINED)
, [Measures].[Rate]
)"

|||

Some options:

try removing the CStr() call and see if that works
ORsql

Problems connection the SQL Server database

I build a small application using VS 2005. The application just builds a report using basic Select statement from SQL Server db. Here is my problem:

When I tried to run the application using VS 2005 by pressing F5 . The applicaiton launches using VS webserver and everything test fine. But when I put the site in IIS created website . I get an error "Login failed for user '<serverName>\<userName>

In my connection string I have Integrated Security = SSPI

Please suggest.

Thanks

Thismight be the problem (if the IIS created website you mentioned is on another machine).

On your machine, the "user" that logs into SQL Server is MYMACHINE\ASPNET.

When you deploy to another machine, the "user" that is trying to log into SQL Server is OTHERMACHINE\ASPNET. Maybe the other machine isn't aware of that "user"