Search code examples
sql-serverreporting-servicesssasmdx

MDX Query: Trying to bring back calculated years

I have a piece of a report which currently works fine with hard-coded years, I am trying to make them a bit more dynamic so as to show the current year and previous year returns to avoid having to update this every year. This is a simple thing to do in SQL, but I'm having a much harder time figuring this out in MDX, which I am still learning. The row in question is the [Date].[Year].&[2013]:[Date].[Year].&[2014]. Here is my current query:

SELECT  {   
        [Measures].[Users],
        [Measures].[Sessions]
    } ON COLUMNS,
    (   
        {[User Type].[Description].&[Customer], [User Type].[Description].&[Vendor]},
        [Date].[Year Month].[Year Month],
        [Date].[Month Name].[Month Name],
        [Date].[Year].&[2013]:[Date].[Year].&[2014]
    ) ON ROWS
FROM    [My Cube]

Thanks for any help.

Solution

  • You can't use a range inside a tuple. You first need to create a member.

    Put this in before the SELECT clause:

    WITH Member [Date].[Periods] as Aggregate( [Date].[Year].&[2013]:[Date].[Year].&[2014])

    and then replace the range in your tuple by [Date].[Periods]