SSRS & SSAS #63 – Stop SSRS 2008 MDX Query Designer from Overwriting Parameter Queries
In a previous blog, SSRS & SSAS #61 – SSRS and SSAS Integration: how bad it is and our hope with two types of data providers, I complained about how bad the integration between Reporting Services and Analysis Services is.
Complain itself will not solve any issue. But it drove me to focus on the frustration and research solutions or workarounds.
Issue – The MDX query designer in SSRS 2008 overwrites my parameter queries
When using Analysis Services as data source, the query designer is a very attractive tool for assembling MDX queries. When I decide to use parameters, it even creates the queries for me for the parameters. All is good, except when I found out that my customized parameter queries got overwritten over and over again.
There are many reasons why I write customized parameter queries. Here are a few:
1. The least reason is to format my MDX queries for better readability. If you have read some machine-generated MDX queries, such as queries from Excel, ProClarity, SSRS, you would not blame me for spending some extra time for better readability.
2. The auto-generated parameter queries most likely will retrieve the top level member ALL for the hierarchy along with children members. I personally do not like this feature. I often modify the parameter queries to exclude the ALL member.
3. To implement cascading parameters, the auto-generated parameter queries must be modified.
Unfortunately, if I open the main data set again in the MDX query designer, all the customized parameter queries will be overwritten.
A Workaround – Add a tag SuppressAutoUpdate = true to the RDL file
There are very few books on the integration between SSRS and SSAS, even fewer resources on solutions to the issues we face. Thanks to some dedicated bloggers, I found the following articles online regarding this annoying issue:
- Prevent SSRS 2008 from Overwriting Datasets
- Stop Reporting Services (SSRS) 2008 from overwriting custom Parameter Datasets
This link, MDX Query Designer Overwrites Parameter Queries, is actually a bug report on Microsoft Connect. It seems that this new issue in SSRS 2008 had never got fixed. I could be wrong on this. It will be appreciated if anybody can let me know if this has been fixed in later release of SSRS 2008.
It turned out that the workaround to preserve my changes to the parameters queries is to modify the report’s RDL source code as follows by adding <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> between the tag <Query> and </Query>:
Here are some simple steps to accomplish this:
1. Right click the report in BIDS 2008, and select View Code.
2. Search string </DataSet> in the RDL/XML file.
Then add <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> to just before </Query>.
3. Repeat step 2 for every data set (or only the data sets you do not want to be updated).
Default values for parameters still need to be manually removed
The above workaround prevents SSRS 2008 from overwriting my parameters queries, but still leaves the Default Values for my parameters.
This is actually not a bad thing for testing purpose because it can save me many mouse-clicking to select parameters. When I am ready to send my report to the report server, I can just open the property window for my parameters, and change to No default value on the Default Values tab.
So I guess I will keep complaining about SSRS-SSAS-MDX till I can find either workarounds or solutions.