时间戳

首页 > 解决方案 > SSAS/MDX：除以 COUNT 覆盖 FORMAT_STRING？

ssas - SSAS/MDX：除以 COUNT 覆盖 FORMAT_STRING？

问题描述

我在格式化 SSAS（多维）中的计算成员时遇到了一个奇怪的问题。

我有一个按年度定义的预算金额，然后通过将年度金额除以成员数量，将其分配到季度、月或周级别。

将 SCOPE 与 DIVIDE 和 COUNT MDX 函数一起使用时，不再应用定义的度量格式 (FORMAT_STRING)。

这是代码和示例：

CREATE MEMBER CURRENTCUBE.[Measures].[VA Budget]
AS 
    NULL,
FORMAT_STRING = "#,#.00",
VISIBLE = 1; 

([Measures].[VA Budget], [Datum].[Datum JQMT].[Jahr].Members) = [VA Jahresbudget];
([Measures].[VA Budget], [Datum].[Datum JQMT].[Quartal].Members) = DIVIDE([VA Jahresbudget], COUNT( Descendants(Ancestor([Datum].[Datum JQMT].CurrentMember, [Datum].[Datum JQMT].[Jahr]), [Datum].[Datum JQMT].[Quartal]) ));
([Measures].[VA Budget], [Datum].[Datum JQMT].[Monat].Members) = DIVIDE([VA Jahresbudget], COUNT( Descendants(Ancestor([Datum].[Datum JQMT].CurrentMember, [Datum].[Datum JQMT].[Jahr]), [Datum].[Datum JQMT].[Monat]) ));
([Measures].[VA Budget], [Datum].[Datum JQMT].[Tag].Members) = DIVIDE([VA Jahresbudget], COUNT( Descendants(Ancestor([Datum].[Datum JQMT].CurrentMember, [Datum].[Datum JQMT].[Jahr]), [Datum].[Datum JQMT].[Tag]) ));
([Measures].[VA Budget], [Datum].[Datum JWT].[Woche].Members) = DIVIDE([VA Jahresbudget], COUNT( Descendants(Ancestor([Datum].[Datum JWT].CurrentMember, [Datum].[Datum JWT].[Jahr]), [Datum].[Datum JWT].[Woche]) ));

[ ]

但是，当我不在 DIVIDE 函数中使用 COUNT 时，它会正确应用：

([Measures].[VA Budget], [Datum].[Datum JQMT].[Monat].Members) = DIVIDE([VA Jahresbudget], 12 ));

[ ]

此外，如果我定义另一个计算成员并引用前一个成员，则格式将再次正确应用：

CREATE MEMBER CURRENTCUBE.[Measures].[VA Budget2]
AS 
    [Measures].[VA Budget],
FORMAT_STRING = "#,#.00",
VISIBLE = 1;

[ ]

这里发生了什么事？这是一个错误吗？如何在不定义额外计算成员的情况下使格式正确？

（SSAS 版本为 2016 - 13.0.5206）

标签： ssasmdx

解决方案

似乎 MDX 函数 Divide() 会导致结果丢失格式。

一种解决方法是创建一个仅包含 DIVIDE 定义的新（不可见）计算成员；然后在您的计算成员中使用它。这里有一个例子：

CREATE MEMBER CURRENTCUBE.[Measures].[My measure YTD]
 AS NULL, 
FORMAT_STRING = "0.0000",
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'MeasureGroup';


CREATE MEMBER CURRENTCUBE.[Measures].[My measure YTD DIVIDE YMD]
 AS DIVIDE (
        AGGREGATE (
                         PeriodsToDate([Date].[Year-Month-Day].[Year YMD],
                                        [Date].[Year-Month-Day].CurrentMember),
                         [Measures].[Dividend]
                    ),
        AGGREGATE (
                         PeriodsToDate([Date].[Year-Month-Day].[Year YMD],
                                        [Date].[Year-Month-Day].CurrentMember),
                         [Measures].[Divisor]
                    )
 ),
FORMAT_STRING = "0.0000",
VISIBLE = 0 ,  ASSOCIATED_MEASURE_GROUP = 'MeasureGroup';

CREATE MEMBER CURRENTCUBE.[Measures].[My measure YTD DIVIDE YQMD]
 AS DIVIDE (
        AGGREGATE (
                         PeriodsToDate([Date].[Year-Quarter-Month-Day].[Year YQMD],
                                        [Date].[Year-Quarter-Month-Day].CurrentMember),
                         [Measures].[Dividend]
                    ),
        AGGREGATE (
                         PeriodsToDate([Date].[Year-Quarter-Month-Day].[Year YQMD],
                                        [Date].[Year-Quarter-Month-Day].CurrentMember),
                         [Measures].[Divisor]
                    )
 ),
FORMAT_STRING = "0.0000",
VISIBLE = 0 ,  ASSOCIATED_MEASURE_GROUP = 'MeasureGroup';

SCOPE ( [Measures].[My measure YTD] );

    SCOPE ( [Date].[Data YMD].MEMBERS, [Date].[Year YMD].[Year YMD].MEMBERS );
        THIS = [My measure YTD DIVIDE YMD];
    END SCOPE;

    SCOPE ( [Date].[Data YQMD].MEMBERS, [Date].[Year YQMD].[Year YQMD].MEMBERS );
        THIS = [Measures].[My measure YTD DIVIDE YQMD];
    END SCOPE;

END SCOPE;

推荐阅读