MDX with Expand Above in Subset

Post Reply
kenship
Posts: 123
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
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: 708
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
Posts: 123
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
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: 708
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: 2116
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
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
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

kenship
Posts: 123
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
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!

Post Reply