SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSRS: Shared DataSet, Not So Shared!!!

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


Posted by Leah on 17 March 2011

Is this a SSRS bug?

Posted by georgew on 17 March 2011

It's a *feature*...

Posted by mtepe-945377 on 17 March 2011

Well said georgew, well said.  

I've found other "features" as well.  Like the in Query Designer where the dataset query runs in Design Mode but fails in Query Mode.  

Posted by madhu.kowthalam on 21 March 2011

Nice feature....

Posted by Mark Stacey on 27 March 2011

What you can do is the following:

When you publish the Shared dataset, also publish the parameter dataset. It doesn't matter that it's hidden, you can still publish it.

Then add BOTH datasets to your report - for those who don't know how to do this, it's by right clicking on the datasets folder, and saying "Add dataset" - you can then browse to the folder where you published the parameter dataset.

At that point, you can change the parameter that was automatically created when you added the shared dataset to use the parameter dataset for its' available values.

Leave a Comment

Please register or log in to leave a comment.