[Mondrian] MDX from Excel 2007

Anton Nikitin cybernelly at gmail.com
Wed Jun 13 11:49:22 EDT 2007


Will,

I've tried to reproduce this MDX one more time. Actually it is a bit more
complicated in Excel 2007 (the difference only in DIMENSION/CELL
PROPERTIES):

WITH  SET [XL_Row_Dim_0] AS
'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
Customers].[USA]), Descendants([Customers].[All Customers].[USA])})))'
SELECT NON EMPTY Hierarchize({[Time].[Year].members}) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY
Hierarchize(Intersect({DrilldownLevel({[Customers].[All Customers]})},
[XL_Row_Dim_0])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM
[Sales] WHERE ([Measures].[Store Sales]) CELL PROPERTIES VALUE,
FORMAT_STRING, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MSAS-2000 result is:

Store Sales		Column Labels
Row Labels		1997
USA			565238.13
Grand Total		565238.13

(Hope I haven't made any changes in my MS Foodmart database...)

Please keep in mind that I didn't originally used Pentaho Spreadsheet
Services to check this MDX in Mondrian - but simply typed it in AD-hoc query
test JSP. It is possible, Excel will generate a bit different MDX when
connecting to Spreadsheet Services ODBO Provider...

Regards,
Anton

-----Original Message-----
From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Will Gorman
Sent: Tuesday, June 12, 2007 5:29 PM
To: Mondrian developer mailing list
Subject: Re: [Mondrian] MDX from Excel 2007

Anton,

Would it be possible to post the expected results from Excel 2007 / MSAS
2000 on this query? I'm in the process of getting access to a MSAS
server but it may be a few days.

Thanks!

Will

On Wed, 2007-05-30 at 19:17 +0400, Anton Nikitin wrote:
> Today I was experimenting with Excel 2007 pivot tables and found that
> it generates a bit crazy MDX statements. 
> 
>  
> 
> For example, very simple GUI navigation steps cause the following MDX:
> 
>  
> 
> WITH  
> 
>     SET [XL_Row_Dim_0] AS
> 'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
> Customers].[USA]), Descendants([Customers].[All
> Customers].[USA])})))'  
> 
> SELECT 
> 
>     NON EMPTY Hierarchize({[Time].[Year].members}) ON COLUMNS , 
> 
>     NON EMPTY Hierarchize(Intersect({DrilldownLevel({[Customers].[All
> Customers]})}, [XL_Row_Dim_0])) ON ROWS  
> 
> FROM [Sales] 
> 
> WHERE ([Measures].[Store Sales])
> 
>  
> 
> Mondrian doesn't correctly processes this one (results differ from
> MSAS 2000).
> 
>  
> 
> Anton 
> 
>  
> 
> P.S. IMHO it may concern Pentaho Spreadsheet Services
> 
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian




More information about the Mondrian mailing list