SSRS Parameters and MDX Data Sets
- by blakmk
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.
