January 19, 2010 at 5:16 am
Hi Folks,
My query is like below:
SELECT
{STRTOSET('[Dim Time].[Week Of Year].&[49]') ,
STRTOSET('[Dim Time].[Week Of Year].&[48]')} ON Columns,
[Dim Stores].[Store Name].AllMembers on rows
FROM
[HCRetailSales]
When I copy and paste it to the SSRS query designer
SELECT
{STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater]') ,
STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater].PrevMember')} ON Columns,
{[Dim Stores].[Store Name].allmembers } on rows
FROM
[HCRetailSales]
Then I retrieve nothing. I have also attached screenshots for both queries
Any Idea?
January 19, 2010 at 8:10 pm
Firstly, Reporting Services wants to have measures on columns and all other information required to be shown on the report needs to be rows. This is part of the underlying design of the way SSRS deals with all datasets - all report grouping, pivoting, summation etc is down during the reporting rendering.
So, try changing your MDX to something like
SELECT ([Measures].[Your Measure Name Goes Here] } ON Columns,
{STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater]') ,
STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater].PrevMember')}
*
{[Dim Stores].[Store Name].allmembers } on rows
FROM
[HCRetailSales]
January 21, 2010 at 12:29 am
Thank you very much for your reply.
I have tried to apply your code but I got the following error:
SELECT [[Measures].[Satis Miktari] ON Columns,
{STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater]') ,
STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater].PrevMember')}
*
{[Dim Stores].[Store Name].allmembers } on rows
FROM
[HCRetailSales]
---------------------------------------
Executing the query ...
Query (1, 8) The dimension '[[Measures]' was not found in the cube when the string, [[Measures].[Satis Miktari], was parsed.
Execution complete
January 22, 2010 at 9:50 pm
Try the following
SELECT { [Measures].[Satis Miktari] } ON Columns,
{STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater]') ,
STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater].PrevMember')}
*
{[Dim Stores].[Store Name].allmembers } on rows
FROM
[HCRetailSales]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply