Problems installing SSRS on web server

Hi gang -
I've just recently begun playing around with SQL Server Reporting
Services (SSRS), and I was blown away by it. I had no problem
installing it to my development machine (XP Pro, IIS 6, SQL Server 2000
Dev Ed), but I'm having a lot of trouble setting it up in the
production environment.
I am incredibly frustrated by the documentation on this, and from what
I've seen, no one else has had this problem.
When I'm attempting to install to the first WEB SERVER(IIS 5.0), I get
a warning message during the install that I don't have the Visual
Studio IDE installed (which shouldn't be a problem--I mean, hey, it's a
server, right?). Fine.
The next page or so is the list of which components you want to
install--and Tada, NO SERVER COMPONENTS are in the list. The first time
this happened, I shrugged, and went through the rest of the set up
process. I didn't recieve any of the other setup pages (virtual
directories, SQL Server connection info, etc). It ended up installing a
handful of other dlls (everything under the client components branch
was available for install).
Stumped? I am.
Here's the last little bit of info: I'm attempting to install this
software over a terminal connection. The servers are co-located, and I
was hopeing to install this without taking the neccessary road trip. I
doubt this is the problem, but hey, you never know.
If anyone else has experienced this problem and has a resolution,
please, please let me know.
Thanks much,
Mark S. Milley, MCADI have found a post that had a similar problem until they upgrade to
SP4 on the web server. I'm going to see if that corrects the problem.
In addition, there is no Default Web Site on the server, however, my
symptoms so far are different than what others have experienced. I'll
keep you posted...|||Ignore warning about VS, that is only for development tools, not production.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Milley - BinarySwitch" <mark.milley@.binaryswitch.com> wrote in message
news:1126879087.836704.12350@.z14g2000cwz.googlegroups.com...
> Hi gang -
> I've just recently begun playing around with SQL Server Reporting
> Services (SSRS), and I was blown away by it. I had no problem
> installing it to my development machine (XP Pro, IIS 6, SQL Server 2000
> Dev Ed), but I'm having a lot of trouble setting it up in the
> production environment.
> I am incredibly frustrated by the documentation on this, and from what
> I've seen, no one else has had this problem.
>
> When I'm attempting to install to the first WEB SERVER(IIS 5.0), I get
> a warning message during the install that I don't have the Visual
> Studio IDE installed (which shouldn't be a problem--I mean, hey, it's a
> server, right?). Fine.
> The next page or so is the list of which components you want to
> install--and Tada, NO SERVER COMPONENTS are in the list. The first time
> this happened, I shrugged, and went through the rest of the set up
> process. I didn't recieve any of the other setup pages (virtual
> directories, SQL Server connection info, etc). It ended up installing a
> handful of other dlls (everything under the client components branch
> was available for install).
> Stumped? I am.
> Here's the last little bit of info: I'm attempting to install this
> software over a terminal connection. The servers are co-located, and I
> was hopeing to install this without taking the neccessary road trip. I
> doubt this is the problem, but hey, you never know.
> If anyone else has experienced this problem and has a resolution,
> please, please let me know.
> Thanks much,
> Mark S. Milley, MCAD
>

Problems deploying to a different server

I've been playing with Reporting Services on my computer and got it working fine. I can view the reports on my localhost with no problems. However, when I change the TargetServerURL in the report properties to the server that I want the report to go on (yes, the target server has SQL Reporting Services installed on it), I get the following error message:

Server was unable to process request. > Method ReportingService.SetExecutionOptions can not be reflected. > Unknown error - HRESULT 0x80131047

Anyone have any idea of what's going wrong here? Thanks for any help.

Just to add: I do have rights to the server I'm trying to publish to. This is assuming that SQL Reporting Services is distributing the reports under my username. It could well be that it's trying to use the generic ASPNET user that Visual Studio has a habit of using for its web publishing. Anyone know for sure?Problem solved. Silly mistake on my part. I deployed Framework 2.0 beta to that server and by default it set the folders used by Report Services to use ASP.NET 2.0 which my Visual Studio 2003 didn't like, so I set the folders to use ASP.NET 1.1 and all is good.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