When developing Reporting Services reports that use Analysis Services as a data source you may find that it is difficult to dynamically set default parameter that are passed into the dataset being used. It turns out this is a lot simpler to do then many people think. In this post I’ll walk you through the steps of setting a default date value in a dataset’s parameter.
When creating a dataset that uses Analysis Services you will select Query Designer and then drag over the measures and dimension attributes that are needed for the report where it says “Drag levels or measures here to add to the query” . To add a parameter to the dataset you need to add a filter above where you just dragged over your report fields. In my example I am filtering by the year 2008. If I want to make this a parameter I just check Parameter box. Now when you hit OK and then OK again to return to your report you will have a new parameter added. When I made 2008 the Filter Expression it automatically sets that value as my default parameter value.
What I really want to do is setup this parameter so that it dynamically changes. In my report I want it to always show the current year by default in the parameter. So instead of 2008 the report should display 2009 and automatically change to 2010 on January 1st. You can do this using the SSRS expression language in the default value of the expression. The tricky part about it is that the default value of the parameter is using a MDX value. If you open the report parameter and select the Default Values tab you can change the value used for this parameter to use a SSRS expression so it dynamically changes to the current year no matter what the date is. The expression used for my example was =”[Year].[Year].&[“+CSTR(Year(Today))+”]”. This is using the Today function and only returning back the year using the Year function. Lastly it converts the value into a string.
This is a very basic example but I could use a similar expression if I had used a date hierarchy instead of just the year attribute. The key is to find out how the MDX that is being passed into the value looks like and then you can duplicate them using the SSRS expression language. Now every time I run the report It will have the current year populated by default in the parameter.