MDX - Display the labels/values for dimensions for this MDX query

Question

Usually I display a value for dimensions by using the CurrentMember.Value/caption as as an alias.

For the following query this breaks with an error along the lines of

'The hierarchy [Measures] appears in more than one axis or in an axis/axes and a slicer too'.

Which I understand.

So how do I edit/expand the query such that I see the book values on rows (normally achieved with

'WITH Member [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION'

and the column dimension values along the top?

(Excel image below, values white color'd out as on client site.)

SELECT 
NON EMPTY ([Ccy].[Ccy].[Ccy]) 
ON COLUMNS , 
NON EMPTY 
([Book].[Book].[Book]) 
ON ROWS  
FROM [TraderCube] 
WHERE 
([Date].[Date].[ALL].[AllMember].[2019-12-12],[Measures].[JTD.SUM])

Answer 1

I have made more progress however this comes out staggered duplicating the row / book label at each repeated currency.

Does anyone know how I would:

1) Get the currencies along the top? 2) Have one row / book label per row?

WITH  
  Member  [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION 
  SELECT 
  NON EMPTY (HIERARCHIZE(([Ccy].[Ccy].Members, {[Measures].[Book_Label],[Measures].[JTD.SUM]}), POST)) 
  ON COLUMNS, 
  NON EMPTY (HIERARCHIZE([Book].[Book].Members, POST)) 
  ON ROWS 
  FROM[TraderCube] WHERE ([Date].[Date].[2019-12-13])"

Answer 2

As an update for anyone who comes across this thread/question, for me I am writing a C# layer that translates simple user words and constructs MDX. I query the remote cube and then I return a 2D array. Up until now I constructed the headerRow etc myself and I returned the row dimension labels using the MDX itself.

I have now discovered that metadata is available on the AdomoClient libraries I am using.

This tutorial has inspired me: https://www.codeproject.com/Articles/28290/Microsoft-Analysis-Services-2005-Displaying-a-grid

It's not a direct answer to my query of getting all of the labels in MDX but it will mean I can construct my headerRow (may be more than one row as seen in Excel pivots) and populate my row labels differently using the positions property of each Axes on the set (Axes[0] is columns, Axes[1] is rows) and the Positions property has a member property too.