Reporting: Setting the default value on generated parameters based on a cube

By | June 23, 2009

My users want their report to default select the last year and month available in the cube, most of the times this wont be the current month as the dataware house is behind. In a previous blog post  I talked about creating date time sets like current year, month, this way we can select the last month and year. I want to use these sets as default value.

I found a litte trick to make selecting the default parameter a walk in the park 🙂

I created my report as i would usually and created 2 parameters year and month, thus automaticly creating 2 datasets with all the data from year and month attributes. 

Now here comes the tricky part, i copied the mdx created by visual studio and added a filter using the set i have in my cube and created a new dataset called CurrentMonth. The MDX ends with “ON ROWS FROM [CUBE]”  and you can change it to “ON ROWS FROM ( SELECT ( [CurrentMonth] ) ON COLUMNS FROM [CUBE])” using the set which returns the current month. This new dataset returns the current month and the All dimension, i excluded the all dimension in a filter on the dataset.

Now you can use the new dataset to set the default value in the parameter by selecting “get values from a query” and selecting the new dataset and as value ParameterValue.

This will enable you to set the default value on default generated parameters.