MDX filter with alias

Im trying to get a list of years from a Date dimension that includes a limited number of values.  I want the current year and the previous 5 years.  The year number in the cube is actually a char(4) value so I will have to cast that.

Also I want to alias the field, since Im using this list to use as a parameter in an SSRS report.

The MDX I have at the moment is this:<br/>

WITH
MEMBER [SALESYEAR] AS
CInt([DimCalendar].[Year].CURRENTMEMBER)

SELECT 
NULL ON COLUMNS,FILTER(
[DimCalendar].[Year].MEMBERS , [SALESYEAR] > 2012) ON ROWS
FROM [MySalescube]

The above returns empty, but I also need to define an expression that is the current year - 5 years


  • Edited by shiftbit 18 hours 9 minutes ago sdfgsdfg
September 9th, 2015 8:55am

Why not use ParallelPeriod() ?
 like ParallelPeriod(Year,5,Currentyear):Currentyear
  • Edited by yger 17 hours 58 minutes ago
Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 9:06am

I think im heading over to pluralsight to review MDX basics because Im obviously not productive with it yet.  If it was tsql I would have been done already :)
September 9th, 2015 9:34am

Hi shiftbit,

In MDX, if you want to convert string into number, you should use STRTOVALUE() instead of CINT(). For your above query, you modify it like:

WITH
 MEMBER [SALESYEAR] AS
[DimCalendar].[Year].CURRENTMEMBER.member_key

 SELECT 
 NULL ON COLUMNS,FILTER(
 [DimCalendar].[Year].MEMBERS , STRTOVALUE([SALESYEAR]) > 2012) ON ROWS
 FROM [MySalescube] 

Since the where clause in MDX is different from TSQL, we can only specify date range the get the set of years as yger suggested.

TAIL(null:STRTOMEMBER("[Date].[Calendar].[Calendar Year].&["+Format(NOW(),"yyyy")+"]"),6)

Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 11:43pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics