[Mondrian] VisualTotals and Excel 2007

[Calum Miller]

Hi

The following query against the FoodMart Database yields different  
results in SSAS and Mondrian
select NON EMPTY Hierarchize(
                     Intersect({[Store].[All Stores].[USA],[Store]. 
[All Stores].[USA].[CA]},
                         VisualTotals(
                             hierarchize({[Store].[All Stores].[USA], 
[Store].[All Stores].[USA].[CA]})))) ON COLUMNS
from [Sales]
where [Measures].[Sales count]


Result Excel 2007 -> SSAS
-------------------------------------
Axis #0:
{[Measures].[Sales Count]}
Axis #1:
{[Store].[All Stores].[USA]}
{[Store].[All Stores].[USA].[CA]}
Row #0: 24,442
Row #0: 24,442

Result Excel 2007 -> Mondrian
-----------------------------------------
Axis #0:
{[Measures].[Sales Count]}
Axis #1:
{[Store].[All Stores].[USA]}
{[Store].[All Stores].[USA].[CA]}
Row #0: 86,837
Row #0: 24,442


Mondrian requires the VisualTotals function to be the outer function,  
so the following query works as expected:

select NON EMPTY Hierarchize(VisualTotals(Intersect({[Store].[All  
Stores].[USA],[Store].[All Stores].[USA].[CA]},
                           {[Store].[All Stores].[USA],[Store].[All  
Stores].[USA].[CA]}))) ON COLUMNS
from [Sales]
where [Measures].[Sales count]

Axis #0:
{[Measures].[Sales Count]}
Axis #1:
{[Store].[All Stores].[USA]}
{[Store].[All Stores].[USA].[CA]}
Row #0: 24,442
Row #0: 24,442

The queries from Excel are dynamic, so I need a clean way to apply  
visual totals by default to each axis, any thoughts?

Calum