累计总和/运行总计| MDX [英] Cumulative Sum/ Running Total | MDX

问题描述

我具有以下SSAS多维数据集要求(MDX):

I have an SSAS Cube requirement (MDX) as below:

我正在尝试创建一个包含多个维度(大约7个维度)的组合的计算得出的度量累积总和/总计".另外，用户将使用他们想要的任何尺寸.例如，我们有一个产品，程序，子程序，ProgramStatus，SubProgramStatus，客户和日期.用户应该能够将维度的属性添加到最低级别，并且应该能够通过排除某些维度(例如产品或SubProgramStatus或两者)来将数据汇总到更高的级别.请注意，用户将使用报表布局作为表格形式，并且他们可以自由地进行切片和切块.

I am trying to create a calculated measure "Cumulative Sum/ Running Total" with the combination of multiple dimensions (around 7 dimensions). In addition, the users will be using any dimensions that they want. For example, we have a Product, Program, SubProgram, ProgramStatus, SubProgramStatus, Customer, and Date. The users should be able to add the dimension’s attribute to the lowest level as well as they should be able to roll-up the data to a higher level by excluding some of the dimensions such as the Product or SubProgramStatus, or both. Please note the users will be using the Report Layout as a Tabular Form and the can they should have the freedom to slice and dice.

我知道如何通过使用日期维度(例如YTD)创建累积度量，但是不确定如何通过包含和排除用户想要的所有维度或属性来包含用户可能执行的所有方案来创建MDX .此外，用户将具有另一个包含累积量度基数的非累积量度，并且用户将希望并排添加非累积量和累积量.通过添加累积和非累积度量，汇总应显示数量如何增加.请参阅示例.

I know how to create the cumulative measure by using the Date dimension such as YTD, but not sure how to create the MDX by including all the scenarios that the users might do, by including and excluding any dimension or attributes that they want. Beside, the users will have another non-cumulative measure that contains the base number for the cumulative measure, and the users will want to add the non-cumulative and cumulative side by side. By adding the cumulative and non-cumulative measures, the roll-up should show how the number increase. Please see the sample.

任何建议将不胜感激.

推荐答案

看看下面的示例查询.想法是，如果您知道最内层的属性，那么您的度量就可以执行累计运行

Take a look at the sample query below. The Idea is that if you know the inner most attribute your Measure will be able to perform running total

with 
member 
[Measures].[Internet Sales AmountRunningtotal]
as 
case when [Measures].[Internet Sales Amount] = null then null 
else 
sum({[Product].[Subcategory].firstchild:[Product].[Subcategory].currentmember},[Measures].[Internet Sales Amount])
end
select {[Measures].[Internet Sales Amount],
[Measures].[Internet Sales AmountRunningtotal]
} on columns,

non empty
([Date].[Calendar Year].[Calendar Year],[Date].[Calendar Quarter of Year].[Calendar Quarter of Year],
[Product].[Category].[Category],[Product].[Subcategory].[Subcategory])
on 
rows 
from 
[Adventure Works]

结果

这篇关于累计总和/运行总计| MDX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！