ssas - 基于多个维度成员聚合值以通过 SCOPE 语句创建损益报告

问题描述

我对 SSAS MDX 比较陌生，并且面临在不同维度成员的所有度量上创建聚合的问题。下图最好地描述了我想要实现的目标：问题和解决方案应该是什么

这是一个更复杂场景的草稿和简化，所以不要打扰;-)。在运行了一系列实验后，我发现使用一个简单的维度而不连接到其他维度，以便能够使用 SCOPE 语句覆盖报告中生成的结果。简单维度“Kurz PuL”包含前面的成员 (Umsatz .. Periodenergebnis)，并应生成利润 + 损失报告。

Kurz PuL 维度

目前的工作是显示单个成员的结果（如 Umsatz、Wareneinsatz、SbA、Personal）。但我无法聚合多个成员的值以获得聚合，例如：Umsatz + Wareneinsatz = Rohertrag。我尝试了不同的方法，但都没有奏效。请参阅以下 SCOPE 语句，这些语句用于“覆盖”在 Excel 中显示的结果值（第一张图像 ^^）：

范围声明：

    /*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

SCOPE ([Kurz PuL].[Calculated].[Umsatz]);
    THIS = AGGREGATE([EinfacheBwaZeile].[Hierarchy].&[U]);
END SCOPE;

SCOPE ([Kurz PuL].[Calculated].[Wareneinsatz]);
    THIS = AGGREGATE([EinfacheBwaZeile].[Hierarchy].&[W]);
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[Rohertrag]);
    THIS = AGGREGATE(FILTER([EinfacheBwaZeile].[Hierarchy].CurrentMember, { [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W] }));
END SCOPE;

SCOPE ([Kurz PuL].[Calculated].[Personal]);
    THIS = AGGREGATE( { [EinfacheBwaZeile].[Hierarchy].&[P] });
END SCOPE;

SCOPE ([Kurz PuL].[Calculated].[Marketing]);
    THIS = AGGREGATE( { [EinfacheBwaZeile].[Hierarchy].&[M] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[Deckungsbeitrag]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D] });
END SCOPE;

SCOPE ([Kurz PuL].[Calculated].[SbA]);
    THIS = AGGREGATE( { [EinfacheBwaZeile].[Hierarchy].&[S] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[EBITDA]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D]
                        , [EinfacheBwaZeile].[Hierarchy].&[S], [EinfacheBwaZeile].[Hierarchy].&[A] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[EBIT]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D]
                        , [EinfacheBwaZeile].[Hierarchy].&[S], [EinfacheBwaZeile].[Hierarchy].&[A]
                        , [EinfacheBwaZeile].[Hierarchy].&[I] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[EBT]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D]
                        , [EinfacheBwaZeile].[Hierarchy].&[S], [EinfacheBwaZeile].[Hierarchy].&[A]
                        , [EinfacheBwaZeile].[Hierarchy].&[I]
                        , [EinfacheBwaZeile].[Hierarchy].&[T] });
END SCOPE;

/* DOES NOT WORK: */
SCOPE ([Kurz PuL].[Calculated].[Periodenergebnis]);
    THIS = AGGREGATE( {   [EinfacheBwaZeile].[Hierarchy].&[U], [EinfacheBwaZeile].[Hierarchy].&[W], [EinfacheBwaZeile].[Hierarchy].&[R], [EinfacheBwaZeile].[Hierarchy].&[P]
                        , [EinfacheBwaZeile].[Hierarchy].&[M], [EinfacheBwaZeile].[Hierarchy].&[D]
                        , [EinfacheBwaZeile].[Hierarchy].&[S], [EinfacheBwaZeile].[Hierarchy].&[A]
                        , [EinfacheBwaZeile].[Hierarchy].&[I]
                        , [EinfacheBwaZeile].[Hierarchy].&[T]
                        , [EinfacheBwaZeile].[Hierarchy].&[E] });
END SCOPE;

我一直在尝试寻找一种资源来解释如何聚合多个成员，但没有找到解决方案。如何使用 AGGREGATE() 函数来组合/求和与 [EinfacheBwaZeile].[Hierarchy].A, ... .B, ... .C 的多个维度成员相关的值？

我真的很感谢你的回答！在此先感谢， Cordt

-- 更新 2019-05-28 作为 2019-05-27 对 Moaz 的回复： -- 您好 Moaz，谢谢您的建议。可悲的是，该解决方案不能满足我的需求。首先要注意的是，我需要一个 SCOPE-Statement，而不是 SELECT-MDX。其次，我需要对另一个维度的特定成员的所有度量进行“汇总”。乍一看，这就像一个“运行总数”，但它取决于“先前”成员的值，其中一些可能会被跳过。用 Adventure Works 示例的话，以下显示了我需要的内容：

SCOPE (MountainBikeSales);
    THIS = AGGREGATE(Product.&[Bikes]);
END SCOPE;
SCOPE (BikesAndAccessories);
    THIS = AGGREGATE({ Product.&[Bikes], Product.&[Accessories] });
END SCOPE;
SCOPE (BikesAccsClothing);
    THIS = AGGREGATE({ Product.&[Bikes], Product.&[Accessories], Product.&[Clothing] });
END SCOPE;

这更清楚吗？您甚至可以想到一个 SCOPE，它通过总结自行车和服装而不是配饰来跳过配饰。对我来说问题是，第一个语句会成功（总结单个成员），但其他语句不会。

感谢您的意见！科尔特

标签： ssasmdx

解决方案

我不确定我是否完全理解您的问题。但我想你想知道如何计算运行总数。就是这样，看看下面的例子

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]

结果

编辑：根据您的编辑

select 
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity],
[Measures].[Internet Tax Amount],
[Measures].[Internet Gross Profit Margin]
}on 0 , 
[Product].[Category].[Category]
on 1
from
[Adventure Works]

结果

请注意，组件的所有度量都具有空值。现在让我们将组件等同于自行车和配件

Scope  ([Product].[Category].&[2]) ;  
this = (aggregate({[Product].[Category].&[1],[Product].[Category].&[4]},[Measures].[Measures].currentmember));
end scope;

select 
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity],
[Measures].[Internet Tax Amount],
[Measures].[Internet Gross Profit Margin]
}on 0 , 
[Product].[Category].[Category]
on 1
from
[Adventure Works]

结果

ssas - 基于多个维度成员聚合值以通过 SCOPE 语句创建损益报告

问题描述

解决方案

推荐阅读