Need helping getting Member Full Path and Ordinality rotated on columns
I have cube with the following:

  • 1 dimension with 4 levels (dim) in its hierarchy. Example in table:
    GCA = Group Customer A = Level 0, EC = End Customer A = level 1
  • 1 time dimension with Fiscal Year, Quarter and Month Level
  • 2 measures (Revenue and Cost)
  • 2 calculated measures (Gross Margin and Gross Margin%)

I use a LastPeriods statement on columns to display the last X quarters in column
I use a ToggleDrillState statement to drill on the dim and crossjoin this with the 4 measures.
To be able to style the table on the front end, I need the dim MemberFullPath and ordinality.
This gives me the following result:

Code:
DimensionWith4Lvl   Measures        2017Q1             2017Q2            
GCA                 Revenue         100                110               
GCA                 Cost            60                 70                
GCA                 GM              40                 40                
GCA                 GM%             40%                36%               
GCA                 MemberOrdinal   0                  0                 
GCA                 MemberFullPath  [Dim].[GCA]        [Dim].[GCA]       
EndCustA            Revenue         100                110               
EndCustA            Cost            60                 70                
EndCustA            GM              40                 40                
EndCustA            GM%             40%                36%               
EndCustA            MemberOrdinal   1                  1                 
EndCustA            MemberFullPath  [Dim].[GCA].[ECA]  [Dim].[GCA].[ECA]
I need to get, however, the following output:

Code:
DimensionWith4Lvl  Measures   2017Q1   2017Q2  MemberFullPath     MemberOrdinal
GCA                Revenue    100      110     [Dim].[GCA]        0
GCA                Cost       60       70      [Dim].[GCA]        0
GCA                GM         40       40      [Dim].[GCA]        0
GCA                GM%        40%      36%     [Dim].[GCA]        0
EndCustA           Revenue    100      110     [Dim].[GCA].[ECA]  1
EndCustA           Cost       60       70      [Dim].[GCA].[ECA]  1
EndCustA           GM         40       40      [Dim].[GCA].[ECA]  1
EndCustA           GM%        40%      36%     [Dim].[GCA].[ECA]  1
This is my query.

Code:
with member [Measures].[Member Full Path] as '[Revenuedim].CurrentMember.UniqueName'
  member [Measures].[Member Ordinal] as '[Revenuedim].CurrentMember.Ordinal'
  member [Measures].[GM] as '([Measures].[Revenue_USD] - [Measures].[Cost_USD])'
  member [Measures].[GM%] as 'IIF(  [Measures].[Revenue_USD] > 2 , (([Measures].[GM] / [Measures].[Revenue_USD]) * 100), NULL)'
  member [Measures].[Revenue] as '[Measures].[Revenue_USD]'
  member [Measures].[Cost] as '[Measures].[Cost_USD]'
  
select  LastPeriods(4, [Date.YQM].LastChild.LastChild) ON 0,
    
  Crossjoin(
  
  
  order(
     Filter(    
  ToggleDrillState(    
          ToggleDrillState(
                {[Revenuedim].[All GCs]}, {[Revenuedim].[All GCs] }) 
        , {} )
   ,  [Measures].[Revenue] <> 0 OR [Measures].[Cost] <> 0)     
  ,[Measures].[Revenue], DESC) * [Revenuedim].CurrentMember.UniqueName   , {[Measures].[Revenue], 
  [Measures].[Cost], 
  [Measures].[GM],
  [Measures].[GM%],
  [Measures].[Member Full Path],
  [Measures].[Member Ordinal] }) ON 1


from [GM_CUST]