locked
Date Range Filter in MDX RRS feed

  • Question

  • Hi,

    I have a dimension, lets call it DIM_A, DIM_A has the following attributes and sample data like below

    Emp_ID Emp_Key Reporting_Level1  Reporting_Level2  Effective_Date Expiry_Date
    100 100 Adam   David 01-01-2016 31-05-2016
    100 200 Adam  Lopez 01-06-2016 05-06-2016
    100 999 George  John 01-01-2015 01-01-9999
    100 300 Logan    Paul 01-12-2015 31-12-2015

    Now I am going to connect my SSRS reports to the cube and need to pass 2 date parameters (Start date and End date) and Emp_ID, totally 3 parameters from the report and have to retrieve the rows from DIM_A whose date range falls under the passing date parameters for that Emp_ID.

    For example

    If I pass Emp_ID: 100, Start date: 01-06-2015 and End date: 04-03-2016, then based on the sample data given above the below records has to be returned 

    100 100 Adam   David 01-01-2016 31-05-2016
    100 999 George  John 01-01-2015 01-01-9999
    100 300 Logan    Paul 01-12-2015 31-12-2015

    So if you notice one row has been filtered out based on the input date parameters.

    Now how do I achieve the above through MDX query? Please guide

    Thanks

    Monday, July 17, 2017 6:50 PM

Answers

  • Hi Madhan,

    Thanks for your response.

    You can just apply your logic to the sample MDX provided by me. If you want to apply the logic like below:

    Effective_Date <= @Start date And Expiry_Date >= @Start date 
    
    OR
    
    Effective_Date >= @Start date And Effective_Date <= @End date

    Then you may try below sample MDX query:

    select {} on 0,
    {filter([Product].[Category].[Category],
    [Product].[Category].CurrentMember IS [Product].[Category].&[01])*
    [Product].[Subcategory].[Subcategory]*
    [Product].[Product].[Product]*
    [Product].[Model Name].[Model Name]*
    filter([Product].[Start Date].[Start Date],
    [Product].[Start Date].CurrentMember.member_value >= Cdate("01-07-2013"))*
    filter([Product].[End Date].[End Date],
    [Product].[End Date].CurrentMember IS [Product].[End Date].&[1899-12-30T00:00:00])} 
    +
    {filter([Product].[Category].[Category],
    [Product].[Category].CurrentMember IS [Product].[Category].&[01])*
    [Product].[Subcategory].[Subcategory]*
    [Product].[Product].[Product]*
    [Product].[Model Name].[Model Name]*
    filter([Product].[Start Date].[Start Date],
    [Product].[Start Date].CurrentMember.member_value <= Cdate("01-07-2013"))*
    filter([Product].[End Date].[End Date],
    [Product].[End Date].CurrentMember.member_value >= Cdate("27-12-2008")) }
    on 1
    from [Adventure Works]


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Wednesday, July 19, 2017 1:50 AM
  • Hi Madhan,

    Thanks for your response.

    You can use MDX except function to exclude UNKNOWN MEMBER, something like below:
    EXCEPT([DIM_A].[DIM_A HIERARCHY].members,[DIM_A].[DIM_A HIERARCHY].[All].UNKNOWNMEMBER),

    SELECT {} ON 0,
    {EXCEPT([DIM_A].[DIM_A HIERARCHY].members,[DIM_A].[DIM_A HIERARCHY].[All].UNKNOWNMEMBER)*
    FILTER([DIM_A].[EFFECTIVE DATE].[EFFECTIVE DATE],
    [DIM_A].[EFFECTIVE DATE].CurrentMember.member_value <= Cdate("2018-01-01"))*
    FILTER([DIM_A].[EXPIRY DATE].[EXPIRY DATE],
    [DIM_A].[EXPIRY DATE].CurrentMember.member_value >= Cdate("2019-06-02"))
    }
    +
    {EXCEPT([DIM_A].[DIM_A HIERARCHY].members,[DIM_A].[DIM_A HIERARCHY].[All].UNKNOWNMEMBER)*
    FILTER([DIM_A].[EFFECTIVE DATE].[EFFECTIVE DATE],
    [DIM_A].[EFFECTIVE DATE].CurrentMember.member_value >= Cdate("2018-01-01"))*
    FILTER([DIM_A].[EXPIRY DATE].[EXPIRY DATE],
    [DIM_A].[EXPIRY DATE].CurrentMember.member_value <= Cdate("2019-06-02"))
    }
    ON 1
    FROM MyCube
    WHERE [DIM_A].[Emp_Name].&Logan

    For the reason why UNKNOWN MEMBER exist in here, you may want to refer to below blogs:
    https://mgarner.wordpress.com/2009/10/22/unknown-member-in-analysis-services/
    http://ms-olap.blogspot.sg/2014/08/the-0-has-nullprocessing-set-to-but.html


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Aka_Maddy Thursday, July 20, 2017 12:56 PM
    Thursday, July 20, 2017 7:04 AM

All replies

  • Hi Aka_Maddy,

    Thanks for your question.

    Can you explain your logic for your desired results ? 

    As i test in cube AdventureWorks, you can try MDX query as below:

    select {} on 0,
    [Product].[Category].[Category]*
    [Product].[Subcategory].[Subcategory]*
    [Product].[Product].[Product]*
    [Product].[Model Name].[Model Name]*
    [Product].[Start Date].[Start Date]*
    [Product].[End Date].[End Date]
    on 1
    from [Adventure Works];

    To filter category Bikes and start date >= 01-07-2013, see below MDX query:

    select {} on 0,
    filter([Product].[Category].[Category],
    [Product].[Category].CurrentMember IS [Product].[Category].&[01])*
    [Product].[Subcategory].[Subcategory]*
    [Product].[Product].[Product]*
    [Product].[Model Name].[Model Name]*
    filter([Product].[Start Date].[Start Date],
    [Product].[Start Date].CurrentMember.member_value >= Cdate("01-07-2013"))*
    [Product].[End Date].[End Date]
    on 1
    from [Adventure Works]

    I do not know your logic for the desired results, but you can try something like below to get the desired results.

    Filter([DIM_A].[Emp_ID].[Emp_ID],
           [DIM_A].[Emp_ID].currentmember.member_key = @Emp_ID)

    Filter([DIM_A].[Effective_Date].[Effective_Date],
            [DIM_A].[Effective_Date].currentmember.member_key >= format(CDate(@Start_Date), "dd-MM-yyyy")

    Filter([DIM_A].[Expiry_Date].[Expiry_Date],
            [DIM_A].[Expiry_Date].currentmember.member_key <= format(CDate(@End_Date), "dd-MM-yyyy")


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Tuesday, July 18, 2017 6:47 AM
  • Hi Wilson yuan,

          Thanks for your response, I really appreciate it.

          I am elaborating my question and the logic I am trying to achieve. Lets take the same dataset (below) as example.

    Emp_ID Emp_Key Reporting_Level1  Reporting_Level2  Effective_Date Expiry_Date
    100 100 Adam   David 01-01-2016 31-05-2016
    100 200 Adam  Lopez 01-06-2016 05-06-2016
    100 999 George  John 01-01-2015 01-01-9999
    100 300 Logan    Paul 01-12-2015 31-12-2015

     When I pass @Emp_ID@Start Date and @End Date from SSRS, the intention is to retrieve all the records that are/were active during that period for that particular employee. 

    For example:

    If I pass @Emp_ID: 100, @Start date: 01-06-2015 and @End date: 04-03-2016 from the reports, the MDX query must pull all the records that are active in between these 2 date parameters.

    so except for the 2nd row record (ref. dataset above), all the other rows has to be retrieved including the 3rd row whose date ranges from 01-01-2015 to 01-01-9999 and apparently the input parameter dates fall between these 2 dates. Now If we go with your below filter logic, then since we are using the  >= operator to compare @Start date and Effective_Date, we will lose the 3rd row records.

    Filter([DIM_A].[Emp_ID].[Emp_ID],
           [DIM_A].[Emp_ID].currentmember.member_key = @Emp_ID)
    
    Filter([DIM_A].[Effective_Date].[Effective_Date],
            [DIM_A].[Effective_Date].currentmember.member_key >= format(CDate(@Start_Date), "dd-MM-yyyy")
    
    Filter([DIM_A].[Expiry_Date].[Expiry_Date],
            [DIM_A].[Expiry_Date].currentmember.member_key <= format(CDate(@End_Date), "dd-MM-yyyy")

    So the logic should compare all the below conditions, we have to use OR condition

    Effective_Date <= @Start date And Expiry_Date >= @Start date

    OR

    Effective_Date >= @Start date And Effective_Date <= @End date

    hopefully the above conditions will cover all the scenarios

    Thanks in advance,

    Madhan

    Tuesday, July 18, 2017 1:46 PM
  • Hi Madhan,

    Thanks for your response.

    You can just apply your logic to the sample MDX provided by me. If you want to apply the logic like below:

    Effective_Date <= @Start date And Expiry_Date >= @Start date 
    
    OR
    
    Effective_Date >= @Start date And Effective_Date <= @End date

    Then you may try below sample MDX query:

    select {} on 0,
    {filter([Product].[Category].[Category],
    [Product].[Category].CurrentMember IS [Product].[Category].&[01])*
    [Product].[Subcategory].[Subcategory]*
    [Product].[Product].[Product]*
    [Product].[Model Name].[Model Name]*
    filter([Product].[Start Date].[Start Date],
    [Product].[Start Date].CurrentMember.member_value >= Cdate("01-07-2013"))*
    filter([Product].[End Date].[End Date],
    [Product].[End Date].CurrentMember IS [Product].[End Date].&[1899-12-30T00:00:00])} 
    +
    {filter([Product].[Category].[Category],
    [Product].[Category].CurrentMember IS [Product].[Category].&[01])*
    [Product].[Subcategory].[Subcategory]*
    [Product].[Product].[Product]*
    [Product].[Model Name].[Model Name]*
    filter([Product].[Start Date].[Start Date],
    [Product].[Start Date].CurrentMember.member_value <= Cdate("01-07-2013"))*
    filter([Product].[End Date].[End Date],
    [Product].[End Date].CurrentMember.member_value >= Cdate("27-12-2008")) }
    on 1
    from [Adventure Works]


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Wednesday, July 19, 2017 1:50 AM
  • Hi Wilson Yuan,

          Thanks for your detailed explanation, I have marked your response as answer and I have also got what I want almost but I just one question. There is a dataset like below

    Reporting_Level1 Reporting_Level2 Reporting_Level3 Emp_Key Emp_Name EFFECTIVE_DATE EXPIRY_DATE
    RL1_100 RL2_100 RL3_100 100 Logan 3/14/2017 12/31/9999
    RL1_256 RL2_256 RL3_256 200 Logan 8/8/2016 12/31/9999
    UNKNOWN UNKNOWN UNKNOWN 300 Logan 3/22/2017 12/31/9999

    and I built the below query which should ideally return all the above 3 records based on the Input dates but its ignoring the 3rd row any idea why?

    SELECT {} ON 0,
    {[DIM_A].[DIM_A HIERARCHY].members*
    FILTER([DIM_A].[EFFECTIVE DATE].[EFFECTIVE DATE],
    [DIM_A].[EFFECTIVE DATE].CurrentMember.member_value <= Cdate("2018-01-01"))*
    FILTER([DIM_A].[EXPIRY DATE].[EXPIRY DATE],
    [DIM_A].[EXPIRY DATE].CurrentMember.member_value >= Cdate("2019-06-02"))
    } 
    +
    {[DIM_A].[DIM_A HIERARCHY].members*
    FILTER([DIM_A].[EFFECTIVE DATE].[EFFECTIVE DATE],
    [DIM_A].[EFFECTIVE DATE].CurrentMember.member_value >= Cdate("2018-01-01"))*
    FILTER([DIM_A].[EXPIRY DATE].[EXPIRY DATE],
    [DIM_A].[EXPIRY DATE].CurrentMember.member_value <= Cdate("2019-06-02"))
    } 
    ON 1
    FROM MyCube
    WHERE [DIM_A].[Emp_Name].&Logan

    Thanks,

    Madhan

    Wednesday, July 19, 2017 3:10 PM
  • Hi Madhan,

    Thanks for your response.

    You can use MDX except function to exclude UNKNOWN MEMBER, something like below:
    EXCEPT([DIM_A].[DIM_A HIERARCHY].members,[DIM_A].[DIM_A HIERARCHY].[All].UNKNOWNMEMBER),

    SELECT {} ON 0,
    {EXCEPT([DIM_A].[DIM_A HIERARCHY].members,[DIM_A].[DIM_A HIERARCHY].[All].UNKNOWNMEMBER)*
    FILTER([DIM_A].[EFFECTIVE DATE].[EFFECTIVE DATE],
    [DIM_A].[EFFECTIVE DATE].CurrentMember.member_value <= Cdate("2018-01-01"))*
    FILTER([DIM_A].[EXPIRY DATE].[EXPIRY DATE],
    [DIM_A].[EXPIRY DATE].CurrentMember.member_value >= Cdate("2019-06-02"))
    }
    +
    {EXCEPT([DIM_A].[DIM_A HIERARCHY].members,[DIM_A].[DIM_A HIERARCHY].[All].UNKNOWNMEMBER)*
    FILTER([DIM_A].[EFFECTIVE DATE].[EFFECTIVE DATE],
    [DIM_A].[EFFECTIVE DATE].CurrentMember.member_value >= Cdate("2018-01-01"))*
    FILTER([DIM_A].[EXPIRY DATE].[EXPIRY DATE],
    [DIM_A].[EXPIRY DATE].CurrentMember.member_value <= Cdate("2019-06-02"))
    }
    ON 1
    FROM MyCube
    WHERE [DIM_A].[Emp_Name].&Logan

    For the reason why UNKNOWN MEMBER exist in here, you may want to refer to below blogs:
    https://mgarner.wordpress.com/2009/10/22/unknown-member-in-analysis-services/
    http://ms-olap.blogspot.sg/2014/08/the-0-has-nullprocessing-set-to-but.html


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Aka_Maddy Thursday, July 20, 2017 12:56 PM
    Thursday, July 20, 2017 7:04 AM