CODEWITHSUNDEEP
unionmdx
Roshan N
Roshan N

Reputation: 1053

How to merge two mdx queries when I have used same dimension & Same Measure

I have to merge below two mdx queries results into one set.

Query #1:

SELECT 
      {[Measures].[Internet Sales Amount]} ON COLUMNS,
      {[Product].[Category].&[4],
      [Product].[Category].&[1]} ON ROWS 
FROM [Adventure Works];

Output is:

                Internet Sales Amount
 Accessories       $700,759.96 
 Bikes          $28,318,144.65

Query #2:

SELECT 
      {[Measures].[Internet Sales Amount]} ON COLUMNS,
      {[Product].[Category].&[3]} ON ROWS 
FROM [Adventure Works]
where [Geography].[State-Province].&[WA]&[US]

Output is:

             Internet Sales Amount
Clothing    $339,772.61

I want to filter the region only for "clothing" Category but not for the other category.

But I want the results together. How to union these two result sets?

Final output should be:

        Internet Sales Amount
Accessories $700,759.96 
Bikes       $28,318,144.65 
Clothing    $339,772.61

Upvotes: 0

Views: 4911

Answers (1)

whytheq
whytheq

Reputation: 35557

This seems to work:

WITH 
  MEMBER [Product].[Category].[Clothing excl WA_US] AS 
    (
      [Product].[Category].&[3]
     ,[Geography].[State-Province].&[WA]&[US]
    ) 
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,{
    [Product].[Category].&[4]
   ,[Product].[Category].&[1]
   ,[Product].[Category].[Clothing excl WA_US]
  } ON ROWS
FROM [Adventure Works];

Suspect this performs better:

SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,{
    [Product].[Category].&[4]
   ,[Product].[Category].&[1]
   ,Exists
    (
      [Product].[Category].&[3]
     ,[Geography].[State-Province].&[WA]&[US]
    )
  } ON ROWS
FROM [Adventure Works];

Upvotes: 1

Related Questions