Search code examples
mdxolap-cube

Pivot in MDX based on a dimension

I have am mdx query something like

 SELECT NON EMPTY { [Measures].[Value] } ON COLUMNS, NON EMPTY { 
([Account].[Account List].[Account List].ALLMEMBERS * [Property].[Property].[Property].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [My Cube]

Currently i write the result to a physical temporary table and use "pivot" command to pivot based on the "Property". I am trying to avoid this intermediate step and do the pivot directly in MDX. Is it possible? I did try something using Hierarchize, but then there are 20 over dimension which i need to join and after adding 4 os so propertied, it gives out of memory.

Also i was trying to do something like

Select A.* from (MyQuery) As A Pivot (fields) piv

The above did'nt work as well within MDX. How do i achieve this?

Solution

  • Just move it to columns:

     SELECT 
NON EMPTY [Property].[Property].[Property].ALLMEMBERS ON 0, 
NON EMPTY { 
([Account].[Account List].[Account List].ALLMEMBERS ON 1 
FROM [My Cube]
WHERE Measures].[Value];