Issue with cascading multi-value parameters, Select all, All, Unknown (SSRS report on SSAS cube datasource)
As the title suggests I have got a problem with cascading multi-value parameters.
The problem occurs when creating a report in Report Builder 3.0 (SQL 2008 R2), with reports based on an SSAS cube datasource.
After creating a dataset with parameters, additional datasets are automatically created to query the parameter values. With a cube as the datasource, these values include an All member and an Unknown member. Using multi-value cascading parameters, this
gives the following problems:
- The 'Select all' option won't work --> error raised on executing the query of the next dependent parameter.
- Selecting both 'Unknown' and other values at the same time also raises an error on depending queries.
I have tried editing the queries for the parameter datasets, filtering out the All and Unknown members. This helped making the Select all option work again. However now I am unable to use the unknown members in a selection, which is preferred.
Additionally, having to edit the query for every multi-value parameter kind of takes down the whole idea that parameters are automatically added for the used datasets.
I still end up editing them all, and they are not exactly indented nicely.
But my question is: how can I both exclude the All member from my multi-value parameters and leave the Unknown member selectable without breaking the dependent cascading parameter/data queries?
May 24th, 2011 10:25am