Well it this weeks  T-SQL Tuesday #005  topic seems quite fitting. Having spent the past few weeks creating reports and dashboards in SSRS and SSAS 2008, I was frustrated by how difficult it is to use custom datasets to generate parameter drill downs. It also seems Reporting Services can be quite unforgiving when it comes to renaming things like datasets, so I want to share a couple of techniques that I found useful.

One of the things I regularly do is to add parameters to the querys. However doing this causes Reporting Services to generate a hidden dataset and parameter name for you. One of the things I like to do is tweak these hidden datasets removing the ‘ALL’ level which is a tip I picked up from Devin Knight in his blog:

There are some rules i’ve developed for myself since working with SSRS and MDX, they may not be the best or only way but they work for me.

Rule 1 – Never trust the automatically generated hidden datasets

Or even ANY, automatically generated MDX queries for that matter.... I’ve previously blogged about this here.  

If you examine the MDX generated in the hidden dataset you will see that it generates the MDX in the context of the originiating query by building a subcube, this mean it may NOT be appropriate to use this in a subsequent query which has a different context. Make sure you always understand what is going on.

Often when i’m developing a dashboard or a report there are several parameter oriented datasets that I like to manually create. It can be that I have different datasets using the same dimension but in a different context. One example of this, is that I often use a dataset for last month and a dataset for the last 6 months. Both use the same date hierarchy.

However Reporting Services seems not to be too smart when it comes to generating unique datasets when working with and renaming parameters and datasets. Very often I have come across this error when it comes to refactoring parameter names and default datasets.

"an item with the same key has already been added"

The only way I’ve found to reliably avoid this is to obey to rule 2.

Rule 2 – Follow this sequence when it comes to working with Parameters and DataSets:

1.    Create Lookup and Default Datasets in advance

2.    Create parameters (set the datasets for available and default values)

3.    Go into query and tick parameter check box

4.    On dataset properties screen, select the parameter defined earlier from the parameter value defined earlier.

Rule 3 – Dont tear your hair out when you have just renamed objects and your report doesn’t build

Just use XML notepad on the original report file. I found I gained a good understanding of the structure of the underlying XML document just by using XML notepad. From this you can do a search and find references of the missing object. You can also just do a wholesale search and replace (after taking a backup copy of course ;-)

So I hope the above help to save the sanity of anyone who regularly works with SSRS and MDX.

@Blakmk