SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 SSRS cascading parameters with shared dataset
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scottcabral79
Starting Member

3 Posts

Posted - 04/14/2014 :  11:16:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/22/2014 :  05:18:23  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000