Business Query Totaling Use Cases.  

Example 1: Grand totaling on columns as defined in the example below.  

MDX Generated:

WITH SET [&1DTE] AS '.[DTE].MEMBERS'

SET [&1CARS] AS '[CARS].[CAR].MEMBERS' 

SET [COL] AS 'CROSSJOIN([&1DTE], [&1CARS]'

MEMBER .[All DATE].[Total] AS 'AGGREGATE([COL])'
SET [DTETOTAL ] AS '{.[All DATE].[Total]}'

MEMBER [CARS].[ ] AS 'AGGREGATE([COL])'
SET [CARSTOTAL ] AS '{[CARS].[ ]}'
MEMBER [Measures].[&1SUM OF SALES] AS '[Measures].[SUM OF SALES]'
SELECT ({[Measures].[&1SUM OF SALSES]}) ON COLUMNS ,
UNION([COL], CROSSJOIN({[DTETOTAL ]}, {[CARSTOTAL ]})) ON ROWS FROM MDDBCARS

Sample Code to use:

BusinessQuery bq = (BusinessQuery) dataSelection;
dataSelection.setTotalType(bq.GRANDTOTAL, Role.ROW);

 

Example 2: Sub-totaling on columns as defined in the example below.   

MDX generated automatically:

WITH SET [&1DTE] AS '.[DTE].MEMBERS'

SET [&1CARS] AS '[CARS].[CAR].MEMBERS' 

SET [COL] AS 'CROSSJOIN([&1DTE], [&1CARS]'

MEMBER [Measures].[&1SUM OF SALES] AS '[Measures].[SUM OF SALES]'
member [cars].[subtotal] as 'aggregate(intersect([COL], crossjoin({[date].currentmember}, [&1CARS] )))'
set [set2] as 'union([COL], crossjoin({[&1DTE] }, {[cars].[subtotal]}))'
set [final] as 'hierarchize([set2])'
select { [Measures].[&1SUM OF SALES] } on columns, non empty[final] on rows from mddbcars 

Sample Code to use:

BusinessQuery bq = (BusinessQuery) dataSelection;
dataSelection.setTotalType(bq.SUBTOTAL, Role.ROW);

 

Example 3: All Total (both sub-total and grand total) on columns  as defined in the example below.    

MDX Generated:

WITH SET [&1DTE] AS '.[DTE].MEMBERS'

SET [&1CARS] AS '[CARS].[CAR].MEMBERS' 

SET [COL] AS 'CROSSJOIN( [&1DTE], [&1CARS]'

MEMBER .[All DATE].[Total] AS 'AGGREGATE([COL])'
SET [DTETOTAL ] AS '{.[All DATE].[Total]}'

set [GRANDTOTAL] as '{[&1DTE],[DTETOTAL ] }'

MEMBER [Measures].[&1SUM OF SALES] AS '[Measures].[SUM OF SALES]'
member [cars].[subtotal] as 'aggregate(intersect([COL], crossjoin({[date].currentmember}, [&1CARS] )))'
set [set2] as 'union([COL], crossjoin({[GRANDTOTAL] }, {[cars].[subtotal]}))'
set [final] as 'hierarchize([set2])'

Sample Code to use:

BusinessQuery bq = (BusinessQuery) dataSelection;
dataSelection.setTotalType(bq.ALLTOTAL, Role.ROW);