SQL Clone
SQLServerCentral is supported by Redgate
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!


No comments.

Leave a Comment

Please register or log in to leave a comment.