Question

Suhandy Chow on Fri, 29 Jul 2016 08:51:34


My MDX select query:

SELECT { [Measures].[Fact Term Employee Count] } ON COLUMNS,

{ ([Term Reason].[Attrition].[Term Rsn Code].ALLMEMBERS ) } ON ROWS FROM

( SELECT ( { [Term Reason].[Attrition].[Term Rsn Group].&[Termination] } )

ON COLUMNS FROM [Terminate])

Result:

Termination | Absen more ... | 669

Termination | Violation | 665

Termination | Violation | 7

Termination | Cases | 40

Termination | Cases | 19

Termination | Contract End | 6782

Termination | Efficiency | 244

Termination | Contract End | 11

Termination | Contract End | 7423

Termination | Absent more ... | 290

I need to group it into:

termination | Absent more... | 959

termination | Violation | 672

termination | Cases | 59

termination | Contract End | 14,216

termination | Efficiency | 244

can anyone help me to achieve this?




Sponsored



Replies

yger on Fri, 29 Jul 2016 09:00:09


Hi,Instead of using Term Rsn Code u could use some Term Rsn Name Attribute if u havbe it designed in the Dimension.

Otherwise create Calculated members like (taken from Adventure Works):

Create member currentcube.[Customer].[Customer Geography].[All].[Aggregation1] as

Aggregate

({[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[Canada],[Customer].[Customer Geography].[Country].&[France]})

Simon_Hou on Sat, 30 Jul 2016 06:15:38


Hi Suhandy,

Yger is correct. In this scenario, it seems [Term Rsn Code] is child level in [Attrition] hierarchy. If you apply [Term Reason].[Attrition].[Term Rsn Code].ALLMEMBERS, it will show fact value associated with [Term Rsn Code]. You should directly select the parent level on your Axis so that you will get the aggregate value for the each [Term Rsn].

Regards,