MDX Column Alias for use in SQL

sql tsql ssas mdx olap-cube
Will Wainwright picture Will Wainwright · Feb 10, 2014 · Viewed 8.6k times · Source

I'm trying to query our SSAS cubes and return the values into a SQL view for use as parameters in cube based reports.

I need to return 3 values for financial seasons based on the date hierarchy.

This is the below code, which returns the correct data.

WITH 
    MEMBER [CurrentHalf] AS [Date - Master].[Financial Calendar].CURRENTMEMBER.UNIQUENAME
    MEMBER [NextHalf] AS [Date - Master].[Financial Calendar].LEAD(1).UNIQUENAME
    MEMBER [PreviousHalf] AS [Date - Master].[Financial Calendar].LEAD(-1).UNIQUENAME

SELECT {[Date - Master].[Financial Calendar].[Season Half]} ON COLUMNS , 
        {[CurrentHalf],NextHalf,PreviousHalf} ON ROWS
FROM [Sales and Stock]

WHERE  (Filter([Date - Master].[DateKey].Members,[Date - Master].[DateKey].MemberValue = (format(now(),"dd/MM/yyyy"))))

However the column name for [Date - Master].[Financial Calendar].[Season Half] returns the caption value (e.g. 2014- Autumn /Winter).

See image:

enter image description here

Normally this would be fine however as I am passing it into SQL I need a column name to select off. What I need is to rename this column to something else (e.g. HalfName)

I have tried

WITH 
    MEMBER [CurrentHalf] AS [Date - Master].[Financial Calendar].CURRENTMEMBER.UNIQUENAME
    MEMBER [NextHalf] AS [Date - Master].[Financial Calendar].LEAD(1).UNIQUENAME
    MEMBER [PreviousHalf] AS [Date - Master].[Financial Calendar].LEAD(-1).UNIQUENAME
    MEMBER [HalfName] as [Date - Master].[Financial Calendar].[Season Half].CURRENTMEMBER.VALUE

SELECT {[HalfName]} ON COLUMNS , 
        {[CurrentHalf],NextHalf,PreviousHalf} ON ROWS
FROM [Sales and Stock]

WHERE  (Filter([Date - Master].[DateKey].Members,[Date - Master].[DateKey].MemberValue = (format(now(),"dd/MM/yyyy"))))

But that still didn't work.

Answer

Timothy Walters picture Timothy Walters · Feb 10, 2014

When you define MEMBER expressions as you have, it infers you are creating them on the [Measures] dimension, so the full name of your [HalfName] column is [Measures].[HalfName].

Also, I don't see where SQL/T-SQL come into this, unless you are mistaking your MDX for SQL (they look similar as they share some syntax, but are very different beasts).

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More