Search code examples
ssasmdxcube

MDX Query to Filter by Date Dimensions

I am new to MDX queries and am trying to figure out how to filter a result set using date dimensions.

Let's take a cube structured like this (contrived example):

Cube Structure

I would like to give the user a list of projects to select, and display the cost of all events that occurred during the selected projects (i.e. between start date and end date). However, the events are not linked to projects.

Using the query:

SELECT NON EMPTY
{
    [Measures].[Cost]
}
ON COLUMNS,
NON EMPTY
{
    (
        [Project Details].[Project].[Project].ALLMEMBERS
        * [Project Details].[Start Date].[Start Date].ALLMEMBERS
        * [Project Details].[End Date].[End Date].ALLMEMBERS
        * [Event Details].[Date of Occurrence].[Date of Occurrence].ALLMEMBERS
    )
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Cube]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I can get a list of items like this:

Project    Start Date    End Date    Date of Occurrence    Cost
------------------------------------------------------------------
Project 1  01-Jan-15     31-Jan-15   27-Dec-14             750
Project 1  01-Jan-15     31-Jan-15   01-Jan-15             680
Project 1  01-Jan-15     31-Jan-15   02-Jan-15             320
Project 1  01-Jan-15     31-Jan-15   03-Jan-15             150
Project 1  01-Jan-15     31-Jan-15   01-Feb-15             700
Project 1  01-Jan-15     31-Jan-15   05-Feb-15             175

If I run the query for Project 1 only, it should exclude the first event and last 2 events.

Would the best approach be to use a WHERE or FILTER? And because these are dimensions and not measures, how would I do a comparison of WHERE [Date of Occurrence] BETWEEN [Start Date] AND [End Date]?

Any help is much appreciated.

Solution

  • I would try something like this:

    WITH MEMBER [Measures].[Cost in period] AS 
    IIF(
        [Event Details].[Date of Occurrence].CurrentMember.Properties('Key') >= 
          [Project Details].[Start Date].CurrentMember.Properties('Key') &&
        [Event Details].[Date of Occurrence].CurrentMember.Properties('Key') <= 
          [Project Details].[End Date].CurrentMember.Properties('Key'),
    [Measures].[Cost], NULL)
SELECT NON EMPTY
{
    [Measures].[Cost in period]
}
ON COLUMNS,
NON EMPTY
{
    (
        [Project Details].[Project].[Project].ALLMEMBERS
        * [Project Details].[Start Date].[Start Date].ALLMEMBERS
        * [Project Details].[End Date].[End Date].ALLMEMBERS
        * [Event Details].[Date of Occurrence].[Date of Occurrence].ALLMEMBERS
    )
}
ON ROWS
FROM [Cube]

    Basically, you create a calculated measure which is NULL when the Date of Occurrence lies outsite the Start Date - End Date interval. Thanks to NON EMPTY on the Row members, the data should be filtered out of the result.