SSRS Parameter Passing Issue

0
votes

We're using SSRS within an application where users login and are authenticated based on their Store. When they generate reports, we pass their store name to SSRS to generate report for their store. Everything works fine on the first report generation. But when the user picks a different date range, they get report for all of the stores in the system. We use MDX to feed the default and available parameter for store name in the .rdl as follows:

SELECT NON EMPTY { [Measures].[MTDActiveMembers], [Measures].[YTDActiveMembers],
 [Measures].[MTDMembers], [Measures].[YTDMembers], [Measures].[Members], 
[Measures].[Members-Active] } 
ON COLUMNS, NON EMPTY { ([Store].[Store].[Store].ALLMEMBERS ) } DIMENSION 
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( 
STRTOSET(@StoreStore, CONSTRAINED) ) ON COLUMNS 
FROM ( SELECT ( STRTOMEMBER(@FromCalendarDateHierarchy, CONSTRAINED) : 
STRTOMEMBER(@ToCalendarDateHierarchy, CONSTRAINED) ) ON COLUMNS FROM [Members])) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, 
FONT_NAME, FONT_SIZE, FONT_FLAGS

Stepping through the app revealed that when the user picks a different date range, all the parameters are intact, correct store name is preserved, but when the user hits "View Report" parameter for store name changes to ALL. :w00t: What could be wrong? As far as app is concerned "View Report" is a black box, so don't know what to change with the rdl :( Thanks in advance for your input.

--Edited Sorry, the query I provided above is the actual report query. Parameter query is this: WITH MEMBER [Measures].[ParameterCaption] AS [Store].[Store].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Store].[Store].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Store].[Store].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Store].[Store].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromCalendarDateHierarchy, CONSTRAINED) : STRTOMEMBER(@ToCalendarDateHierarchy, CONSTRAINED) ) ON COLUMNS FROM [Members])

2
sqlreporting-servicesrdl

2 Answers

0
votes

Hi Instead of returning really all members you need to change following line :

 NON EMPTY { ([Store].[Store].[Store].ALLMEMBERS ) }

To:

 NON EMPTY { [Store].[Store].[Store] }

It will return to you only those which have values.

0
votes

The problem should be that when the Date is modified all the parameters below this are updated, you must check if the Store parameter is below the Date parameter: enter image description here

Move the Store parameter above the Date with the arrow Up enter image description here

I hope this helps