Search code examples
reporting-servicesssas

Creating Parameters for shared datasets using Multidimensional Data

ive been trying to create a SSRS report based off a cube in the system. This report is to use a shared datasource and a shared dataset. So far, ive created a shared datasource and a shared dataset which has about 5 dimensions as parameters in the filter area (with the "equal" operator) with the parameters field checked off and a bunch of dimensions and measures in the results grid.

Now, when i create a report using this shared dataset, the 5 dimensions present in the filter area do appear as parameters, however, when i run the report, these parameters have no values in the drop down lists. Am i missing something in the process? Is this a permissions issue?

When i create a local dataset then things are fine and the parameters are populated with the members of the dimension. but the same doesnt work on a shared data set.

Also, i m using the report builder 3.0.

Has anyone run into the same issue? Please help.

Solution

  • I think parameters are local to a report and aren't really seen as shared. You can share a dataaset, but I'm not sure how you would share a parameter as it requires another dataset to populate it. When you create a parameter from an SSAS source, SSRS creates a hidden dataset for each parameter and uses that to populate the parameter drop-down list.

    You can write another shared dataset that uses the same MDX as what SSRS normally does for the hidden dataset and make it available to report builders. For instance, here is the MDX that SSRS generates for a calendar year parameter: 

    WITH 
Member [Measures].[ParameterCaption] as [Date].[Calendar Year].Currentmember.CAPTION
MEMBER [Measures].[ParameterValue] as [Date].[Calendar Year].Currentmember.UNIQUENAME
MEMBER [Measures].[ParameterLevel] as [Date].[Calendar Year].Currentmember.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel} on COLUMNS, 
[Date].[Calendar Year].ALLMEMBERS ON ROWS 
FROM [CUBE]

    You can take this and adapt it for whatever dimension hierarchy you need. If you were doing a product category parameter, you would just replace [Date].[Calendar Year] with [Product].[Category] in the query.