MDX with Expand Above in Subset

Post Reply
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

MDX with Expand Above in Subset

Post by kenship »

I'm building a subset using MDX. The hierarchy structure looks like this:

Net Profit
> Gross Exp
> Exp Group
> GL Accounts
> Revenue
> Rev Group
> GL Accounts

The request is to list all of them to the lowest level but using Expand Above so that subtotal at the bottom, except Net Profit to remain on top.

When I use MDX and apply Expand Above, Net Profit will to the bottom, I use the Except statement to exclude it. But I have no luck putting Net Profit back to the top. No matter what MDX function I use to include Net Profit, it will show up at the bottom.

My question is: Is there any way to put Net Profit on top and keep the Expand Above layout.

Right now it looks like this, it won't show Net Profit at all.

Code: Select all

EXCEPT(
{TM1DRILLDOWNMEMBER({[GL_Account].[NetProfit]},ALL,RECURSIVE)}
,{[GL_Account].[NetProfit]}
)
Thanks.

Kenneth
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: MDX with Expand Above in Subset

Post by paulsimon »

Hi Kenneth

The following should do it

Code: Select all

{ UNION(
                { [GL_Account].[NetProfit]},
                { EXCEPT(
                                { TM1DRILLDOWNMEMBER( { [GL_Account].[NetProfit] } , ALL , RECURSIVE ) } ,
                                { [GL_Account].[NetProfit] }
                             )
                }
             )
}
The Union just puts Net Profit at the top, since you aren't expanding it, expand above won't apply. Not actually tried it, but it should work. Let me know.

Regards

Paul Simon
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: MDX with Expand Above in Subset

Post by kenship »

Thanks but unfortunately it still put Net Profit down at bottom.

I began to believe that since the elements expanded are coming from the same parent (Net Profit) that I want to place on top, the Expand Above overrides and put it at bottom no matter what the MDX said.

Kenneth
paulsimon wrote: Tue Jan 22, 2019 9:00 pm Hi Kenneth

The following should do it

Code: Select all

{ UNION(
                { [GL_Account].[NetProfit]},
                { EXCEPT(
                                { TM1DRILLDOWNMEMBER( { [GL_Account].[NetProfit] } , ALL , RECURSIVE ) } ,
                                { [GL_Account].[NetProfit] }
                             )
                }
             )
}
The Union just puts Net Profit at the top, since you aren't expanding it, expand above won't apply. Not actually tried it, but it should work. Let me know.

Regards

Paul Simon
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: MDX with Expand Above in Subset

Post by paulsimon »

Hi Kenneth

I see the problem. To get around it I did the following on the SDATA sample cubes, using the account2 dimension which is the most similar one I could find on a sample cube that we can both access.

The solution needs two subsets.

Create a Subset called Subset Exp Above, which has the Expand Above option and the MDX Expression

{ EXCEPT( {TM1DRILLDOWNMEMBER( {[account2].[Earnings Before Taxes] }, ALL, RECURSIVE )}, { [account2].[Earnings Before Taxes] }) }

I then created a second subset with the Expand Above option turned off and the Expression

{UNION( {[account2].[Earnings Before Taxes] } , {[account2].[Subset Exp Above] } ) }

The second part of the union references the subset created above. It appears that this gets around the problem. Even though the containing subset does not have Expand Above turned on, the Expand Above set on the Unioned in Subset is retained.

Regards

Paul Simon
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX with Expand Above in Subset

Post by Wim Gielis »

That's a good trick Paul !
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: MDX with Expand Above in Subset

Post by kenship »

Agree! It's a good solution.

Thanks Paul!
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX with Expand Above in Subset

Post by lotsaram »

I think it is worthwhile to resurect this post. I'm not sure in which version it was added to the server, and it remains undocumented, but there is now a dedicated TM1 specific MDX function to support expand above, TM1ToggleExpandMode

Code: Select all

{TM1ToggleExpandMode( {<your set expression here>}, EXPAND_ABOVE)}
This is how expand above is supported in Workspace. The function is supported in 11.8 of the server (and maybe ealier?)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX with Expand Above in Subset

Post by MarenC »

Hi,

this didn't quite do what I expected.

I created the following MDX:

Code: Select all

{TM1ToggleExpandMode( {TM1DRILLDOWNMEMBER( {[Brand].[UK and Europe]}, ALL, RECURSIVE )}, EXPAND_ABOVE)}
And the result was as follows:

Image

I was expecting the following:

Image

Maren
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX with Expand Above in Subset

Post by Wim Gielis »

Works fine for me, server version 11.8

Code: Select all

{TM1ToggleExpandMode( {[Fct_Country].[Belgium]} + {[Fct_Country].[Total Country]}, EXPAND_ABOVE)}
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX with Expand Above in Subset

Post by MarenC »

I am looking at version 11.9, get the same result in both PAW and Architect.

Not impressed by this new function so far!
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX with Expand Above in Subset

Post by Wim Gielis »

MarenC wrote: Thu Oct 21, 2021 8:24 am I am looking at version 11.9, get the same result in both PAW and Architect.

Not impressed by this new function so far!
11.9 does not exist, does it ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX with Expand Above in Subset

Post by MarenC »

Hi Wim,

Actually I am on the following:

11.8.00800.5
I did a right click TM1sd.exe and details.

I blame looking at the CMPLST file for the previous false information!

Maren
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX with Expand Above in Subset

Post by Wim Gielis »

Thanks !
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply