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

SSRS Repeat Row Headers not working as expected

I had a report that I was working on recently, I was asked to repeat the row headers on each page. A simple task at first thought, but not as easy as you suspect on some reports.

If you have ever put a Table or a matrix in from the wizard, the standard “Repeat row headers” property should work, but if you have deleted the row headers at any point, I believe this messes with SSRS and causes you to think you are going insane.

So, if you ever set the property, view the report and see that it’s not having the desired effect, follow these steps to solve the problem:

1. Create a new row above your row with a grouping (right click on the row with the group and select Insert Row > Outside Group – Above)
2. Select a cell in this row, Ideally the left most cell
3. Go to your grouping pane, switch on “Advanced Mode” by clicking the small arrow to the right of Column Groups, You should see some “Static” groupings appear.
4. The one relative to the cell you have selected should show up darker than the rest, click on the darker static group, and view the properties (usually on the right hand pane), in the Other section there is a property for Repeat on new page, set this to true and test your report.
5. You may now have repeated headers, so move the contents from your original header (which should be above your new rows) to these new rows, and delete your original rows.

This should solve the problem, providing that you start from step 1, I tried doing step 3 & 4 for my original rows and this didn’t work. It must be something to do with SSRS thinking that your original rows aren’t row headers.

You could also take a different approach which doesn’t need you to create new rows, to modify the XML and put in the following element in the document.

<RepeatOnNewPage>true</RepeatOnNewPage>

I believe the first option is slightly easier and less error prone.

I got this detail from the following Link if you need more info.

I know from personal experience you can waste lots of time on this sort of tricky stuff so I hope it helps you out.

Max