T-SQL Tuesday #005 : SSRS Parameters and MDX Data Sets
- by blakmk
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
