MDX #17 – Can we create a column alias?
In SSRS, we often have another challenge. That is to control the column name.
Continuing from MDX #16 – Attribute [Month Name] Is Invisible.
With this MDX query, we got two columns back from the Query Designer in SSRS. The column [Month Name] represents all members from level [Date].[Month Name].[Month Name].
In SSRS, we often have another challenge. That is to control the column name. This query does not scream for column name control. I’ll only re-use it for demonstration purpose.
Need the month name to come back as “MyTimeInterval”
TSQL developers are all familiar with alias. Using table alias allow us to write more compact and easier-to-read queries. Using column alias allows us to control the column names. In the MDX + SSRS world, occasionally I find myself in the need to have the same column name from different hierarchies. The Date dimension is a good example. I want the column name to be “MyTimeInterval” no matter I am querying from the Month hierarchy or the Quarter hierarchy.
Query-scoped calculated set does not create column alias
My first reaction is to use query-scoped calculated set to “rename” the hierarchy. Calculated set, in some sense, does serve the purpose as the alias does in TSQL.
Interesting enough, the query-scoped calculated set name [MyTimeInterval] is not what returns back from the Query Designer in SSRS. Instead the level name [Month Name] is the column name, even when I sort of “renamed” it to [MyTimeInterval].
A Query-Scoped Calculated Measure Can Create a Column Alias
Since the query-scoped calculated set didn’t really “rename” the column name for me, my next try is to create query-scoped calculated measure, [Measures].[MyTimeInterval], and then place it on the X axis.
I got a new column [MyTimeInterval] back, exactly as how I wanted it to be. Column [MyTimeInterval] and [Month Name] represent the same members from the Month Name attribute. The only difference is that [MyTimeInterval] is the column name I wanted it to be, and the [Month Name] is the original attribute name.