SSRS – Overwriting MDX datasets for Parameter

In Report Builder, and I'm guessing Visual Studio, when you create a parameter from a OLAP source it creates the associated Dataset for you, great stuff, but here’s the annoying bit, If you modify the MDX in the dataset for this parameter it stores it fine, until you modify the dataset that uses this Parameter.

E.G. You have a query that looks at Sales, and you create a parameter called Financial Year (FY), the available values for financial year are automatically created for you based on the Dimension and the Hierarchy that you point it to (which at first is pretty cool). You modify the FY dataset, an example would be so that it only brings back the financial year, rather than all the members below it. The problem then occurs when you modify your MDX for your Sales dataset, you are still referencing your Parameter, when you OK after making any changes, it will recreate the Dataset for your FY parameter, overwriting any changes you have made.

I found this ultra annoying, and I'm sure many of you will. I’ve found a workaround (albeit a clunky one) and thought you might appreciate it:

So if you are making changes to your dataset, and you know it will affect your parameters, take the following steps to avoid your datasets being overwritten.

1. Open the parameter(s) in question


2. Copy the Name and the default value into a text document


3. Replace your MDX for your “sales” dataset or make the relevant changes


4. Click on the Parameters icon and paste the name(s) and the Default value(s) in, leaving the Dimension and the Hierarchy blank, it may complain that you have repeated them, so delete your original parameters.


5. 

When you click on Ok, it doesn’t overwrite your Parameters, which saves you a lot of time if you have several of these to do.

Hope that helps.

Max