Hi all, I am having hell of a time getting cascading parameters to work when I use a cube as a datasource I have a simple Calendar Hierarchy. User selects Year and then gets month in that year from a second parameter. I have the 2 Parameters working, ie change the year and the second parameter changes to reflect the new Year I created the 2 parameters from the design screen of the main reports dataset (ds_Report). Trouble is the parameters are not working when I run the report. No matter what I select in the Parameters it does not effect the report. Looking at the MDX in SQL Profiler there is no sign of the 2 parameters being passed. What am I missing ? Any ideas anyone ? TIA

Hi LuckyJim1001, Can you please post the SSRS generated MDX query here (by going into the query mode within the Query Designer)? That would be the best possible way to identify the problem. Cheers, IceQBPlease mark correct answers :)

Hi LuckyJim1001, Actually, we can build cascading parameters by passing the first parameter value to the second parameter dataset. To your issue, it seems you give a incorrect steps for your cascading parameter. Below is a sample MDX for cascading parameter, you can get a reference: 1st parameter: MEMBER [Measures].[StateUN] AS [Location].[Location Hierarchy].CurrentMember.Member_Unique_Name SELECT { [Measures].[StateUN], [Measures].[Total Amount] } ON COLUMNS, { NONEMPTY([Location].[Location Hierarchy].[State].Members, [Measures].[Total Amount]) } ON ROWS FROM [Combined Programs] CELL PROPERTIES VALUE 2nd parameter: WITH MEMBER [Measures].[ElectorateUN] AS [Electorate].[Electorate].CurrentMember.Member_Unique_Name SELECT { [Measures].[ElectorateUN], [Measures].[Total Amount] } ON COLUMNS, { NONEMPTY([Electorate].[Electorate].[Electorate].Members, [Measures].[Total Amount]) } ON ROWS FROM ( SELECT {STRTOMEMBER(@State)} ON COLUMNS FROM [Combined Programs] ) For more information about How to implement cascading parameters in a SQL Services Reporting Services MDX Report, pleas see: http://agilebi.com/ddarden/2008/05/18/how-to-implement-cascading-parameters-in-a-sql-services-reporting-services-mdx-report/ Thanks, Challen FuPlease remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi all Thanks for the replies. Not working afraid. I will post my MDX here in case anyone would be nice enough to have a look. 1st Parameter: WITH MEMBER [Measures].[ParameterCaption] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [dim_CFO_Calendar].[Calendar - CFO].CHILDREN ON ROWS FROM [cube_SalesAndDiscount_Review_Weekly] 2nd Parameter: WITH MEMBER [Measures].[ParameterCaption] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.LEVEL.ORDINAL' SELECT { [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , DESCENDANTS(STRTOMEMBER(@prmYears) ) ON ROWS FROM [cube_SalesAndDiscount_Review_Weekly] These 2 query parameters work fine when I run them in BIDS. And finallly the main dataset query: SELECT NON EMPTY { [Measures].[TY-Gross Profit] } ON COLUMNS FROM [cube_SalesAndDiscount_Review_Weekly] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS So I was hoping to see the TY-Gross Profit value change as I selected different dates. But no joy. As said before it looks to me like the parameters are not being passed to the query. Many thanks.

Hi all Thanks for the replies. Not working afraid. I will post my MDX here in case anyone would be nice enough to have a look. 1st Parameter: WITH MEMBER [Measures].[ParameterCaption] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [dim_CFO_Calendar].[Calendar - CFO].CHILDREN ON ROWS FROM [cube_SalesAndDiscount_Review_Weekly] 2nd Parameter: WITH MEMBER [Measures].[ParameterCaption] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[dim_CFO_Calendar].[Calendar - CFO].CURRENTMEMBER.LEVEL.ORDINAL' SELECT { [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , DESCENDANTS(STRTOMEMBER(@prmYears) ) ON ROWS FROM [cube_SalesAndDiscount_Review_Weekly] These 2 query parameters work fine when I run them in BIDS. And finallly the main dataset query: SELECT NON EMPTY { [Measures].[TY-Gross Profit] } ON COLUMNS FROM [cube_SalesAndDiscount_Review_Weekly] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS So I was hoping to see the TY-Gross Profit value change as I selected different dates. But no joy. As said before it looks to me like the parameters are not being passed to the query. Many thanks.

Okay got this to work. I created a parameter on the report dataset Data tab via the UI not the MDX screen Then I edited the data set BIDS created to use the Years parameter. If I really knew what i was doing I could have just edited the report dataset MDX.