Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Microsoft Business Intelligence and Data Warehousing

Garrett Edmondson, Independent Consultant MCITP & MCTS: 10+ years experience with BI stack.

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.

image

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

image

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

image

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.

image

Note: this report used the AdventureWorksDW2008R database

Download the Report Project shown above.


Comments

Leave a comment on the original post [garrettedmondson.wordpress.com, opens in a new window]

Loading comments...