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!



Subscribe to this blog
Briefcase
Print
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.