Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

SSRS - Setting Default Parameter Values for Analysis Services Datasets

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. 

Step One

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.

image

Step Two

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.

image

Comments

Posted by Lempster on 3 November 2009

Excellent! Thanks for this - I'd been wondering how to do precisely what you've demonstrated.

Posted by knight_devin@hotmail.com on 6 November 2009

Fantastic!  I'm glad it was helpful.

Posted by kaypeegun on 5 January 2010

Thanks for this realy help full

Leave a Comment

Please register or log in to leave a comment.