Search code examples
ssasmdxcube

Rename the Dimension Headers in MDX Queries

I need to rename dimension headers in the result table for example I can change measure names like following:

WITH Member x AS [Measures].[Num of Project]
SELECT
NON Empty {x} ON COLUMNS,
NON Empty {[DimensionName].[HierarchyName].[LevelName]} on ROWS
FROM [CubeName]

And this MDX Query return a table as a result in Analysis Server's interface like that:

|---------------------|------------------|
|     LevelName       |         x        |
|---------------------|------------------|
|     Member 1        |         34       |
|---------------------|------------------|
|     Member 2        |         12       |
|---------------------|------------------|
                      .
                      .
                      .

But I have to rename the displayed dimension's level names for example I want to rename 'LevelName' to 'Level 1' as displayed name in table headers. I tried the following MDX Query:

WITH Member x AS [Measures].[Num of Project]
SELECT
NON Empty {x} ON COLUMNS,
NON Empty {[DimensionName].[HierarchyName].[LevelName]} as [Level 1] on ROWS
FROM [CubeName]

But it neither works nor excepts the error. 'LevelName' is still displayed in table header, not 'Level 1'. I need that in Adomd Connection to distinguish dimensions from each other for some calculations. I coudn't find any answer in anywhere. I am new in MDX, I hope that I can explain my problem clearly.

Solution

  • You can do the same thing for a dimension as you did to the measure. For instance you can do something like this (you may need to make some changes to adapt to your case)

    WITH Member [Measures].[x] AS [Measures].[Num of Project]
     Member [Measures].[Level 1] AS [DimensionName].[HierarchyName].CurrentMember.membervalue
SELECT
NON Empty {[Measures].[x], [Measures].[Level 1]} ON COLUMNS,
NON Empty {[DimensionName].[HierarchyName].[LevelName]} on ROWS
FROM [CubeName]

    As you may notice, this doesn't rename the header of the dimension (which should usually be done by the reporting tool) rather, it will create a new column in your data set with the desired header.