posted 3/14/2011 by PatrickLeBlanc - Views: [3964]
I was really excited about the concept of Shared DataSets when they were introduced. My excitement diminished a little when I ran into a small issue. When you create an embedded data set based on a SSAS Cube that includes a parameter, a hidden dataset is automatically created that provides the parameter values.
Thus creating a drop down list of available choices on the report.
However, if the data set was created from a Shared Dataset the Hidden Dataset is not created when you add the shared dataset to the report. Therefore, the parameter is created, but the drop down list created on the report is empty since a dataset does not exist.
So, if you do not want to write MDX to populate your parameters then you cannot use Share Datasets based on cubes. If you find a workaround for this please email me at pleblanc@pragmaticworks.com.
Talk to you soon,
Patrick LeBlanc, MVP, founder SQL Lunch
Haven't fully used them yet because I'm not sure how my copy of the dataset will change the server 'shared' copy when I want to start from that template, but filter slightly, etc. SQLlunch topic?
That's a bit frustrating. Thanks for the heads up.
hmmm. Very interesting. I'll have to keep that in my back pocket for now. Have you put it on the special Microsoft site for MVP's? :)
I create shared datasets for common, reusable report parameters. In your example, I would create the embedded dataset and let SSRS create the hidden dataset. Open the hidden datset, copy the MDX out to create a shared dataset for Fiscal Dates. Delete both of the embeded datasets. Then when you build reports, add both shared data sets (the report data and the date list). Point your date paramter to the shared date dataset under 'Available Values'.
Depending on the cube, it is sometimes faster to query the relational table for the date dim and use SQL text functions to build a list of MDX pointers for the parameters, but that's a separate article.