locked
filter to last 3 months using STROMEMBER RRS feed

  • Question

  • Hi i am trying to filter the following MDX to last 3 months based on a dynamic filter  but i am unsure where to place the filter in the query below is the MDX followed by the filter for 3 months data;

    SELECT NON EMPTY { [Measures].[ Value], [Measures].[%] } ON COLUMNS, NON EMPTY { ([Date].[Date].[Date].ALLMEMBERS * [Product].[Brand].[Brand].ALLMEMBERS * [Product].[Product Name].[Product Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Product].[ProductId].&[162005], [Product].[ProductId].&[1562235076], [Product].[ProductId].&[16203227], [Product].[ProductId].&[162356232077] } ) ON COLUMNS FROM ( SELECT ( { [Product].[ParentProduct].&[1254678 - PARENT] } ) ON COLUMNS FROM [Sales])) WHERE ( [Product].[ParentProduct].&[1254678 - PARENT], [Product].[ProductId].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    3 months filter;

    SELECT ({STRTOMEMBER(""""[Date].[Date].&[""""+FORMAT(NOW(),""""yyyyMMdd"""")+""""]"""").Lag(91):STRTOMEMBER(""""[Date].[Date].&[""""+FORMAT(NOW(),""""yyyyMMdd"""")+""""]"""")})

    any help would be amazing

    Monday, February 24, 2020 11:58 AM

All replies

  • Hi,

    You can put it instead of ([Date].[Date].[Date].ALLMEMBERS).

    SELECT NON EMPTY { [Measures].[ Value], [Measures].[%] } ON COLUMNS, NON EMPTY { ({STRTOMEMBER(""""[Date].[Date].&[""""+FORMAT(NOW(),""""yyyyMMdd"""")+""""]"""").Lag(91):STRTOMEMBER(""""[Date].[Date].&[""""+FORMAT(NOW(),""""yyyyMMdd"""")+""""]"""")} * [Product].[Brand].[Brand].ALLMEMBERS * [Product].[Product Name].[Product Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Product].[ProductId].&[162005], [Product].[ProductId].&[1562235076], [Product].[ProductId].&[16203227], [Product].[ProductId].&[162356232077] } ) ON COLUMNS FROM ( SELECT ( { [Product].[ParentProduct].&[1254678 - PARENT] } ) ON COLUMNS FROM [Sales])) WHERE ( [Product].[ParentProduct].&[1254678 - PARENT], [Product].[ProductId].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Monday, February 24, 2020 1:44 PM
  • thank you for this, although i get the following error when i do this Details: "AnalysisServices: Query (1, 502) Parser: The syntax for '[Date]' is incorrect

    Monday, March 9, 2020 2:28 PM
  • Try like this :

    SELECT NON EMPTY { [Measures].[ Value], [Measures].[%] } ON COLUMNS, 
    NON EMPTY { ({STRTOMEMBER("[Date].[Date].&["+FORMAT(NOW(),"yyyyMMdd")+"]").Lag(91):STRTOMEMBER("[Date].[Date].&["+FORMAT(NOW(),"yyyyMMdd")+"]")} * [Product].[Brand].[Brand].ALLMEMBERS * [Product].[Product Name].[Product Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Product].[ProductId].&[162005], [Product].[ProductId].&[1562235076], [Product].[ProductId].&[16203227], [Product].[ProductId].&[162356232077] } ) ON COLUMNS FROM ( SELECT ( { [Product].[ParentProduct].&[1254678 - PARENT] } ) ON COLUMNS FROM [Sales])) WHERE ( [Product].[ParentProduct].&[1254678 - PARENT], [Product].[ProductId].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Monday, March 9, 2020 2:54 PM