我有一个计算度量值,我正在尝试使用范围在我的日期维度中的多个层次结构(年-季度-月-日(上工作。
该度量值在会计层次结构上工作正常,但在日历层次结构上不起作用,一旦我删除了月份级别,该维度中的所有值的值都将返回相同的值。
CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[Average Head Count]
AS NULL,
FORMAT_STRING = "Standard",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Availability';
SCOPE([Measures].[Average Head Count]);
-- Calendar Hieararchy
SCOPE([Date].[Calendar Hierarchy].Members);
THIS = Avg(Descendants([Date].[Calendar Hierarchy].CurrentMember, [Date].[Calendar Hierarchy].[Date]), [Measures].[Availability Count]);
END SCOPE;
-- Fiscal Hierarchy
SCOPE([Date].[Fiscal Hierarchy].Members);
THIS = Avg(Descendants([Date].[Fiscal Hierarchy].CurrentMember, [Date].[Fiscal Hierarchy].[Date]), [Measures].[Availability Count]);
END SCOPE;
END SCOPE;
我可以使用针对 THIS 指定的相同计算将它们创建为两个单独的度量值,因此我的范围语法必须不正确。
作用域中的集合包括 [Date]。日历层次结构]。[全部]/[日期]。[财政层次结构]。[ALL],如何像这样更改您的代码:
SCOPE([Measures].[Average Head Count]);
-- Calendar Hieararchy
SCOPE([Date].[Calendar Hierarchy].[Calendar Hierarchy]);
THIS = Avg(Descendants([Date].[Calendar Hierarchy].CurrentMember, [Date].[Calendar Hierarchy].[Date]), [Measures].[Availability Count]);
END SCOPE;
-- Fiscal Hierarchy
SCOPE([Date].[Fiscal Hierarchy].[Fiscal Hierarchy]);
THIS = Avg(Descendants([Date].[Fiscal Hierarchy].CurrentMember, [Date].[Fiscal Hierarchy].[Date]), [Measures].[Availability Count]);
END SCOPE;
END SCOPE;