MDX SSRS Parameter Values

<rant> One of the most frustrating things about working with the Microsoft BI Stack is after you go through all of the hard work of developing an ETL, Relational Data Warehouse and Cube you are confronted with creating MDX SSRS reports.  Developing SQL SSRS reports can be frustrating but add to it working with MDX takes it to a new level. </rant>

imageWith that said the best MDX SSRS solution that I have seen is Grant Paisley’s Angry Koala Cube Browser. I highly recommend reading his book Microsoft SQL Server Reporting Services Recipes.

One of the challenging issues when developing MDX SSRS reports is passing parameter values to the MDX dataset. Although there several ways of doing this. Some which evolve modifying the MDX dataset, which can be undone by someone opening the Query Designer. The easiest way that I have found is described below and best of all it does not involve custom MDX.

For this example I will start with a Report that just has the following two ‘Date/Time’ parameters create.


Create a Data Source (Adventure Works Cube) and a MDX dataset. Make sure that you create a filter that is parameterized (range (inclusive)).


Select the Parameters page to edit the MDX dataset parameter value.


Configure the values to convert the values from the ‘Date/Time’ report parameters to match the member keys of the Date hierarchy which is the hierarchy in the dataset filter. The report parameters values are returned in the MM/DD/YYYY format which needs to be convert into the YYYYMMDD format which matches the format of the member key. Here is the code that will doe that.


=”[Date].[Date].&[” &
&iif(DatePart(DateInterval.Month,Parameters!FromDate.Value) < 10,Cstr(0)&Cstr(DatePart(DateInterval.Month,Parameters!FromDate.Value)),Cstr(DatePart(DateInterval.Month,Parameters!FromDate.Value)))
&iif(DatePart(DateInterval.Day,Parameters!FromDate.Value) < 10,Cstr(0)&Cstr(DatePart(DateInterval.Day,Parameters!FromDate.Value)),Cstr(DatePart(DateInterval.Day,Parameters!FromDate.Value)))
& “]”


=”[Date].[Date].&[” &
&iif(DatePart(DateInterval.Month,Parameters!ToDate.Value) < 10,Cstr(0)&Cstr(DatePart(DateInterval.Month,Parameters!ToDate.Value)),Cstr(DatePart(DateInterval.Month,Parameters!ToDate.Value)))
&iif(DatePart(DateInterval.Day,Parameters!ToDate.Value) < 10,Cstr(0)&Cstr(DatePart(DateInterval.Day,Parameters!ToDate.Value)),Cstr(DatePart(DateInterval.Day,Parameters!ToDate.Value)))
& “]”

Now you can run the report and enjoy.


Note: this report used the AdventureWorksDW2008R database

Download the Report Project shown above.


Tags: ,

One Response to “MDX SSRS Parameter Values”

  1. jan Says:

    this is good way. i have found that here:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: