I would like to create a TopCount over multiple dimensions, and include a "rest"/"remainder" and a sub-total.
I use the following MDX on the default Sales schema in icCube:
with
member [Product].[Product].[All Products].[rest] as "All Products - top 2"
SET [top] AS
Generate( { {[Customers].[Geography].[Region] } * [Time].[Calendar].[2010] } as s1,
TopCount( s1.CurrentMember * [Product].[Product].[Article].Members, 2, [Measures].[Amount] ) + s1.CurrentMember * {[Product].[Product].[All Products].[rest] , [Product].[Product].[All Products]} )
select
[Measures].[Amount] on 0
[top] on rows
from sales
The result is in the following picture.
How to get a value for "rest"? Using the formula: "All Products" -/- the Top 2
The answer will depend on whether the dimension includes many-to-many relations or not.
If there are no many-to-many, you can either use the SubCubeComplement
function:
MEMBER [Product].[Product].[All Products].[rest] as Eval( SubCubeComplement( TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]) ) , [Product].[Product].defaultMember )
or calculate it (all minus the sum of the TopCount set):
MEMBER [Product].[Product].[All Products].[rest] as ([Product].[Product].defaultMember) - Sum( TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]), [Product].[Product].currentMember )
The risk here is that in case you have many-to-many relations, the two solutions above can be subtracting unwanted rows (as they may contain articles that should have been in the final set.)
Therefore, if you have many-to-many relations use the Eval
function with the following syntax:
MEMBER [Product].[Product].[All Products].[rest] as Eval( [Product].[Product].[Article].Members - TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]), [Product].[Product].defaultMember )
The statement will therefore be (please note the adjustment on the [top]
set definition):
with
// v1 (no many-to-many) - behaves like a FILTERBY
// MEMBER [Product].[Product].[All Products].[rest] as Eval( SubCubeComplement( TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]) ) , [Product].[Product].defaultMember )
//v2 (no many-to-many)
// MEMBER [Product].[Product].[All Products].[rest] as ([Product].[Product].defaultMember) - Sum( TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]), [Product].[Product].currentMember )
//v3 (many-to-many)
MEMBER [Product].[Product].[All Products].[rest] as Eval( [Product].[Product].[Article].Members - TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]), [Product].[Product].defaultMember )
SET [top] AS
Generate( { {[Customers].[Geography].[Region] } * [Time].[Calendar].[2010] } as s1,
s1.CurrentMember * TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount] ) + s1.CurrentMember * {[Product].[Product].[All Products].[rest] , [Product].[Product].[All Products]} )
select
[Measures].[Amount] on 0
[top] on rows
from sales
Version 3 will also work if there are no many-to-many relations.