CODEWITHSUNDEEP
excelssasmdxadventureworks
sldrose
sldrose

Reputation: 11

Adventureworks MDX calculation not updating member when child members filtered in excel

I have an MDX calculation issue.

Basically the calculation in the cube is

[Operating Profit] = [Gross Margin] - [Operating Expenses]

This calculation does not update when I use excel to filter out child members of [Operating Expenses]. e.g. uncheck [Labor Expenses] The Operating Profit should change, but it remains static.

I have created the following calculated member in Excel2013 as a test, however this is static as well.

WITH MEMBER [Account].[Accounts].[Operating Profit Calc] as 
[Account].[Accounts].&[49] - [Account].[Accounts].&[58]

I have spent some time experimenting with the MDX existing function, but I am not able to get this to work with the Descendants of the [Operating Expenses] (&[58]) member.

(please download and update connection to your local AW cube)

AdventureWorks example

Upvotes: 0

Views: 206

Answers (1)

sldrose
sldrose

Reputation: 11

Figured it out, the key is to use dynamic sets

Create the following dynamic set.

create hidden dynamic set currentcube.[OPEX_dyn] as { intersect( existing [Account].[Accounts].members , descendants([Account].[Accounts].&[58],,LEAVES) ) };

The mdx existing function takes ALL members in the dimension that are active (checked) in excel.

The descendants function, returns all leaf members below the target "Operating Expenses" (key &[58]) member. This includes all children irrespective of whether the descendant leaf member has been selected/filtered in excel.

The intersect function basically applies the existing functionality to the descendants of the target member, returning a set of leaf members beneath the target that are selected.

By adding the following calculated member, only the selected children/desc of the target member are aggregated

WITH MEMBER [Account].[Accounts].[OPEX dynamic] as sum([OPEX_dyn],[Measures].currentmember)

The above example will only partially address the initial calculation in the AdventureWorks cube, as the AW cube uses plus and minus unary operators at the branch level to aggregate, whereas this method will sum only leaf members. i.e.

[Operating Profit] = [Gross Margin] - [Operating Expenses]

Upvotes: 1

Related Questions