Search

MDX Query Designer Overwrites Parameter Queries by Teo Lachev

Closed
as Fixed Help for as Fixed

8
0
Sign in
 to vote
Type: Bug
ID: 417209
Opened: 2/23/2009 6:44:20 AM
Access Restriction: Public
4
Workaround(s)
6
User(s) can reproduce this bug
Two issues with the MDX Query Designer.
1. After switching to MDX mode, making and saving changes to the report query, the MDX Query Designer overwrites the parameter datasets and wipes out all changes made to these parameter datasets.
2. Reporting Services treats MDX parameters as cascading parameters and always refreshes the report when a parameter value is changed although the parameters are not dependant on each other (there are not cascading parameters).
Details (expand)
Product Language
English

Version

SQL Server 2008 - Developer Edition

Category

Reporting Services

Operating System

Windows XP SP2 Professional
Operating System Language
US English
Steps to Reproduce
1. Open the attached report in Report Builder 2.0 or BIDS Report Designer. Right-click DataSource1 and click Show Hidden Datasets to see the parameter datasets.
2. Notice that some changes are made to the parameter datasets. For example, in the ProductCategory dataset, [Product].[Category].[Category].ALLMEMBERS is requested on ROWS to avoid the All member.
3. Open the DataSet1 in MDX Query Designer and notice that in opens in MDX mode because manual changes are made to the query (a custom calculated member is defined).
4. Make a small change to the query, such as deleting a whitespace and save the query.
5. Open the ProductCategory dataset and notice that its query has been rewritten and the [Product].[Category].ALLMEMBERS is now used on ROWS.

As far as the second issue, notice that when you preview the report and change Date.Calendar Year parameter, the Category parameter is refreshed although it doesn't depend on the first parameter and it's configured as Never Refresh.
Actual Results
See above.
Expected Results
1. The MDX Query Designer shouldn't overwrite a parameter dataset if it exists. This issue is really annoying as the report author must make all manual changes to all parameter datasets after the main dataset is updated.
2. When parameters don't depend on each other, they should not be treated as cascading parameters. The Never Refresh option should actually work.

Platform

32
File Attachments
File Name Submitted By Submitted On File Size  
parameters.rdl (restricted) Teo Lachev 2/23/2009 -
Sign in to post a comment.
Posted by Whuppi on 8/10/2010 at 5:22 AM
Is the first problem solved? Please give feedback, in which version this problem is solved. It drives my customer crazy, if the hidden datasets are overwritten.

Best regards,
Nils Rottgardt
Posted by DipenParekh on 5/24/2010 at 7:57 AM
Is the second issue of parameters being treated as cascading, resolved in SQL Server 2008 R2 ?
Posted by jdxhor on 11/13/2009 at 12:06 PM
Tested this in the October CTP of Report Builder 3.0, this is still an issue.
Posted by Microsoft on 8/12/2009 at 3:43 PM
The issue regarding MDX parameter dependencies has been resolved as By Design by the product team. The current behavior cannot be changed in this release without introducing backward compatibility issues. However, we are considering changes in a future release that would enable direct control over parameter dependencies.
Posted by Microsoft on 5/22/2009 at 12:36 PM
Also, the workaround to preserve changes made to a parameter dataset is to add the following tag to the RDL:
<Query>
...
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
</Query>
Posted by Microsoft on 5/22/2009 at 12:35 PM
The issue of overwriting user-modified paramter datasets has been fixed in the next release of SQL Server. I filed a separate bug internally to track the issue of MDX parameters being treated as dependent when they are not.
Posted by Microsoft on 2/23/2009 at 11:59 AM
Thanks for the feedback on this, Teo, especially for the clear descriptions and repro. We are looking into the issue.
Sign in to post a workaround.
Posted by Denesh Pohar on 5/19/2009 at 12:52 PM
There is a workaround to address the issue of overwriting the query, but it involves manually editing the RDL. Include the following tag in your RDL DataSet's Query tag...


<Query>
...
<rd:AutoGenerated>true</rd:AutoGenerated>
</Query>
Posted by Denesh Pohar on 5/19/2009 at 1:03 PM
Typo in the workaround -- should have been the following:

<Query>
...
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
</Query>
Posted by AlexCooke on 2/17/2011 at 3:16 AM
If you don't want to manually modify the RDL file, you can use the following workaround:

When you make changes to an automatically generated parameter dataset that (such as adding a filter), it is possible to 'lock' these changes in place by simply executing the parameter dataset within the Query Designer. This effectively adds the following tag

<rd:AutoGenerated>false</rd:AutoGenerated>

to the parameter dataset, which prevents it from being overwritten.
Posted by AlexCooke on 2/17/2011 at 3:20 AM
Clarification:

I stated that the workaround adds this tag:

<rd:AutoGenerated>false</rd:AutoGenerated>

In reality, it simply removes this tag:

<rd:AutoGenerated>true</rd:AutoGenerated>

The effect is the same!
© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement