MDX with Expand Above in Subset

[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

[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]

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

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

[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]

That's a good trick Paul !

[kenship]

Agree! It's a good solution.

Thanks Paul!

[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?)

[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:



I was expecting the following:



Maren

[Wim Gielis]

Works fine for me, server version 11.8

Code: Select all

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

[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]

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 ?

[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]

Thanks !