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).

Solving All Versus (Select All) SSRS End User Confusion

 

When developing reports that use Analysis Services as a data source end user can sometimes be confused about some of the options they see in report parameters.  Anytime you have a parameter that allows for multiple values to be selected then you will see a (Select All) option that Reporting Services adds that make for an easy way to check off each item in the parameter dropdown.  

If you followed most of the defaults when developing you dimensions in Analysis Services you likely also have an option called All (in my case All Customers) in the parameter dropdown. 

So (Select All) is provided by Reporting Services to simple check off each item, while the All option is provided by Analysis Services to check just that one value to represent all subordinates. 

This can be really confusing to end users so you may want to eliminate one of these to save yourself the explanation of what each really means.  To do this you will need to edit the dataset that was automatically created to populate the dropdown list.

·         Right-click on the SSAS data source and check off Show Hidden Datasets.  This will show the dataset used to populate the parameter dropdown.

·         Edit the hidden dataset by changing the highlighted MDX statement to say .Children instead of .AllMembers in the Query Designer.  Then hit OK twice.

·         Now when you run the report you only have the option for (Select All) without the additional All member.

Perfect!  Now you want to make sure (Select All) is select by default when your users view the report.

·         Open the parameter properties and change the Default Values page to use Get values from a query.

·         Select the dataset used for the parameter dropdown and then select ParameterValue (This field is automatically created everyone should have it) for the Value field.

Once you return back to your report you should see that the (Select All) option is set by default so all parameter values are displayed in the report.

Hope this helps!

Comments

Posted by Steve Jones on 15 March 2010

Nice tip! Good to know that's where the other "all" comes from in SSAS.

Posted by mark blakey on 16 March 2010

This is Slick, I got round the problem by setting a filter on the dataset to restrict the level. This is more elegant. Thanks for the tip

Posted by dbowlin on 16 March 2010

Simple.  Straight forward.  User friendly.  Those of us familiar with MDX should know this one, but reminders to keep things user friendly are great.

Thanks

Posted by l543123 on 16 March 2010

nice tip.. but isn't it better to select cubes "All" instead of selecting all individual values? I thought the "All" value makes the cube fetch data faster.

Posted by hennie7863 on 26 November 2014

Yes in my opinion too. It's better to pass ALL Customers to the Cube instead of selecting all items individually.

Leave a Comment

Please register or log in to leave a comment.