MDX-TopCount加上“其他"或“其余" [英] MDX - TopCount plus 'Other' or 'The Rest'

问题描述

我创建了一个MDX查询，该查询可按以下方式计算TOP 10邮政编码(根据我的住院天数"):

I have created an MDX query which calculates the TOP 10 ZipCodes (according to my Patient Stay measure) as such:

WITH
MEMBER [Discharge Date].[Y-M-D].[ Aggregation] AS 'AGGREGATE( EXISTING { [Current Month] } )', SOLVE_ORDER = 0

SELECT
NON EMPTY { [Measures].[Patient Stays] }
ON COLUMNS,

TOPCOUNT({ ORDER( HIERARCHIZE( { [Patient].[ByZipcode].[All].CHILDREN } ), ( [Measures].[Patient Stays] ), BDESC ) }, 10)
ON ROWS

FROM [Patient Stay]

WHERE ( [Discharge Date].[Y-M-D].[ Aggregation], [Facility].[ByAffiliation].CURRENTMEMBER, [Facility].[ByRegion].CURRENTMEMBER )

此查询用于填充PerformancePoint 100％堆积条形图.客户要求，由于这是一个基于！00％的图表，因此我们将其余邮政编码集中到其他"字段中，这样就应该有11个值:前10个值中的每个值，第11个值中的第11个值是其余邮政编码的总和.

This query is used to populate a PerformancePoint 100% Stacked Bar chart. The client has asked that since this is a !00% based chart, we lump the rest of the zip codes into an "Other" field, such that there should be 11 values: one for each of the top 10, and an eleventh which is a sum of the remaining Zip Codes.

我是MDX的极端新手，但这并不让人感到不可能.有人有任何想法或建议吗?

I am an extreme novice to MDX, but this doesn't souund like it should be impossible. Does anyone have any ideas or suggestions?

推荐答案

我将尽力处理未经测试的代码，所以去:

I'll do my best with untested code, so here goes:

WITH
MEMBER [Discharge Date].[Y-M-D].[ Aggregation] AS 'AGGREGATE( EXISTING { [Current Month] } )', SOLVE_ORDER = 0

SET [Top10ZipCodes] AS 
    (TOPCOUNT({ ORDER( HIERARCHIZE( { [Patient].[ByZipcode].[All].CHILDREN } ), ( [Measures].[Patient Stays] ), BDESC ) }, 10))

MEMBER [Patient].[ByZipCode].[OtherZipCodes] AS 
    (AGGREGATE({EXCEPT([Patient].[ByZipCode].Members, [Patient].[ByZipCode].[Top10ZipCodes])}))

SELECT
NON EMPTY { [Measures].[Patient Stays] }
ON COLUMNS,

{[Top10ZipCodes], [Patient].[ByZipCode].[OtherZipCodes]}
ON ROWS

FROM [Patient Stay]

WHERE ( [Discharge Date].[Y-M-D].[ Aggregation], [Facility].[ByAffiliation].CURRENTMEMBER, [Facility].[ByRegion].CURRENTMEMBER )

此操作将创建一组您的前10个邮政编码，然后汇总(不同于总和！！)所有邮政编码，除了前10个邮政编码.

What this does is creates a set of your top 10 ZIP codes, and then aggregates (different than sum!!!) all the ZIP codes, with the exception of your top 10.

此外，如果这是一个通用集(排名前10位的邮政编码)，则可能要在多维数据集上建立一个集，您可以在其中重复使用它，而不必更改您拥有的每个MDX查询.

Also, if this is a common set (top 10 ZIP codes), you may want to make a set on the cube, where you can reuse it ad nauseum, without having to change every MDX query you have.

干杯，
埃里克

Cheers,
Eric

这篇关于MDX-TopCount加上“其他"或“其余"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！