…brew your own Business Intelligence

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

click to zoom

And then adding a parameter like this:

click to zoom

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]…

InitialAttempt_PreviewCalendarYear

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

InitialAttempt_PreviewProductCategory

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

click to zoom

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

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

    click to zoom

    <?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]” />

    <?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=”[Product].[Category].[Category]” />

  2. Now comes the tricky part…in the report you must use an expression everywhere you want to access this field.Gerhard_ReportBody1
    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):Gerhard_ExpressionDetail 
  3. For field headers, axis labels, etc…you can simply reference the parameter label.
    click to zoom

    click to zoom

And here’s the end result:

Gerhard_CalendarYear

 

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.

12 thoughts on “SSRS + MDX: Dynamic Dimension Attribute on Row Axis

  1. Justin says:

    Very cool solution.

    I’ve done something similar but with a slightly different approach – I had a parm to change the axis specification and also defined each field in the dataset individually but I actually had each column defined in my table and then used visibility to only show the populated column. So Cal Year was empty and thus invisible but Product was populated and thus visible.

    1. Bill says:

      Hi Justin, that definitely works as well – thanks for sharing 🙂

  2. nicolas says:

    Many thanks, it sounds great. I’m just going to try to implement it …

  3. Pat York says:

    Thanks for this example – StackOverflow didn’t cut it on this one.

    Great method of implementing a dynamic Field Source in SSRS.

  4. Charles Young says:

    The above solution is exactly what I’m trying to accomplish, although being new to SSRS cube reporting, I get lost on the specific steps. Is there a way I can see the specific steps of how you set this up? I assume two datasets are involved, one to build the parameter list, and one for the display in the matrix. But for the first one, what does the query screen look like? In order to define the list of available values for the parameter, I need to select on field from the first dataset, but I’m being forced to specify one of my two fields from the first dataset. Confused…

  5. Javier says:

    Good idea! Thanks a lot!

  6. jiaxin says:

    What a smart solution it is!

  7. Damodar says:

    please give me a hint suppose if i want to show two columns(Calender Year and Category) wise sales amount how to modify the dataset

    1. Bill says:

      Hi Damodar,

      Its been a while since I’ve thought about this solution, but here’s a quick outline of how I’d approach it…

      – create a second parameter (i.e. pDimension1, pDimension2)
      – add logic so that the second parameter list excludes the attribute selected in the first parameter (e.g. you wouldn’t want CalendarYear x CalendarYear).
      – update the dynamically generated MDX query (second screenshot in the original post) to cross join the 2 selected dimension attributes
      – add a column to the tablix and set the expressions for column-header/column-details accordingly

      if you want to allow the second parameter to be blank, that adds a bit more complexity.

      Lastly, a much better solution is to simply use excel (or some other exploratory analysis tool).

      Hope this helps.

  8. Javier says:

    It sounds good, but it’s a limited solution. If you want that users “play” with the cube, you have to add each dimension as field. If you have several dimensions is a hard work, and if the cube change you have to modify the report again.

    The first one that I have in mind is passing the parameter to the XML. Do you know a way to pass the parameter into the XML? For example, something like that:

    =””

    Where “[Dim Product].[Size].[Size]” have to be the parameter. I tried but SSRS is taking the XML as string and it’s not working. Do you have any idea?

    Thank you!

    1. Bill says:

      Absolutely agree that this is a limited solution. If the goal is to enable the user to “play” with the cube, I’m typically going to hook them up with excel (pivot table) and call it a day. This particular blog post was based on a fairly specific client scenario and we certainly had the “excel is a better tool for this” conversation but they had a firm SSRS requirement.

      As to your suggestion about passing the parameter to the XML…assuming you’re referring to the XML in the field source value in the 5th screenshot…the answer is no, I’m not aware of a way to make that dynamic :-/

Leave a Reply