Search code examples
sqlsql-serverssasmdxcube

Return data between two dates from a MDX Query SSAS

I'm trying to filter data between two date ranges. Its data type is datetime. I have generated the query via the Query designer in SSAS.

Below is sample of the dataset I have:

enter image description here

Sample image of Measure groups and dimensions:

enter image description here

Sample Filter I have used:

enter image description here

Generated MDX Query:

`SELECT NON EMPTY { [Measures].[Status] } ON COLUMNS, NON EMPTY { ([Lobby].[Added Local Time].[Added Local Time].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Lobby].[Added Local Time].&[2020-01-02T10:32:37.806667] : [Lobby].[Added Local Time].&[2020-02-19T13:43:13.833333] ) ON COLUMNS FROM ( SELECT ( { [Lobby].[Status].[All] } ) ON COLUMNS FROM [LTS KROI DEMO])) WHERE ( [Lobby].[Status].[All] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS`

Problem:

Issue is that it doesn't filter the data according to the given datetime ranges. Neither gives any error.

If I use the only the Filter - Status a specific value without giving all it all works fine.

Solution

  • I was able get the desired result by following the answer of @GregGalloway by making a small change to the parameter I passed as the date. When I removed time passing the date to the Cdate function it work fine.

    SELECT { [Measures].[Status] } ON COLUMNS, NON EMPTY { 
Filter(
 [Lobby].[Added Local Time].[Added Local Time].ALLMEMBERS,
 [Lobby].[Added Local Time].CurrentMember.MemberValue >=  CDate("2020-01-02") 
 and [Lobby].[Added Local Time].CurrentMember.MemberValue <= CDate("2020-02-19")
) 
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS 
FROM [LTS KROI DEMO]
CELL PROPERTIES VALUE