We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Strange MDX query

HI Sincfusion,

We are looking for OLAP component, and your is pretty good. But MDX queries make me sad.

We made some simple query with filter, and MDX query look like a big ugly monster.

Let me show you example.

SELECT   NON EMPTY({[Measures].[Order Count]})  dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME  ON COLUMNS ,  NONEMPTY( VISUALTOTALS( ({{hierarchize(Union(Intersect(VISUALTOTALS({ Except(Descendants([Date].[Calendar], 1, SELF_AND_BEFORE) , {[Date].[Calendar].[Calendar Year].&[2005], [Date].[Calendar].[Calendar Year].&[2006], [Date].[Calendar].[Calendar Year].&[2007], [Date].[Calendar].[Calendar Year].&[2008], [Date].[Calendar].[Calendar Year].&[2009]}) }), Drilldownlevel({ [Date].[Calendar] })) , Except(Drilldownlevel({ [Date].[Calendar] }) , {[Date].[Calendar].[Calendar Year].&[2005], [Date].[Calendar].[Calendar Year].&[2006], [Date].[Calendar].[Calendar Year].&[2007], [Date].[Calendar].[Calendar Year].&[2008], [Date].[Calendar].[Calendar Year].&[2009]})))}}) ),{[Measures].[Order Count]})  dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME  ON ROWS  FROM [Adventure Works]  CELL PROPERTIES VALUE, FORMAT_STRING, FORMATTED_VALUE

How this query is in EXCEL

SELECT NON EMPTY Hierarchize({DrilldownLevel({DrilldownLevel({[Date].[Calendar].[All Periods]},,,INCLUDE_CALC_MEMBERS)},[Date].[Calendar].[Calendar Year],INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Date].[Calendar].[Calendar Semester].[Calendar Year] ON COLUMNS  FROM (SELECT ({[Date].[Calendar].[Calendar Year].&[2010]}) ON COLUMNS  FROM [Adventure Works]) WHERE ([Measures].[Order Count]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Differences are obvious. Your mdx designer made it using "EXCEPT". That very tough way for OLAP CUBE, and requires much resources to execute.

Why is that and can you change it?

Kind regard, Aleaxander.

1 Reply

JA Jesus Arockia Sankaran S Syncfusion Team February 5, 2015 01:30 PM UTC

Hi Alexander,

Thanks for your interest in Syncfusion products.

We have updated the response in incident 134883 which you have created. Please refer the incident for further follow up.


Jesus Arockia Sankaran S

Live Chat Icon For mobile
Up arrow icon