Page 1 of 1

MDX with Expand Above in Subset

Posted: Tue Jan 22, 2019 7:11 pm
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

Re: MDX with Expand Above in Subset

Posted: Tue Jan 22, 2019 9:00 pm
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

Re: MDX with Expand Above in Subset

Posted: Wed Jan 23, 2019 1:33 pm
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

Re: MDX with Expand Above in Subset

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

Re: MDX with Expand Above in Subset

Posted: Thu Jan 24, 2019 7:51 am
by Wim Gielis
That's a good trick Paul !

Re: MDX with Expand Above in Subset

Posted: Thu Jan 24, 2019 3:18 pm
by kenship
Agree! It's a good solution.

Thanks Paul!

Re: MDX with Expand Above in Subset

Posted: Wed Oct 20, 2021 1:37 pm
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?)

Re: MDX with Expand Above in Subset

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

Re: MDX with Expand Above in Subset

Posted: Wed Oct 20, 2021 7:27 pm
by Wim Gielis
Works fine for me, server version 11.8

Code: Select all

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

Re: MDX with Expand Above in Subset

Posted: Thu Oct 21, 2021 8:24 am
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!

Re: MDX with Expand Above in Subset

Posted: Thu Oct 21, 2021 12:27 pm
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 ?

Re: MDX with Expand Above in Subset

Posted: Thu Oct 21, 2021 3:01 pm
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

Re: MDX with Expand Above in Subset

Posted: Thu Oct 21, 2021 8:59 pm
by Wim Gielis
Thanks !