MDX Except Function

[ccierpik]

I'm having a little problem in using the Except statement to return a MDX subset. I want to return the children of an element (Accrued Expenses) with the parent (Accrued Expenses) and one of the children (Accrued Bonuses) excluded from the result. I tried the following and while it does exclude Accrued Expenses it does NOT exclude Accrued Bonuses from the result. I've triple checked that I spelled it correctly and these are not alias'. I've also tried different methods for returning the first set and get the same results. I'm using version 8.4.3.1811. Thank you in advance for any assistance you can provide.

Except(
{DrillDownLevel({[Finstmt_bs_Account].[Accrued Expenses]})},
{[Finstmt_BS_Account].[Accrued Expenses],
[Finstmt_BS_Account].[Accrued Bonuses]}
)

[Marcus Scherer]

just a quick answer to give you an idea - without having tested it. You may use .children to exclude the C-Element one step before the except comes into place:

Except(
{[Finstmt_bs_Account].[Accrued Expenses].children},
{[Finstmt_BS_Account].[Accrued Bonuses]}
)

[ccierpik]

Thanks Marcus. That was one of the alternative methods I tried, but I get the same results. Accrued Bonuses is still included in the returned subset.

[Mike Cowie]

Hi,

First, I don't really see anything wrong with your MDX - the way Marcus has written it is a little easier to read/understand, but equivalent.

Anway, I saw some problems with EXCEPT a while ago, and I think my customer logged the issue with Applix or Cognos (whoever owned TM1 at the time). Let me run you through an example of the problem (it was very hit-or-miss and indicated that the EXCEPT function behavior was a little flaky for a particular element):

Trying to use the following MDX expression:

Code: Select all

{ EXCEPT( 
   {TM1SUBSETALL( [acct] )}, 
   { [acct].[Travel and Entertainment].Children }) } 

We got the following behavior across TM1 versions (the subset should have excluded all children in the Travel and Entertainment rollup from the ALL subset - Note there was no subset named "Travel and Entertainment" conflicting):
TM1 9.1 SP2 U3: CORRECT
TM1 9.0 SP2: WRONG - The child elements were not excluded.
TM1 8.4.5: WRONG - The child elements were not excluded.
TM1 8.2.9: WRONG - The child elements were actually included twice!
TM1 8.1.8: WRONG - The child elements were actually included twice!

We did some playing around at the time and the only thing that made the subset work in 9.0 SP2 (the version we were targeting) was changing the name of the rollup. The following different names were tried and here were the results of the tests:
Travels and Entertainment: FAILED
Travel and Entertainments: FAILED
Test – Travel and Entertainment: FAILED
T&E Test: SUCCESS! We didn't try any further names.

So, the bottom line is that if we added a new rollup called T&E Test and used the following MDX expression everything was OK:

Code: Select all

{ EXCEPT( 
   {TM1SUBSETALL( [acct] )}, 
   { [acct].[T&E Test].Children }) } 

BOTTOM LINE: The farther back you go in TM1 versions, the more buggy they may be when it comes to MDX support. I'm sorry I can't tell you much more on that, but hopefully this will validate that you are probably not going crazy and that you may need to test in a newer version and/or reach out to support (though you may be on an unsupported release - I forget if 8.4.3 is out of the supported versions list, but I would guess it is).

Regards,
Mike