SSAS MDX计算运行总计，但排除不太重要的行 [英] SSAS MDX Calculate running total but exclude less significant rows

问题描述

旧的SQL开发人员，MDX的新手:需要帮助，要求在运行总计中包含较小的总计，但不显示这些较小的总计的贡献行.

Old SQL developer, new to MDX: Need help with a requirement to include small totals in a running total but not show the contributing rows of those small totals.

考虑此数据

                Amount    Running Total
     Denver     6,321     6,321
     Portland   8,426    14,747
     Boise     19,222    33,969
     Helena    23,257    57,226
     Bozeman   31,225    88,451
     Seattle   36,894   125,345

我的要求是不要显示任何低于15,000的金额，而要显示包括这些金额的运行总额.我不能显示金额或累计总数少于15,000.这是为了确保无法识别出特定城市的人数.

My requirement is to not show any amounts under 15,000 but instead show the running total that includes them. I must not show amounts or running totals less than 15,000. This is to insure that small numbers can't be identified to specific cities.

像这样:

                Amount    Running Total
     Other     19,222    33,969
     Helena    23,257    57,226
     Bozeman   31,225    88,451
     Seattle   36,894   125,345

或者理想情况下，需要什么:

Or, ideally, what is needed:

                Amount
     Other     33,969
     Helena    23,257
     Bozeman   31,225
     Seattle   36,894

感谢您的协助 马丁·A

Thanks for any assistance MartinA

推荐答案

在几次白板会议之后，我的同谋提出了一个简单的解决方案:添加新度量，计算运行总额与城市数量之间的差.当此差异小于阈值时，这将是汇总的行，其中将包含其他"的城市名称，并且还将使用运行总计而不是城市总计. Tyson

After a few whiteboard sessions, my co-conspirator came up with a simple solution: Add a new measure, calculating the difference between the running total and the city amount. When this difference is less than the threshold, then this will be the rolled up row that is to contain the City name of "Other" and also use the running total rather than city total. Kudos to Tyson

下面是一些示例代码:

WITH 
SET [OrderedSet] AS
Nonempty
    (
    ORDER
        ([Age].[Age Group B].[Age Group B].Members,
         [Measures].[Emergency Room Visits per 1,000 Member Months],
         BASC
        )
    )
MEMBER [Measures].[RowNumber] AS
    Rank([Age].[Age Group B].CURRENTMEMBER,
         [OrderedSet]
        )
MEMBER [Measures].[Running Total] as 
    Sum(
        Head
            ([OrderedSet], 
             ([Measures].[RowNumber],[Age].[Age Group B].CurrentMember)
            ),
            [Measures].[Emergency Room Visits per 1,000 Member Months]
        )
MEMBER [Measures].[Ttl_RunTtl_Diff] AS
    [Measures].[Running Total] - [Measures].[Emergency Room Visits per 1,000 Member Months]
MEMBER MEASURES.NewAge AS 
    IIF([Measures].[Ttl_RunTtl_Diff] = 0 OR [Measures].[Ttl_RunTtl_Diff]>15000 
        , [Age].[Age Group B].CURRENTMEMBER.Name, "Other") 
MEMBER MEASURES.NewTotal AS 
    IIF([Measures].[Ttl_RunTtl_Diff] = 0 OR [Measures].[Ttl_RunTtl_Diff]>15000 
        , [Measures].[Emergency Room Visits per 1,000 Member Months], [Measures].[Running Total]) 
SELECT NON EMPTY 
  { 
  [Measures].[Emergency Room Visits per 1,000 Member Months],
  [Measures].[Member Months],
  [Measures].[Emergency Room Visits],
  [Measures].[Running Total],
  [Measures].[Ttl_RunTtl_Diff],
  [Measures].[NewAge],
  [Measures].[NewTotal]
  } 
 ON COLUMNS, 
  NON EMPTY 
    FILTER({[OrderedSet]} 
            , [Measures].[Running Total] > 15000 )
 ON ROWS FROM [Model] 

这篇关于SSAS MDX计算运行总计，但排除不太重要的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！