代码之家 › 专栏 › 技术社区 › James Culshaw

MDX结果中的合计错误

mdx

James Culshaw · 技术社区 · 8 年前

我有以下MDX查询:

SELECT  { [Measures].[Work Order Count Housekeeping Per Sq Ft],  
          [Measures].[Work Order Count], 
          [Measures].[House Keeping Square Footage]} ON 0,  
        { (  [Location].[Entity Location Name].Members ) } ON 1 
FROM [Work Order] 
WHERE ( {  [Department].[Department Name].[Housekeeping]}, 
        { [Location].[Entity Location ID].[12280], [Location].[Entity Location ID].[14067], [Location].[Entity Location ID].[15092]}, 
        {  [Event Start Dates].[Date Key].[20160705] :  [Event Start Dates].[Date Key].[20180705]   }, 
        { [Owner Entity].[Entity ID].[12279], [Owner Entity].[Entity ID].[12280], [Owner Entity].[Entity ID].[14067], [Owner Entity].[Entity ID].[15092]}, 
        { [Work Order Days Open].[Days Open].[1] : [Work Order Days Open].[Days Open].[250] }, 
        { [Work Order Days Overdue].[Days Overdue].[1] : [Work Order Days Overdue].[Days Overdue].[250] } )

这就是我得到的结果:

我期待着全部值为6.42857、45和7,而不是我得到的值。

我在我的问题上做错了什么?

1 回复 | 直到 8 年前

whytheq 8 年前

一切永远都是一切

似乎您需要一个新的all,它是where子句中选择的3个成员的聚合。

可以使用with子句创建allcustom成员:

WITH
SET LocationSet AS
{ [Location].[Entity Location ID].[12280], 
[Location].[Entity Location ID].[14067], 
[Location].[Entity Location ID].[15092]}
MEMBER Location].[Entity Location ID].[All].ALLcustom AS
AGGREGATE ( LocationSet )
SET [Locations] AS
{LocationSet,
 [Location].[Entity Location ID].[All].ALLcustom
}
SELECT  { [Measures].[Work Order Count Housekeeping Per Sq Ft],  
          [Measures].[Work Order Count], 
          [Measures].[House Keeping Square Footage]} ON 0,  
        [Locations] ON 1 
FROM [Work Order] 
WHERE ( {  [Department].[Department Name].[Housekeeping]}, 
        {  [Event Start Dates].[Date Key].[20160705] :  [Event Start Dates].[Date Key].[20180705]   }, 
        { [Owner Entity].[Entity ID].[12279], [Owner Entity].[Entity ID].[12280], [Owner Entity].[Entity ID].[14067], [Owner Entity].[Entity ID].[15092]}, 
        { [Work Order Days Open].[Days Open].[1] : [Work Order Days Open].[Days Open].[250] }, 
        { [Work Order Days Overdue].[Days Overdue].[1] : [Work Order Days Overdue].[Days Overdue].[250] } )

推荐文章