Browsed by
Month: August 2012

SSRS: Using date pickers to filter SSAS cube data

SSRS: Using date pickers to filter SSAS cube data

When you have a report that pulls data from an SSAS cube, you have most likely used a date range filter on which time series you would like to display. It is not so straightforward though, to create date-parameters in SSRS and in your MDX query you refer to these queries. Because MDX needs to interpret the date values in a completely different syntax, we have to format the data. This post shows you how to accomplish this. This post does not describe how you should format the MDX to make this work, I assume that you have your dataset ready to go to use in your report.

The first step is to create two dimensions for the date range. The types of these parameters is “Date/Time”. There are no specific other settings for these parameters, as these are the date pickers that the end user can use to select a date range. So when you start the report, you will only have these parameters:

 

The next thing to do is create parameters that contain the formatted values that the MDX query can read. To accomplish this, create two more parameters called “FromDate” and “ToDate”, of type “Text”, and set the visibility on “Internal” (this causes the parameter to be hidden, but the values will change when the selection of other parameters change). On the “Available Values” tab, select “None”. On the “Default Values”, the following expression should be used:

=”[Order Date].[Date].&[” & Format(Parameters!FromDate.Value, “yyyy-MM-dd”) & “T00:00:00]”

The second parameter should contain this expression as the default value:

=”[Order Date].[Date].&[” & Format(Parameters!ToDate.Value, “yyyy-MM-dd”) & “T00:00:00]”

When you run the report now, you will see that the selected dates will be applied to your dataset. If not, you can always set the last two parameters to “Visible” and run the report. That way you will see how these parameters are formatted. Most of the errors that I got is that the descriptions of the cube dimensions were not completely correct.