Hi All,

While working with SSRS reports for displaying some financial data; many a times we have to display data for particular time-frame. Or most commonly – last 12 month’s data.

I am talking about the case where you are pulling data from SSAS Cube using MDX queries. In this particular example, I need to display SSRS report for last 12 month’s data from SSAS Cube. So I need to provide 2 things: StartDate and EndDate.

But as the default expected behavior of the Report requires EndDate to be today’s date so we have to dynamically configure today’s date in EndDate parameter. We also have to determine the date of 12 month’s back to today’s date for StartDate parameter.

“I had a funny incident when I was taking interviews for BI developers. I asked candidate to find me a previous month and year based on given month name. E.g. if I tell him Dec 2012 then the answer I am expecting would be Nov 2012. So simple to answer for a human but it’s not possible to determine just based on month name or based on month & year name 🙂 if you would have noticed/analyzed how DimTime dimension is processed, you could easily understand that we can’t determine the year name just based on month name. It requires year name to make that month unique in the list of month-year name. And moreover to go back in time, just month-year names are not enough as DateTime functions requires a full date to operate upon it.”

So far, this is simple understanding, but the ways of implementation makes this easier for further modifications and future maintenances.

To get the last 12 month’s data using MDX, we need to parameterize StartDate and EndDate of result set. This can be achieved in many ways … by modifying MDX query itself to take corresponding dynamic StartDate and EndDate or using VB Script on SSRS front.

Following are the most commonly used methods:
•    While generating DataSet (MDX Query Generation using Query Designer) in SSRS, we can modify the MDX query in Query Mode by playing with different date functions provided.
•    Using VB Script to get parameter values and pass it to MDX query of DataSet.

Here I would talk on second option, which is easy to implement & easy to modify.

Step – 1
In DataSet generation -> MDX Query Designer -> Drag-Drop fields which you need to make it parameterized. In our case, its StartDate and EndDate. So you can Drag-Drop the Time hierarchy and choose “Range (Inclusive In)” clause in Operator. Provide the Start and End range from available values (These are just default values). And at the right most side, put a tick mark for making them as parameterized.

You can click Ok and exit the Query Designer dialog box.

Step – 2
You will notice that designer has created 2 parameters in “Parameters” section in “Report Data” Pane.

Double click on first parameter and select “Available Values” tab in “Report Parameter Properties” dialog box. You will see a DataSet is generated for you and Value and Label mapping is also done automatically.

Go to “Default Values” tab. You will see the “Default” value being passed to the Report in “Value” section. Click on Expression button to see its value expression.

Note: Here you may see different value expression than others (shown by people on internet) because this depends on the Time hierarchy structure.

For my case, it shows: [DimTime].[Time].&[2012-03-01 T00:00:00]&[2012]

So the dynamic string will be :

=”[DimTime].[Time].&[” + IIF(Month(Now())=12,CStr(Year(Now())),CStr(Year(Now())-1)) + “-”  + Switch(Month(Now())=1,”02″,Month(Now())=2,”03″,Month(Now())=3,”04″,
Month(Now())=4,”05″,Month(Now())=5,”06″,Month(Now())=6,”07″,Month(Now())=7,”08″,Month(Now())=8,”09″,
Month(Now())=9,”10″,Month(Now())=10,”11″,Month(Now())=11,”12″,Month(Now())=12,”01″) + “-01T00:00:00]&[” + IIF(Month(Now())=12,CStr(Year(Now())),CStr(Year(Now())-1))  + “]”

The above expression will become my StartDate default expression. And similarly the EndDate default expression would be like following:

=”[DimTime].[Time].&[” + CStr(Year(Now())) + “-” +
Switch(Month(Now())=1,”01″,Month(Now())=2,”02″,Month(Now())=3,”03″, Month(Now())=4,”04″,Month(Now())=5,”05″,Month(Now())=6,”06″,Month(Now())=7,”07″,Month(Now())=8,”08″, Month(Now())=9,”09″,Month(Now())=10,”10″,Month(Now())=11,”11″,Month(Now())=12,”12″) + “-01T00:00:00]&[” + CStr(Year(Now())) + “]”

By doing this, your SSRS report will show last 12 month’s data on first-time load.
I hope you will find this helpful.

Thanks, Khilit