MDX with Expand Above in Subset

Post Reply
kenship
Regular Participant
Posts: 166
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 » Tue Jan 22, 2019 7:11 pm

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: 805
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 » 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

kenship
Regular Participant
Posts: 166
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 » Wed Jan 23, 2019 1:33 pm

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: 805
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 » Wed Jan 23, 2019 10:00 pm

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: 2786
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX with Expand Above in Subset

Post by Wim Gielis » Thu Jan 24, 2019 7:51 am

That's a good trick Paul !
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis

kenship
Regular Participant
Posts: 166
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 » Thu Jan 24, 2019 3:18 pm

Agree! It's a good solution.

Thanks Paul!

lotsaram
MVP
Posts: 3534
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 » Wed Oct 20, 2021 1:37 pm

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: 175
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 » Wed Oct 20, 2021 2:24 pm

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: 2786
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX with Expand Above in Subset

Post by Wim Gielis » Wed Oct 20, 2021 7:27 pm

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

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis

MarenC
Regular Participant
Posts: 175
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 » 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!

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

Re: MDX with Expand Above in Subset

Post by Wim Gielis » Thu Oct 21, 2021 12:27 pm

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

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis

MarenC
Regular Participant
Posts: 175
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 » Thu Oct 21, 2021 3:01 pm

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: 2786
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX with Expand Above in Subset

Post by Wim Gielis » Thu Oct 21, 2021 8:59 pm

Thanks !
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis

Post Reply