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.
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?
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]))
)
Thanks Lynn, I've tried this but it totals up and produces a massive number is which clearly incorrect.
Quote from: cs95aam on 09 Feb 2016 04:17:03 AM
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.