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!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s