SSRS + MDX: Dynamic Dimension Attribute on Row Axis

Earlier this year, while building an SSRS report against a Cube data source, I got stuck trying to parameterize the dimension attribute on the rows axis of the dataset query. What I wanted was to provide the user with the ability to select the dimension (via an SSRS parameter) along which to calculate the measure.

My initial attempt consisted of building a dynamic MDX query and using a query-scoped named set and simply substituting in the dimension-parameter value at run time.

So starting with this:

click to zoom

And then adding a parameter like this:

click to zoom

The values for the pDimension parameter were unique names of the various dimensions I wanted the users to be able to choose from:

• [Date].[Calendar Year].[Calendar Year]
  
• [Product].[Category].[Category]

In theory, this sounds like it will work…and it does for [Date].[Calendar Year].[Calendar Year]…

…but then when I switch the Dimension parameter to [Product].[Category].[Category], i get the following:

This is because the Field Source for the dataset is hard-coded:

click to zoom

Here’s the full xml highlighted in the screenshot above (scroll to the end in red):

<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Date].[Calendar Year].[Calendar Year]" />

So when [Product].[Category].[Category] is selected, nothing resolves for the Field Source shown above because it’s looking for [Date].[Calendar year].[Calendar Year]…instead of [rows_axis].  This is displayed as a warning message in SSDT when executing the report with [Product].[Category].[Category] selected as the parameter:

Warning 1 [rsMissingFieldInDataSet] The dataset ‘ds_Attempt1’ contains a definition for the Field ‘rows_axis’. This field is missing from the returned result set from the data source. C:\Users\banton\Desktop\DynamicDimensionMDXReport\DynamicDimensionMDXReport\sample1.rdl

Warning 2 [rsErrorReadingDataSetField] The dataset ‘ds_Attempt1’ contains a definition for the Field ‘rows_axis’. The data extension returned an error during reading the field. There is no data for the field at position 1. C:\Users\banton\Desktop\DynamicDimensionMDXReport\DynamicDimensionMDXReport\sample1.rdl

I tried a few other tricks, but was unable to get anything working…so, for the sake of time, I ended up simply creating a separate sub-report for each dimension in the parameter list…adding each sub-report to the main report and only making the sub-report visible that corresponded to the dimension attribute selected in the parameter list of the main report.

The obvious downside is that you are now executing multiple queries against the cube…each subreport, regardless of whether it is actually displayed or not, is processed…so the overall load is increased.  But with SSRS caching, it shouldn’t be too much of an issue.

Fast forward a few months and this same question popped up in this thread on the MSDN forums. After sharing my workaround shown above, Gerhard Brueckl (b | t) chimed in offering up what is, in my opinion, a better workaround:

click to zoom

Building from the example in the first part of this post, let’s go ahead and implement Gerhard’s solution:

1. Open the dataset and create a separate field for each potential dimension attribute that can be selected from the report parameter.  In this case, we’ll add one for Calendar Year and Product Category:

   

   click to zoom

   <?xml version=”1.0″ encoding=”utf-8″?><Field xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xsi:type=”Level” UniqueName=”[Date].[Calendar Year].[Calendar Year]” />

   <?xml version=”1.0″ encoding=”utf-8″?><Field xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xsi:type=”Level” UniqueName=”[Product].[Category].[Category]” />

2. Now comes the tricky part…in the report you must use an expression everywhere you want to access this field.
   The expression uses the parameter label to indirectly select the valid field in the dataset field list (the other field is invalid and will throw an error if referenced at run-time): 
3. For field headers, axis labels, etc…you can simply reference the parameter label.

   

   click to zoom

And here’s the end result:

 

Note: you will still get warning messages for each field in the dataset field list corresponding to the dimension report parameter values that weren’t selected.

The reason I feel his solution is better is because it is more efficient and easier to maintain once implemented.  It’s more efficient because we are only executing a single query at run-time…instead of 1 query per subreport.  And the maintenance is easier because all we have to do to add more dimension attributes is to add a new field to the dataset field list…where as in my solution, you would need to create a new subreport, create a new subreport reference in the parent report, and set the visibility accordingly.

Kudos to Gerhard for a nice solution to an interesting problem.