Question

SELECT  
  [Measures].[Internet Sales Amount] ON COLUMNS  
 ,Tail  
  (  
    [Date].[Calendar Year].[Calendar Year].MEMBERS  
   ,2  
  ) ON ROWS  
FROM [Adventure Works];

在MDX查询中,我的产出如下:

因特网销售年限

CY 2003 9,791,060.30美元

2004财政年度:9 770 899.74美元

我理解这一问题是如何运作的,但我想在一个总是给我2年以下时间的河流中制定计算措施。如何做到这一点? 我是SSAS的新手。我在设计简单的措施和内容方面做得很好,但在使用MDX时,我大多 st。

PS:我尝试使用TopCount、BottomCount等,但我在此希望通过“年”来订购,这是一个层面。

希望得到任何帮助。

感谢

Parry

Answer 1

下面的询问计算出一项衡量日期最后2年金额的措施:

WITH
MEMBER [Measures].[Sales from the last 2 Years]
    AS Aggregate( Tail( [Date].[Calendar Year].[Calendar Year].Members, 2)
                , [Measures].[Internet Sales Amount]
                )

SELECT { [Measures].[Sales from the last 2 Years]
       } ON COLUMNS
     , { Tail( [Date].[Calendar Year].[Calendar Year].Members, 2)
       } ON ROWS
    FROM [Adventure Works]

其他有趣的询问,将计算每年及以往的总额:

WITH
MEMBER [Measures].[Sales from 2 years]
    AS Aggregate( { [Date].[Calendar Year].CurrentMember.PrevMember
                  : [Date].[Calendar Year].CurrentMember }
                , [Measures].[Internet Sales Amount]
                )

SELECT { [Measures].[Internet Sales Amount]
       , [Measures].[Sales from 2 years]
       } ON COLUMNS
     , NON EMPTY
       { [Date].[Calendar Year].[Calendar Year]
       } ON ROWS
    FROM [Adventure Works]

It does a sum, because the aggregation type of the measure [Measures].[Internet Sales Amount] is Sum, and the Aggregate function aggregates according to the measure s aggregation type.

MDX是一个难以掌握的议题;如果你重新开始,我建议你读到https://rads.stackoverflow.com/amzn/click/com/0471748080” rel=“nofollow noreferer”>。 MDX Solutions, 2nd edition 。

友情链接