Home > MDX Challenges > MDX #17 – Can we create a column alias?

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

clip_image001

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

clip_image002

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.

clip_image003

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.

Categories: MDX Challenges Tags: ,
  1. Michele Southerland
    November 1, 2012 at 4:15 pm

    Hi Sherry,

    This is great! It almost solves the problem that I’m having.

    I have a parameter (@GeographyType) which cascades to another parameter (@GeographySelection). Therefore, the value of @GeographySelection can be something like [Property].[City].&[Chicago] or [Property].[State].&[IL].

    When I run the MDX query, the column name for @GeographySelection changes – in this example, it would be City or State.

    I’ve added the code

    WITH MEMBER [Measures].[GeoLabel] AS @GeographySelection

    SELECT NON EMPTY {
    [Measures].[Measure 1],
    .
    .
    .
    [Measures].[GeoLabel]} AS COLUMNS,
    ….(rest of query)

    It runs & I get the “GeoLabel” columns, but it’s blank. I’ve tried using variations of your approach, but I can’t get it to work right.

    Is this something you can help me with?

    Thanks in advance.

    Michele

    Like

    • Michele Southerland
      November 2, 2012 at 2:10 pm

      Never mind. I figured it out with a CASE statement 🙂

      Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: