MDX - Dimension on rows and Measure and a Dimension on Columns

Question

I am now adding to something I am writing and need to offer the user the ability to place dimensions and measures on either the rows or columns.

I am about to test an idea to make the MDX for the example in the picture but realised I should also ask any MDX experts too!

So how would one go about pulling off this sort of layout? I cannot view the MDX generated by this Excel (data redacted due to being on site somewhere, also why I cannot download addins for MDX generator etc)

Answer 1

I actually did manage to get the OLAP extension addin installed.

https://github.com/OlapPivotTableExtensions/OlapPivotTableExtensions/releases/tag/v0.8.4

This will help me add some stuff to my MDX generator and prove invaluable as you can see the MDX Excel generates.

SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Ccy].[Ccy].[ALL].[AllMember]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Book].[Book].[ALL].[AllMember]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS  FROM [TraderCube] WHERE ([Date].[Date].[ALL].[AllMember].[2019-12-12],[Measures].[JTD.SUM]) CELL PROPERTIES VALUE