Search code examples
filterssasmdxwhere-clause

MDX Query Filter

I have a situation where i need to filter based on the column (DatetimeA). The values in DatetimeA are in this format 19990101(yyyyMMdd). I need to filter my data to only show the last 60 days.

Mdx query:

    SELECT NON EMPTY {Measures].[DateA] } ON COLUMNS
    ,NON EMPTY { [Date].[DatetimeA].[DatetimeA].ALLMEMBERS ) 
    }DIMENSION PROPERTIES MEMBER_CAPTION
    ,MEMBER_UNIQUE_NAME ON ROWS
    FROM (
    SELECT ([Date].[DatetimeA].& [20170118] : [Date].[DatetimeA].[20170318]) 
    ON COLUMNS
    FROM [TABMODEL]
    ) CELL PROPERTIES VALUE
    ,BACK COLOR
    ,FORE COLOR
    ,FORMATTED_VALUE
    ,FORMAT_STRING
    ,FONT_NAME
    ,FONT_SIZE
    ,FONT_FLAGS

How can we filter the DatetimeA column to only show the last 60 days? Instead of hard coding the values, I need something thats changes based on the day. In ssrs this would be something like DatetimeA between (format(dateadd("d",-60,today()),"yyyyMMdd") and (format(dateadd("d",0,today()),"yyyyMMdd"). Please include the changes in the query above. Thanks!

Solution

  • The code below must generate a dynamic set on the SSAS side:

    {
    StrToMember('[Date].[DatetimeA].[DatetimeA].&[' + Format(Now(),'yyyyMMdd') + ']')
    :
    StrToMember('[Date].[DatetimeA].[DatetimeA].&[' + Format(Now(),'yyyyMMdd') + ']').Lag(59)
}

    Plus, you can add a SSRS parameter to get the current date and StrToMember it into the MDX code as well.