SSRS #52 – Setting Dynamic Default Parameters in MDX Dataset

In my previous blog post, SSAS #21 – Steps to create a SSRS report with MDX accessing a Cube, I blogged the out-of-box features in SSRS to create a report with parameters accessing an Analysis Services database.

5 report parameters were automatically created.

When I preview the report, the Year Number shows the following available values, which include 0 (zero).

Goal

There are two things I’d like to change for the Year Number parameter.

1. Remove the value 0 (zero) from the available list.
2. Set a dynamic default value to the current year. 

Here is what I’d like to achieve;

 

Step 1 – Show Hidden Datasets

In my BIDS, the default is to hide all the MDX datasets that are automatically created. To show all the MDS datasets, right-click on the Analysis Services data source, and select Show Hidden Datasets (make sure the check mark is showing).

Now, all 5 MDX datasets are showing under the Analysis Services data source.

Step 2 – Add a Filter function for Year Number.

In order to remove the 0 from the list, I will need to use the Filter function.

To save some typing, I copied the automatically created MDX query to SSMS, and modified it with a Filter function.

This is the final MDX query with a Filter function.

In the Query Designer, I pasted my new MDX query, and tested it.

Step 3 – Set a dynamic default value to parameter Year Number

I’ll achieve this with Reporting Services expressions. However, with MDX datasets, I’ll need to use a combination of regular RS expressions and MDX expressions.

First, get to the Report Parameter Properties window.

On the Report Parameter Properties window, go to the Default Values tab. Check “Specify values”. Then click Add, and the function button .

In the Expression editor, type this:

="[DimDate].[Year Number].&["+CSTR(Year(Today))+"]"

You will need to replace [DimDate] with your own date/time dimension. [Year Number] is the one of the attributes in [DimDate]. You will need to replace it with your own too.

However, the Today function, the Year function, and the CSTR function are all regular RS functions.

You are done. Now preview your report, you will see that value 0 is removed from the available list, and 2011 is provided as the default value for parameter Year Number.