How can MDX query take parameters to filter by Fact Table datetime attribute

Question

We have a fact table that has a datetime field [Log Entry].[Time Entry].

We would like to filter the following query based on a date range: where the [Log Entry].[Time Entry] was between two supplied dates (2 parameters: i.e. startdate and enddate)

SELECT 
  NON EMPTY { [Measures].[Average Total Time] } ON COLUMNS, 
  NON EMPTY { ([User Property].[UserProperty].[Id].ALLMEMBERS * [Log Entry].[Time Entry].[Logged On].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [MyCube]

I was hoping to avoid having to create Time Table/dimension and being able to compare those dates (passed in via parameters) against the [Log Entry].[Time Entry].

Concept of SQL's between (somedate Between date1 and date2).

How can we do this?

Dalex Dalex · Accepted Answer · 2013-03-15T09:09:16

You should use range expression. Please look here:http://msdn.microsoft.com/en-us/library/ms146001%28v=sql.105%29.aspx

Your WHERE clause should look for example:

  WHERE (
    [Time].[Month Name].&[August 2009]
    :
    [Time].[Month Name].&[June 2012]
    )

UPDATE To Filter Axis inside it, you should use EXISTS clause:

    * {EXISTS
   (
      {[Log Entry].[Time Entry].[Logged On].ALLMEMBERS}
    ,
    {[Log Entry].[Time Entry].&[20120101]:[Log Entry].[Time Entry].&[20130101]}
    )

How can MDX query take parameters to filter by Fact Table datetime attribute

1 Answers