Creating
Cascaded Parameters using MDX Queries
Kindly use the AdventuresDW database
Step 1 : Prepare the MDX Queries for the Parameter @Countries
WITH
MEMBER [MEASURES].[Paramvalue] As
[Customers].[Countries].CurrentMember.UniqueName
MEMBER [MEASURES].[ParamLabel] As
[Customers].[Countries].CurrentMember.Name
Select
{
[MEASURES].[Paramvalue],
[MEASURES].[ParamLabel]
}
On Columns,
{
[Customers].[Countries].AllMembers -
[Customers].[Countries].[All]
}
On Rows
From
[AdventureCUBE]
Step 2: Prepare the MDX Queries for the Parameter @States
WITH
MEMBER [MEASURES].[Paramvalue] As
[Customers].[States].CurrentMember.UniqueName
MEMBER [MEASURES].[ParamLabel] As
[Customers].[States].CurrentMember.Name
Select
{
[MEASURES].[Paramvalue],
[MEASURES].[ParamLabel]
}
On Columns,
{
[Customers].[States].AllMembers -
[Customers].[States].[All]
}
On Rows
Step 3: Prepare the MDX Queries for the Parameter @Cities
WITH
MEMBER [MEASURES].[Paramvalue] As
[Customers].[Cities].CurrentMember.UniqueName
MEMBER [MEASURES].[ParamLabel] As
[Customers].[Cities].CurrentMember.Name
Select
{
[MEASURES].[Paramvalue],
[MEASURES].[ParamLabel]
}
On Columns,
{
[Customers].[Cities].AllMembers -
[Customers].[Cities].[All]
}
On Rows
From
(Select StrtoSet(@Countries) on
Columns From
(Select StrtoSet(@States) On Columns From [AdventureCUBE])
);
Step 4: Create Report Data Source which connect the CUBE and Create 3 New Dataset such as Countries, State & Cities.
Step 5: Creating Dataset for Countries, States & Cities shown below 
Step 6: 3 dataset are created as shown below
Step 7: Create 3 Input Parameters, as @Countries , @States and @Cities
Step 8: Check the Multiple Select Values in the General tab.
Step 9: Click on the Available Value tab and Select the Get Value from Query. Select the Respective Dataset for Countries as CountriesSet. For Value Field Value provide the ParamValue and Label Field value provide the ParamLabel and click on Okay. leave other option as it is.
Step 10: Run the Report and check the Cascading Parameter works properly. By Default nothing will appear as shown. Once selected it will show the respective values.
Step 11: Selecting Countries will show the Respective States and Selecting the State will show the Cities present in it.
Step 12: Reports will be shown in the Later Session
Regards
Daniel J. S
Dataware and Data Mining Architect
+91 8939574948...
No comments:
Post a Comment