[Mondrian] MDX from Excel 2007

[Will Gorman]

Good news, Mondrian returns the exact values below when running this
query verbatim.

Will

On Wed, 2007-06-13 at 19:49 +0400, Anton Nikitin wrote:
> 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
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian