SSAS/SSRS/MDX : Cascading parameters and M2M dimensions

Introduction

Well, today a post about cascading parameters in Reporting Services (SSRS) in combination with MDX coding. Cascading parameters is about defining multiple parameters in such a way that a list of values of  a parameter depends on the values of another parameter. For this post i'll build upon my lab environment as described in my earlier posts:

• Modeling a N:M relation between dimensions (part I).
• Building a multivalue dimension construction in SSAS ( part II).
• Joining issue with MDXing of M2M dimension (part III).

As a matter of fact, this post could be seen as a part IV in this list because the problem is also related with the M2M dimension construction. This is explained in my former post (part III).


Scenario
What i'll describe in this post is building a cascading parameter build upon two datasets: one for department and one for location. I've seen enough examples of building parameters on SQL queries but there are not that many examples based on MDX. As decribed in the earlier posts (see above) i've the following situation:

• Department A
• Location 1.
• Location 2.
• Department B.
• Location 2.
• Department C.
• Location 3.
• Location 4.

So, the cascading parameter should show location 1 and 2 when the first parameter is department A.  Again,  when department C is chosen the other parameter should be limited to location 3 and 4.

Building the report 
First, let's setup the report in Reporting Services that returns a department, a location and a count field. I've created a datasource in SSRS, built a dataset and added the two parameters: Department and Location.

After I pressed OK and returned back to the report i noticed that there are a couple of extra (hidden) datasets created(DepartmentDepartmentDescription and LocationLocationDescription). Dataset1 is MDX query that should return data for the report.

Let's try the report and check if the cascading parameter location is depending on the department parameter. Look at the diagram below and notice that all four locations are shown, even when i choosed a different departments.

Seems that we've to add some more magic to the report. Let's look at the parameter datasets in more detail. I've copied the MDX query of the department dataset below:

WITH
MEMBER [Measures].[ParameterCaption] AS [Department].[Department Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Department].[Department Description].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Department].[Department Description].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS ,
[Department].[Department Description].ALLMEMBERS ON ROWS FROM [MultiValueDimension]

No need for changes here. Let's take a look at the location dataset.

WITH 
MEMBER [Measures].[ParameterCaption] AS [Location].[Location Description].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Location].[Location Description].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Location].[Location Description].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {[Measures].[ParameterCaption], 
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]} ON COLUMNS , 
[Location].[Location Description].ALLMEMBERS ON ROWS 
FROM ( SELECT ( STRTOSET(@DepartmentDepartmentDescription, CONSTRAINED) ) ON COLUMNS 
       FROM [MultiValueDimension])

The problem (as i described in my former post about the showing the right location related at the right department) is that all locations are shown because the relation between location and department isn't enforced. So i need to change the query and two important changes are needed.

First change the WITH part:

WITH 
MEMBER [Measures].[ParameterCaption] AS [Location].[Location Description].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Location].[Location Description].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Location].[Location Description].CURRENTMEMBER.LEVEL.ORDINAL

into:

WITH
MEMBER [Measures].[ParameterCaption] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.MEMBER_CAPTION)
MEMBER [Measures].[ParameterValue] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.UNIQUENAME)
MEMBER [Measures].[ParameterLevel] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.LEVEL.ORDINAL)

This part is enforcing the member to NULL when the relation is not there:

and change:

[Location].[Location Description].ALLMEMBERS ON ROWS 

into:

NONEMPTY([Location].[Location Description].ALLMEMBERS,[Measures].[Bridge Location Department Count])

This part is needed to suppress the NULLs but fon't forget to show all locations( for a specific department), even when there is no fact.This is important to notice because when i don't add this extra constraint location 4 isn't shown at Department C. This location doesn't have a factrecord stored.

And the final solution is shown below:

WITH
MEMBER [Measures].[ParameterCaption] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.MEMBER_CAPTION)
MEMBER [Measures].[ParameterValue] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.UNIQUENAME)
MEMBER [Measures].[ParameterLevel] AS iif(IsEmpty([Measures].[Bridge Location Department Count]), null, [Location].[Location Description].CURRENTMEMBER.LEVEL.ORDINAL)
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS ,
NONEMPTY([Location].[Location Description].ALLMEMBERS,[Measures].[Bridge Location Department Count]) ON ROWS
FROM (SELECT (STRTOSET(@DepartmentDepartmentDescription, CONSTRAINED) ON COLUMNS
              FROM [MultiValueDimension]))

The snippet 'FROM (SELECT (STRTOSET(@DepartmentDepartmentDescription, CONSTRAINED) ON COLUMNS FROM [MultiValueDimension]))' will change the location in a cascading parameter.

Now it's time to test this changed query. Starting up the report in preview mode is shown below (department A is showing the two locations 1 and 2):

Let's try another department (department C shows location 3 and location 4):

Conclusion
Main 'problem' is the usage of the M2M dimension (Location 2 Department) that is actually a n:m relation. To enforce the relation you have to use the second parameter of the NONEMPTY() function.

Greetz,
Hennie