Post reply

MDX during period calculation

  • November 10, 2011 at 8:18 pm

    #146623

    Hi All,

    I'm quite new to the MDX. I have a problem with the time dimension. I have a measure which is counting numbers of student enrolments. I want to know how many enrolments are in a period; Enrolment Start Date <= 31 Dec 2010 and Enrolment End Date >= 1 Jan 2010. To get the measure of the enrolment count I can do this;

    SELECT [Measures].[Enrolment Count] ON COLUMNS

    FROM [Student Enrolment]

    WHERE (NULL : [Enrolement Start Date].[Calendar Date].[Date].[2010-12-31 00:00:00.000], [Enrolment End Date].[Calendar Date].[Date].[2010-01-01 00:00:00.000] : NULL);

    But how can I include MonthName from the date dimension (ON ROWS) in my result so it looks like;

    Month Enrolment Count

    Jan 250

    Feb 550

    Mar 280

    .

    .

    .

    Dec 250

    Thanks

  • November 11, 2011 at 8:22 pm

    #1406629

    Can you not just use a set?

    WITH SET [DateRange]

    AS

    ( Filter([Enrolement Start Date].[Calendar Date].[Date].Members, [Enrolement Start Date].[Calendar Date].[Date] <=[Enrolement Start Date].[Calendar Date].[Date].&[WhateverDateKey] AND [Enrolement Start Date].[Calendar Date].[Date] >= [Enrolement Start Date].[Calendar Date].[Date].&[WhateverDateKey])

    )

    SELECT [Measures].[Enrolment Count] ON COLUMNS,

    [DateRange] ON ROWS

    FROM [Student Enrolment]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply