mdxmdx-querymultidimensional-cube

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

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])

enter image description here

Solution

  • 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.