Have you ever created parameterized SSRS reports on multidimensional cubes? If so, you may have noticed a small problem. Let’s first look at the MDX Query Designer for a simple report on the Adventure Works cube.
At the top are two parameters, based on the Country and City attribute hierarchies from the Location folder in the Customer dimension. So far so good – the query is returning results for the default values of United Kingdom and London in the lower pane. Country and City are query parameters. In SSRS, query parameters automatically generate report parameters, and these generated report parameters are what you see when you preview the report. It is the report parameters that are not quite right. The report parameters appear in the Report Data window in your report design and in drop-downs at the top of the report when it is previewed. The problem is illustrated in the next screenshot of the Country parameter drop-down.
The two entries at the top are for (Select All) and for All Customers – these essentially do the same thing, but the double entry is not necessary and may confuse end users. The data in the drop-down is from a hidden generated MDX query that is attached to the drop-down. You can see this by right-clicking on the Country parameter in the Report Data window in report design, choosing Parameter Properties then Available Values. The same is true for the City report parameter. This generated MDX is returning the All Customers entry as well as a list of individual countries (or individual cities for the City parameter). In addition, the report parameter has the Allow Multiple Values setting turned on. You can verify this by right-clicking the Country parameter and choosing Parameter Properties. The Allow Multiple Values setting is vital if the user wishes to select more than one country – but it has the side-effect of creating the (Select All) entry. To make life simpler for the user, we have to amend the hidden generated MDX so that it does not return All Customers.
To view this hidden MDX, right-click on DataSets in the Report Data window and choose Show Hidden Datasets. Two extra datasets will appear called CustomerCountry and CustomerCity. If you right-click on the CustomerCountry dataset and choose Query you will see the following MDX query (without the first comment line that I added).
-- MDX to populate a report parameter drop-down WITH MEMBER [Measures].[ParameterCaption] AS [Customer].[Country].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Customer].[Country].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Customer].[Country].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS, [Customer].[Country].ALLMEMBERS ON ROWS FROM [Adventure Works]
I have rearranged the code so it is more readable. The important line is [Customer].[Country].ALLMEMBERS ON ROWS. This has a two-part name before the AllMembers property function, composed of the dimension name followed by the hierarchy name. By default, AllMembers (or Members) returns the members of the hierarchy, including the All level at the top of the hierarchy. You can verify this by running the following query against the Adventure Works cube in SSMS.
-- two-part name [Dimension].[Hierarchy] SELECT {} ON COLUMNS, [Customer].[Country].ALLMEMBERS ON ROWS FROM [Adventure Works]
This returns these results, and you can see that the All level is returned – the All level name for the Customer dimension is All Customers.
Now try this next query and verify the results shown underneath.
-- three-part name [Dimension].[Hierarchy].[Level] SELECT {} ON COLUMNS, [Customer].[Country].[Country].ALLMEMBERS ON ROWS FROM [Adventure Works]
This time All Customers is not returned. The query is using a three-part name of the dimension name followed by the hierarchy name followed, in turn, by the level name. AllMembers on a level returns the members of the level and, in this case, not All Customers which is at the All level not at the Country level. Please note that the level name Country is the same as the hierarchy name – this is always the case for attribute hierarchies. All that’s required now is to amend the previously hidden query for the CustomerCountry report parameter to include a three part name. [Customer].[Country].ALLMEMBERS should be [Customer].[Country].[Country].ALLMEMBERS. The final query is show next.
WITH MEMBER [Measures].[ParameterCaption] AS [Customer].[Country].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Customer].[Country].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Customer].[Country].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS, [Customer].[Country].[Country].ALLMEMBERS ON ROWS FROM [Adventure Works]
And the final result is a drop-down that looks right.
You can then apply the same logic to the MDX query for the CustomerCity report parameter. The before and after queries are shown next.
WITH MEMBER [Measures].[ParameterCaption] AS [Customer].[City].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Customer].[City].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Customer].[City].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS, [Customer].[City].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@CustomerCountry, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works]) WITH MEMBER [Measures].[ParameterCaption] AS [Customer].[City].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Customer].[City].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Customer].[City].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS, [Customer].[City].[City].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@CustomerCountry, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works])
Here [Customer].[City].ALLMEMBERS ON ROWS has become [Customer].[City].[City].ALLMEMBERS ON ROWS. Notice the subquery references the CustomerCountry parameter as the CustomerCity is a cascading parameter – in other words, the cities displayed in the drop-down reflect the country/countries chosen in the CustomerCountry drop-down.
By Art Tennick
Art Tennick is a Microsoft Business Intelligence trainer, consultant and author based in the UK with over 15 years of BI exposure and the author of 20 published books and hundreds of magazine articles. To contact Art, email [email protected] or visit him on LinkedIn.
Have a comment? Let us know below, or send an email to [email protected]
