# [Mondrian] VisualTotals and Excel 2007

Calum Miller calum at millersoft.ltd.uk
Tue Dec 22 07:17:45 EST 2009

Hi Julian,

We have just noticed that the test below is ineffectual, as
[Customers].[All Customers].[USA] is the All Member, the test here is
more robust http://jira.pentaho.com/browse/MONDRIAN-668

Thanks again

Calum

public void testVisualTotalsIntersect() {
assertQueryReturns(
"WITH\n"
+ "SET [XL_Row_Dim_0] AS
'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
Customers].[USA]), Descendants([Customers].[All Customers].[USA])})))'
\n"
+ "SELECT \n"
+ "NON EMPTY Hierarchize({[Time].[Year].members}) ON
COLUMNS , \n"
+ "NON EMPTY
Hierarchize(Intersect({DrilldownLevel({[Customers].[All Customers]})},
[XL_Row_Dim_0])) ON ROWS \n"
+ "FROM [Sales] \n"
+ "WHERE ([Measures].[Store Sales])",
"Axis #0:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #1:\n"
+ "{[Time].[1997]}\n"
+ "Axis #2:\n"
+ "{[Customers].[All Customers]}\n"
+ "{[Customers].[All Customers].[USA]}\n"
+ "Row #0: 565,238.13\n"
+ "Row #1: 565,238.13\n");
}

On 21 Dec 2009, at 16:55, Julian Hyde wrote:

>> Calum Miller wrote:
>>
>> The following query against the FoodMart Database yields different
>> results in SSAS and Mondrian
>
> First of all, make sure that you have the fix to
> http://jira.pentaho.com/browse/MONDRIAN-295. It is fixed only on the
> main
> branch currently.
>
> If that doesn't work, please log another jira case.
>
>> The queries from Excel are dynamic, so I need a clean way to apply
>> visual totals by default to each axis, any thoughts?
>
> Are you asking for a way to apply visualtotals even though it isn't
> explicitly specified in the MDX query? I'd rather not do that unless
> there
> is a very good reason.
>
> Julian
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

Calum Miller
calum at millersoft.ltd.uk
http://www.millersoft.ltd.uk
Telephone: +44 (0) 131 208 0202
Mobile: +44 (0) 7973 249 621
Skype: calummiller

-------------- next part --------------
A non-text attachment was scrubbed...
Name: millersoft-mail.logo.jpg
Type: image/jpeg
Size: 8712 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20091222/474ca698/attachment.jpg
-------------- next part --------------