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

With 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.

FromDateDate:

=”[Date].[Date].&[" &

Cstr(

Cstr(DatePart(DateInterval.Year,Parameters!FromDate.Value))

&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)))

)

& "]“

ToDateDate:

=”[Date].[Date].&[" &

Cstr(

Cstr(DatePart(DateInterval.Year,Parameters!ToDate.Value))

&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.