none
Filter in MDX Statement

    Question

  • I have a very basic MDX statement which contains a FILTER statement:

    WITH
    
    SET [Selected Discipline] AS STRTOSET(@Disciplines, CONSTRAINED)
    SET [Selected Department] AS STRTOSET(@Departments, CONSTRAINED)
    
    SET [Selected Departments] AS IIF(@Disciplines = @Departments, 
      FILTER(
        DESCENDANTS([Selected Discipline], [Employee].[DISC - DEPT - EMP].[Department], AFTER), 
        NOT INSTR([Employee].[DISC - DEPT - EMP].CurrentMember.UNIQUE_NAME, "Freelance")),
      DESCENDANTS([Selected Department], [Employee].[DISC - DEPT - EMP].[Department], AFTER))
    
    SET [TTM] AS { LastPeriods(12, StrToMember(@SelectedMonth, CONSTRAINED)) }
    
    SELECT 
    
    { 
      [Measures].[Employee Hours], 
      [Measures].[Employee Hours %] 
    } ON COLUMNS, 
    
    NON EMPTY CROSSJOIN(
    [Selected Departments],
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    [TTM]) ,
    
    FILTER ([Employee].[Manager Name].[Manager Name].Members , 
    
                 NOT InStr([Employee].[Manager Name].CurrentMember.MEMBER_NAME, "O-Tern"))
    
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
    
    FROM [OLSON BI]
    
    WHERE (
    
    [Employee Hours Time Category].[Time Category].&[Client Facing],
    [Employee Hours Time Category].[Utilization Category].&[PRODUCTION])
    
    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, 	FORMATTED_VALUE, FORMAT_STRING, FONT

    When running the query, I get the "Failed to parse the query to detect if it is mdx or dmx".

    THe qqy=uery is basic enough, but for the life of me can't figure out what is wrong????

    Any suggestions woud be greatly appreciated!!

    Thanks


    A. M. Robinson

    Thursday, July 31, 2014 8:51 PM

Answers

All replies

  • Hi Robinson,

    As per my understanding, the issue cannot be related to Filter function. I have tested it on my local environment, we can use the Filter like below.

    In your scenario, please try to remove the parameters and run it in SSMS and check if this issue is persists or not.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, August 01, 2014 9:57 AM
  • I literally have the exact same query running in a different report and it runs fine.

    Also, your FILTER is in a SET statement - mine is not. That is the major difference between my query and yours.

    SELECT 
    
    { 
      [Measures].[Employee Hours], 
      [Measures].[Employee Hours %] 
    } ON COLUMNS, 
    
    NON EMPTY CROSSJOIN(
    [Selected Departments],
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    [TTM]) ,
    
    FILTER ([Employee].[Manager Name].[Manager Name].Members , 
    
                 NOT InStr([Employee].[Manager Name].CurrentMember.MEMBER_NAME, "O-Tern"))


    If I remove the filter statement from this query and run it WITH the same parameters it runs fine - and has been running fine for literally over a year. The introduction of this FILTER statement is causing an issue.

    So either the filter is in the wrong place or it's not formed properly.

    I can provide a couple of examples of queries that are the ecact same with parameters and run fine, and when a FILTER is introduced they still run fine.


    A. M. Robinson



    • Edited by ansonee Sunday, August 03, 2014 2:26 PM new
    Sunday, August 03, 2014 2:20 PM
  • Is there a way to add a second FILTER statement to the SET statement that already has a FILTER statement?

    A. M. Robinson

    Monday, August 04, 2014 11:42 PM
  • Yes, you can nest FILTER calls...

    FILTER(FILTER(..),..) etc..

    This thread really belongs in the Analysis Services forums


    Thanks! Josh


    Monday, August 04, 2014 11:53 PM
  • Thanks for the replay, Josh. I originally had this in the SSAS forum, but wasn't getting any traction. I figured since this query is for an Analysis Services report in SSRS, I thought this may be a good place as well.

    I tried using a nested FILTER, but it still seems to throw an error.

    Given the original MDX query, how could I apply a nested FILTER to the [SelectedDepartments] set.

    Here's the original SET declaration:

    SET [Selected Departments] AS IIF(@Disciplines = @Departments, 
      FILTER(
        DESCENDANTS([Selected Discipline], [Employee].[DISC - DEPT - EMP].[Department], AFTER), 
        NOT INSTR([Employee].[DISC - DEPT - EMP].CurrentMember.UNIQUE_NAME, "Freelance")),
      DESCENDANTS([Selected Department], [Employee].[DISC - DEPT - EMP].[Department], AFTER))
    

    I'd like to add this additional FILTER to the SET:

    FILTER ([Employee].[Manager Name].[Manager Name].Members , 
                 NOT InStr([Employee].[Manager Name].CurrentMember.MEMBER_NAME, "O-Tern"))
    
    

    How would one nest this FILTER?


    A. M. Robinson

    Tuesday, August 05, 2014 7:31 PM
  • Have you tried EXISTS?

    EXISTS(
    	FILTER(
        DESCENDANTS([Selected Discipline], [Employee].[DISC - DEPT - EMP].[Department], AFTER), 
        NOT INSTR([Employee].[DISC - DEPT - EMP].CurrentMember.UNIQUE_NAME, "Freelance")),
      DESCENDANTS([Selected Department], [Employee].[DISC - DEPT - EMP].[Department], AFTER)),
      	  FILTER ([Employee].[Manager Name].[Manager Name].Members , 
                 NOT InStr([Employee].[Manager Name].CurrentMember.MEMBER_NAME, "O-Tern"))
    )
    http://msdn.microsoft.com/en-us/library/ms144936.aspx

    Thanks! Josh

    Tuesday, August 05, 2014 11:20 PM
  • Thanks, Josh! We're almost there...

    The query parsed/compiled fine, but still getting employees who are "O-Tern" for a manager name.

    Here's the entire query:

    WITH
    
    SET [Selected Discipline] AS STRTOSET(@Disciplines)
    SET [Selected Department] AS STRTOSET(@Departments)
    
    SET [Selected Departments] AS IIF(@Disciplines = @Departments, 
    
     EXISTS(
    	FILTER(
        DESCENDANTS([Selected Discipline], [Employee].[DISC - DEPT - EMP].[Department], AFTER), 
        NOT INSTR([Employee].[DISC - DEPT - EMP].CurrentMember.UNIQUE_NAME, "Freelance")),
      DESCENDANTS([Selected Department], [Employee].[DISC - DEPT - EMP].[Department], AFTER)),
      	  FILTER ([Employee].[Manager Name].[Manager Name].Members , 
                 NOT InStr([Employee].[Manager Name].CurrentMember.MEMBER_NAME, "O-Tern"))
    )
    
    SET [TTM] AS { LastPeriods(12, StrToMember(@SelectedMonth, CONSTRAINED)) }
    
    SELECT 
    
    { 
      [Measures].[Employee Hours], 
      [Measures].[Employee Hours %] 
    } ON COLUMNS, 
    
    NON EMPTY CROSSJOIN(
    [Selected Departments],
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    [TTM]) 
    
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
    
    FROM [OLSON BI]
    
    WHERE (
    
    [Employee Hours Time Category].[Time Category].&[Client Facing],
    [Employee Hours Time Category].[Utilization Category].&[PRODUCTION])
    
    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, 	FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Besides that one issue, I think this approach will work. Any idea on what the scoop might be?

    Is there a way to do a WHERE clause where you can exclude values? For example something along the lines of WHERE [Employee].[Manager Name] DOES NOT EQUAL "O-Tern"?

    Thanks again!


    A. M. Robinson


    • Edited by ansonee Wednesday, August 06, 2014 6:38 PM new info
    Wednesday, August 06, 2014 5:33 PM
  • Is there a way to do a WHERE clause where you can exclude values? For example something along the lines of WHERE [Employee].[Manager Name] DOES NOT EQUAL "O-Tern"?

    (http://social.msdn.microsoft.com/Forums/sqlserver/en-US/380ee5d2-3b97-41d9-9b4d-613df7903fa4/using-wildcard-with-parameters-in-mdx-?forum=sqlreportingservices)


    Thanks! Josh

    • Marked as answer by ansonee Thursday, August 14, 2014 2:08 AM
    Thursday, August 07, 2014 2:20 PM
  • Thanks, Josh!

    That did the trick!!

    Thanks again!


    A. M. Robinson

    Thursday, August 14, 2014 2:08 AM