Exclude a descendant member from the hierarchy

[cs95aam]

Hello everyone,

I have an existing Finance MDX report which I need to modify. Currently, it consists of a crosstab with tuples as columns and rows which are members, levels etc from a cube hierarchy. Within the report, each crosstab is repeated based on a set which calculates the current month, previous month, H1, YTD. 

So the part I need to modify and I am struggling with is the row which is for Overheads. The user wants this displayed but he doesn't want a single descendant which is 3 levels down in the hierarchy to be included in the total. I need to display Overheads without this descendant member. The total calculation is done by measures and the time calculation.

I've attached my hierarchy. I know I need to use except, but except works on the child of the immediate parent. In my case I need to exclude the child which is 3 levels down.

How can I do this?

Thank you for reading my post.

[cs95aam]

So far I've got this:

except(descendants([Cube].[Account].[By GL Hierarchy].[Subtotal 3]->:[PC].[@MEMBER].[OVERHEADS],4), set([Cube].[Account].[By GL Hierarchy].[Account Code]->:[PC].[@MEMBER].[701900]))

However, I don't want a massive list of descendants in the report I want one total to appear.

Any ideas what to do next?

[Lynn]

Try using the aggregate function:

aggregate (
  currentMeasure within set except(descendants([Cube].[Account].[By GL Hierarchy].[Subtotal 3]->:[PC].[@MEMBER].[OVERHEADS],4), set([Cube].[Account].[By GL Hierarchy].[Account Code]->:[PC].[@MEMBER].[701900]))
)

[cs95aam]

Thanks Lynn, I've tried this but it totals up and produces a massive number is which clearly incorrect.

[Lynn]

Thanks Lynn, I've tried this but it totals up and produces a massive number is which clearly incorrect.

What is the measure you need to total for that set? I used currentMeasure as an example. Try replacing currentMeasure with the particular measure you need to aggregate.

If the expression from your earlier post gives you a set containing the desired members, then the aggregate function should total up a specified measure for that set.