Search code examples
ssasmdxmultidimensional-cube

MDX Combine two queries


I need two join two queries, I tried with members but it didn't work. I am new to MDX, please let me know if there a way of doing this. Error is due to list in the calculated member.

SELECT NON EMPTY { [RR TYPE].[Item].[Item].ALLMEMBERS * [Measures].[Unit Value] } ON COLUMNS, 
        NON EMPTY { ([FFS].[FFSCD].[FFSCD].ALLMEMBERS) } ON ROWS 
FROM  [GLCube] 
WHERE ( [Location].[Site Name].&[Sandbox]
        , [LT DT].[DATE HIERARCHY].[YEAR].&[2010]
        ,-{ [Ledger].[ID].&[A1],[Ledger].[ID].&[A2] ,[Ledger].[ID].&[A3]} ) 

enter image description here

SELECT NON EMPTY {  [RT TYPE].[Item].[Item].ALLMEMBERS * [Measures].[Price Value] } ON COLUMNS, 
    NON EMPTY { ([FFS].[FFSCD].[FFSCD].ALLMEMBERS) } ON ROWS 
FROM (SELECT [CCTYPE].[Desc].&[FCD] ON COLUMNS 
        FROM [GLCube])
WHERE ( [Location].[Site Name].&[Sandbox]
        , [LT DT].[DATE HIERARCHY].[YEAR].&[2010])

enter image description here

Combined query

WITH MEMBER [Measures].[Unit Value]] AS
    (-{[Ledger].[ID].&[A1],[Ledger].[ID].&[A2] ,[Ledger].[ID].&[A3]},[Measures].[Unit Value])
MEMBER [Measures].[Price Value] AS
    ( [CCTYPE].[Desc].&[FCD],[Measures].[Price Value] )
SELECT NON EMPTY {[Measures].[Unit Value],[Measures].[Price Value]} ON COLUMNS,
    NON EMPTY { ([FFS].[FFSCD].[FFSCD].ALLMEMBERS} ON ROWS 
FROM  [GLCube] 
WHERE ( [Location].[Site Name].&[Sandbox]
        , [LT DT].[DATE HIERARCHY].[YEAR].&[2010])

enter image description here

Expected Output

enter image description here


Solution

  • WITH
    MEMBER [Measures].[Filtered Unit Value] as 
     Aggregate(
      -{ [Ledger].[ID].&[A1],[Ledger].[ID].&[A2] ,[Ledger].[ID].&[A3]},
          [Measures].[Unit Value]
     )
    SELECT NON EMPTY 
    { 
    [RR TYPE].[Item].[Item].ALLMEMBERS 
    * {[RT TYPE].[Item].[All]}
    * { [CCTYPE].[Desc].[All]}
    * [Measures].[Filtered Unit Value] 
    } 
    +
    { 
    {[RR TYPE].[Item].[All]} 
    * [RT TYPE].[Item].[Item].ALLMEMBERS 
    * { [CCTYPE].[Desc].&[FCD]}
    * [Measures].[Price Value] 
    }
    
    ON COLUMNS, 
            NON EMPTY { ([FFS].[FFSCD].[FFSCD].ALLMEMBERS) } ON ROWS 
    FROM  [GLCube] 
    WHERE ( [Location].[Site Name].&[Sandbox]
            , [LT DT].[DATE HIERARCHY].[YEAR].&[2010]
     )