CODEWITHSUNDEEP
ssasmdxolap
Vitaly Ascheulov
Vitaly Ascheulov

Reputation: 182

Intersect Select Query in MDX

I want to have customers intersection in two mdx querys.

1.

SELECT 
  [Measures].[Cs] ON 0
 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1
FROM [sfe cube]
WHERE 
  (
    [Calend].[Period].&[201506]
   ,[Customers].[BP Territory].&[38UZ1]
   ,[Materials].[Brand].&[Coca-Cola]
  );

2.

SELECT 
  [Measures].[Cs] ON 0
 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1
FROM [sfe cube]
WHERE 
  (
    [Calend].[Period].&[201506]
   ,[Customers].[BP Territory].&[38UZ1]
   ,[Materials].[Brand].&[Fanta Orange CSD]
  );

My attempted mdx script:

SELECT
 {} ON COLUMNS,
INTERSECT(
 (  Select [Measures].[Cs] on 0,
    nonempty([Customers].[Customer].members) on 1
    from [sfe cube]
    WHERE ([Calend].[Period].&[201506]
    ,[Customers].[BP Territory].&[38UZ1]
    ,[Materials].[Brand].&[Coca-Cola])
 )
,
(   Select [Measures].[Cs] on 0,
    nonempty([Customers].[Customer].members) on 1
    from [sfe cube]
    WHERE ([Calend].[Period].&[201506]
    ,[Customers].[BP Territory].&[38UZ1]
    ,[Materials].[Brand].&[Fanta Orange CSD]))

) ON ROWS      
FROM [sfe cube]

When I run query there is a error :

Subselect only support axis COLUMNS.

What am I doing wrong?

Upvotes: 1

Views: 463

Answers (2)

SouravA
SouravA

Reputation: 5243

Your syntax has gone haywire! Check the syntax for INTERSECT here.

The way I would get the "common" customers would be as follows:

WITH SET CommonCustomers AS

INTERSECT(

            NonEmpty(
                        [Customers].[Customer].members, 
                        (
                            [Calend].[Period].&[201506],
                            [Customers].[BP Territory].&[38UZ1],
                            [Materials].[Brand].&[Coca-Cola]
                        ) * [Measures].[Cs]
                    )
            ,

            NonEmpty(
                        [Customers].[Customer].members, 
                        (
                            [Calend].[Period].&[201506],
                            [Customers].[BP Territory].&[38UZ1],
                            [Materials].[Brand].&[Fanta Orange CSD]
                        ) * [Measures].[Cs]
                    )

        )

SELECT {} ON COLUMNS,
CommonCustomers ON ROWS
FROM [sfe cube]

Upvotes: 0

whytheq
whytheq

Reputation: 35557

You can implement something like this for the AND logic:

WITH 
  SET [YourSet] AS 
    NonEmpty
    (
      NonEmpty
      (
        {[Customers].[Customer].MEMBERS}
       ,{[Materials].[Brand].&[Fanta Orange CSD]}
      )
     ,{[Materials].[Brand].&[Coca-Cola]}
    ) 
SELECT 
  [Measures].[Cs] ON 0
 ,NON EMPTY [YourSet] ON 1
FROM [sfe cube]
WHERE 
  (
    [Calend].[Period].&[201506]
   ,[Customers].[BP Territory].&[38UZ1]
  );

A different variant (that is I like better) using Exists is detailed in this post:

MDX query to calculate measure with AND condition by members from same dimension

I like sourav's idea to us intersect. I think his script can be simplified slightly to this:

WITH 
  SET CommonCustomers AS 
    Intersect
    (
      NonEmpty
      (
        [Customers].[Customer].MEMBERS
       ,[Materials].[Brand].&[Coca-Cola]
      )
     ,NonEmpty
      (
        [Customers].[Customer].MEMBERS
       ,[Materials].[Brand].&[Fanta Orange CSD]
      )
    ) 
SELECT 
  {[Measures].[Cs]} ON COLUMNS
 ,NON EMPTY 
    CommonCustomers ON ROWS
FROM [sfe cube]
WHERE 
  (
    [Calend].[Period].&[201506]
   ,[Customers].[BP Territory].&[38UZ1]
  );

Upvotes: 1

Related Questions