SSRS cascading parameters with shared dataset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

SSRS cascading parameters with shared dataset

Author

Topic

scottcabral79
Starting Member

3 Posts

Posted - 2014-04-14 : 11:16:01

I am currently building some SSRS reports that sit on top of an SSAS cube.

I will be using the same 2 parameters on all of the reports. These parameters are called Division and Operations and are populated from a Client Dimension table hierarchy. These parameters are also cascading, being that the Operations parameter should change based on the value in the Division parameter

Since these parameters are going to be used on multiple reports, i was going to create the datasets that are going to populate them as shared datasets.

here is the mdx for the Division shared dataset:

WITH
MEMBER [Measures].[ParameterCaption] AS
[Prospect Client].[Business Area Hierarchy].CurrentMember.Member_Caption
MEMBER [Measures].[ParameterValue] AS
[Prospect Client].[Business Area Hierarchy].CurrentMember.UniqueName
MEMBER [Measures].[ParameterLevel] AS
[Prospect Client].[Business Area Hierarchy].CurrentMember.Level.Ordinal
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
,[Measures].[ParameterLevel]
} ON COLUMNS,
{[Prospect Client].[Business Area Hierarchy].[Division].ALLMEMBERS} ON ROWS
FROM [Broker Premium]

Here is the MDX for the operations shared dataset:

WITH
MEMBER [Measures].[ParameterCaption] AS
[Prospect Client].[Business Area Hierarchy].CurrentMember.Member_Caption
MEMBER [Measures].[ParameterValue] AS
[Prospect Client].[Business Area Hierarchy].CurrentMember.UniqueName
MEMBER [Measures].[ParameterLevel] AS
[Prospect Client].[Business Area Hierarchy].CurrentMember.Level.Ordinal
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
,[Measures].[ParameterLevel]
} ON COLUMNS,
{[Prospect Client].[Business Area Hierarchy].[Section].ALLMEMBERS} ON ROWS
FROM [Broker Premium]

The change that I need to make is to get the Operations shared dataset to be cascading based on the value selected from the Division parameter.

Does anyone know the correct syntax to add to the MDX to get this to work? Also, are there any other spots where I need to tell the Operations data set to be aware of the Division parameter value?

thanks

Scott

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-04-22 : 05:18:23

The below link provides solution for the above post...

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8c371381-601d-4115-8c58-d15bbd6e0939/ssrs-cascading-parameters-using-mdx-and-shared-dataset?forum=sqlanalysisservices

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources