温馨提示:本文翻译自stackoverflow.com,查看原文请点击:ssas - MDX Combine two queries
mdx ssas multidimensional-cube

ssas - MDX合并两个查询

发布于 2020-04-29 09:49:31

我需要两个联接两个查询,我尝试了与成员,但没有用。我是MDX的新手,请告诉我是否有这样做的方法。错误归因于计算成员中的列表。

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]} ) 

在此处输入图片说明

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])

在此处输入图片说明

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])

在此处输入图片说明

Expected Output

在此处输入图片说明

查看更多

提问者
Gokul
被浏览
25
GregGalloway 2020-02-11 10:12
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]
 )