MDX for excluding values in "Total" TM1 subsets

[BigG]

Hi, pulling my hair out here, I do not want to build an alternate hierachy removing an element from the rollup so I am attempting this in subsets.

I have a Branch dimension that has a rollup heirarchy from branch through to a top node 'Division Total'.
I have a cube that holds data like FTE and Salaries and has Employee dimension with Total EMployees rollup (as well as Branch dim).

My problem is The Branch dimension rollup 'Division Total' always includes every employee and every Branch in the value even when I apply MDX in a subset to remove one particular Branch called 999. I have tried MDX EXCEPT, UNION, INTERSECT. I would have though EXCEPT would show some success.

See MDX below, I drill down on Division Total and exclude Branch 999, this displays as expected by removing 999 branch, BUT the 'Division Total' still aggregates the Branch 999 in cube results.

Code: Select all

{EXCEPT(

{TM1DRILLDOWNMEMBER( {[Branch].[Division Total]},ALL,RECURSIVE ) }

,{ [Branch].[999]}
)}

I have even removed the 999 EMployees from the Employee dimension via subset, the cube Division Total Still aggregates 999. but view removes 999 employees detail.

Code: Select all

{FILTER( {TM1SUBSETALL( [Employee] )}, [Employee].[Branch] <> "999")}

Does anyone have any suggestions or experience with something similar? Thanks in advance

[tomok]

A subset is just as the name implies, it's a selected listing of items from a dimension. It does not change any of the properties of those elements, like their parent, or attribute values. A subset CAN NOT change how a hierarchy works nor can it stop an element from rolling up to a parent, if that is how the dimension is structured. For what you want to do you'll need to create an alternate hierarchy.

[Alan Kirk]

Hi, pulling my hair out here, I do not want to build an alternate hierachy removing an element from the rollup so I am attempting this in subsets.

I have a Branch dimension that has a rollup heirarchy from branch through to a top node 'Division Total'.
I have a cube that holds data like FTE and Salaries and has Employee dimension with Total EMployees rollup (as well as Branch dim).

My problem is The Branch dimension rollup 'Division Total' always includes every employee and every Branch in the value even when I apply MDX in a subset to remove one particular Branch called 999.

Tomok said it like it is. But bear in mind that you don't have to maintain two completely separate hierarchies for this; one option is to have a consolidation called Division Total (Excl 999) which, as the name suggests, excludes that division and use that for your reporting. Division Total then becomes that consolidation plus 999.

The other option is to create a consolidation consisting of Total Divisions and 999, where 999 has a weight of -1.

Either way, you only have to maintain one hierarchy, since the consolidations described above automatically change as the main one does.

[BigG]

THanks both Alan and tomok for the quick responses, we will investigate the single heirarchy structure that Alan suggested

Thanks again