TM1 Forum

Good morning/afternoon TM1 gurus,

I am working with dynamic subsets and MDX in active forms, so far I’ve created a simple report to show the top 10 debtors of an AR cube sorted by the total overdue amount and excluding an specific customer called “Legal”.
{ EXCEPT(
{ORDER(
{ TOPCOUNT(
{ [Customer].[All Customers].Children} ,10, [AR].([AR measures].[Total OVERDUE]) ) } ,
[AR].([AR measures].[Total OVERDUE]) , DESC) } ,
{ [Customer].[Legal] } ) }

The MDX works perfectly as well as the dynamic subset when browsing the cube. Now what I need to include are 2 things but I’m not sure if it’s possible or how to do it:

1- Include a subtotal of the top 10 at the bottom of the list (kind of a rollup).
2- Get the total for all customers less the top 10 subtotal and show it as “other”.
See attached example…

I could do this manually in the active form however I’d like to fully automate it, Can this be done in the cube viewer and then save if as a view….

Your help will be greatly appreciated…

Well you can insert a subset into another subset and as a result rollup the members of a subset into a sub-total. This might address displaying the total of the top 10 in a cube view (albeit probably not with a helpful display name) but to calculate the delta between total and the top 10 as "other" in cube viewer I don't think this could be done as rules can't reference subsets. It could probably be done as a calculated member in the UI in another viewer like EV or Report Studio but the native cube viewer simply doesn't support this.

I think this is a case where you need to embrace Excel as with an active form, additional DBRW to get the total and a simple A - B excel formula this is very easy to do within an Excel active form report.

LORR wrote:1- Include a subtotal of the top 10 at the bottom of the list (kind of a rollup).
2- Get the total for all customers less the top 10 subtotal and show it as “other”.

Have you considered using TI to build a hierarchy in your dimension showing the point-in-time-top-ten to use in your report? That way you could achieve 1 and 2.
You could use the SubsetCreateByMdx command using the MDX you posted and then process the resulting subset to create both the Top Ten and Not Top Ten hierarchies which you could then reference in your Active Form.

Thank you for your posts, as mentioned by lotsaram I think I’ll go for the active form solution, however I’ll explore rmackenzie’s suggestion and see how I go, thank you again for the handy tips and any other suggestions are more than welcome.