Search code examples
ssasmdxmdx-query

MDX Query change column title

These are my first mdx queries. I would like to execute some queries from a C# application and convert the results to a JSON file or a tabular format.

So I need the columns titles, you can see in the picture in attachment the columns title is empty, how can I change them in the mdx query ?

SELECT { [Measures].[Nombre Inscrits] } ON COLUMNS, 
{ ([ZONE CLIENT].[ID ZONE].[ID ZONE].ALLMEMBERS * [DimDate].[Year].[Year].ALLMEMBERS)}
ON ROWS FROM [Loisirs cube]

No title in columns

Solution

  • The empty cells are empty because in the result space if a name is given to them, it will not make sense, I would suggest you handle it in your code. However for the sake of learning you can use the below query , and ignore the first two columns of the result

    with member
[measures].[ID ZONE] as 
[ID ZONE].[ID ZONE].currentmember.name

[measures].[Year] as 
[DimDate].[Year].currentmember.name


SELECT 
{[measures].[ID ZONE],[measures].[Year], [Measures].[Nombre Inscrits] } ON COLUMNS,
{ ([ZONE CLIENT].[ID ZONE].[ID ZONE].ALLMEMBERS * [DimDate].[Year].[Year].ALLMEMBERS)}
ON ROWS FROM [Loisirs cube]

    So here a sample on adventure works 

    SELECT 
{ [Measures].[Internet Sales Amount] } ON COLUMNS, 
{ ([Product].[Category].[Category].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS)}
ON ROWS 
FROM [Adventure Works]

    Result enter image description here

    Now lets modify the query

    with member 
 [Measures].[Category]
 as 
 [Product].[Category].currentmember.name

member [Measures].[Year]
as 
[Date].[Calendar Year].currentmember.name

SELECT 
{ [Measures].[Category], [Measures].[Year],[Measures].[Internet Sales Amount] } ON COLUMNS, 
{ ([Product].[Category].[Category].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS)}
ON ROWS 
FROM [Adventure Works]

    Result

    enter image description here