CODEWITHSUNDEEP
ssasmdxpowerpivotunion-all
PeddiePooh
PeddiePooh

Reputation: 403

UNION multiple MDX queries in SSAS (powerpivot)

I have some sort of difficulties trying to join 2 MDX queries together. When running them separately they work fine. The script below

WITH 
  MEMBER [Measures].[ParameterCaption] AS 
    [Main_Incidents].[Priority].CurrentMember.Member_Caption 
  MEMBER [Measures].[ParameterValue] AS 
    [Main_Incidents].[Priority].CurrentMember.UniqueName 
  MEMBER [Measures].[ParameterLevel] AS 
    [Main_Incidents].[Priority].CurrentMember.Level.Ordinal 
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[# Incidents]
   ,[Measures].[%SLA]
  } ON COLUMNS
 ,[Main_Incidents].[Priority].ALLMEMBERS ON ROWS
FROM [Model];


WITH 
  MEMBER [Measures].[ParameterCaption] AS 
    [Main_Incidents].[usr_directorate].CurrentMember.Member_Caption 
  MEMBER [Measures].[ParameterValue] AS 
    [Main_Incidents].[usr_directorate].CurrentMember.UniqueName 
  MEMBER [Measures].[ParameterLevel] AS 
    [Main_Incidents].[usr_directorate].CurrentMember.Level.Ordinal 
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[# Incidents]
   ,[Measures].[%SLA]
  } ON COLUMNS
 ,[Main_Incidents].[usr_directorate].ALLMEMBERS ON ROWS
FROM [Model];

The most important bit for me is that I need the label column to show. So I want to UNION the 2 queries together so that the ParameterCaption captures values from "Priority" dimension and "Directorate" dimension....

Please someone help me to achieve this?

Upvotes: 4

Views: 1250

Answers (1)

Brenton
Brenton

Reputation: 444

This is a bit complex, but definitely possible.

Union in MDX only works for members of the same hierarchy, so to achieve this we need to make the row members into Tuples that combine the two hierarchies. We can do this by cross joining each of the ALLMEMBERS sets to the [All] member for the other hierarchy. Then we just need to change the Parameter Caption, Value and Level to conditionally get the value from the appropriate hierarchy.

This could look something like the code below:

WITH 
  MEMBER [Measures].[ParameterCaption] AS
    IIF([Main_Incidents].[Priority].CurrentMember.Level.Ordinal = 0, [Main_Incidents].[usr_directorate].CurrentMember.Member_Caption, [Main_Incidents].[Priority].CurrentMember.Member_Caption)
  MEMBER [Measures].[ParameterValue] AS
    IIF([Main_Incidents].[Priority].CurrentMember.Level.Ordinal = 0, [Main_Incidents].[usr_directorate].CurrentMember.UniqueName, [Main_Incidents].[Priority].CurrentMember.UniqueName)
  MEMBER [Measures].[ParameterLevel] AS
    IIF([Main_Incidents].[Priority].CurrentMember.Level.Ordinal = 0, [Main_Incidents].[usr_directorate].CurrentMember.Level.Ordinal , [Main_Incidents].[Priority].CurrentMember.Level.Ordinal)
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[# Incidents]
   ,[Measures].[%SLA]
  } ON COLUMNS
 ,{
    [Main_Incidents].[Priority].ALLMEMBERS * [Main_Incidents].[usr_directorate].[All],
    [Main_Incidents].[Priority].[All] * [Main_Incidents].[usr_directorate].ALLMEMBERS
  } ON ROWS
FROM [Model];

Upvotes: 1

Related Questions