I have an existing MDX query that I inherited that I need to modify. Here is the query:
WITH MEMBER [Time].[Year - Month].[YTD] AS AGGREGATE(YTD(StrToMember(@SelectedMonth, CONSTRAINED))) --< We need to OT over the individual months in YTD >-- MEMBER [Measures].[Overtime Hours] AS IIF([Time].[Year - Month].CurrentMember = StrToMember(@SelectedMonth, CONSTRAINED), [Measures].[Employee Overtime Hours], SUM(YTD(StrToMember(@SelectedMonth, CONSTRAINED)), [Measures].[CALCED OT])) MEMBER [Measures].[Overtime Hours %] AS '[Measures].[Overtime Hours] / [Measures].[Available Hours minus PTO]' SELECT { [Measures].[Employee Hours], [Measures].[Employee Hours %], [Measures].[Overtime Hours], [Measures].[Overtime Hours %], [MEasures].[Available Hours] } ON COLUMNS, NON EMPTY CROSSJOIN( FILTER( IIF(@SelectedDiscipline = @SelectedDepartment, DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER), DESCENDANTS(StrToMember(@SelectedDepartment, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER) ), NOT INSTR([Employee].[Department].CurrentMember.MEMBER_NAME, "Freelance")), [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS, [Employee].[Employee Level].[Employee Level].ALLMEMBERS, { -- PTO Utilization Category excluded -- PRODUCTION Utiltization Category included with detail CROSSJOIN( {[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[PRODUCTION]}, [Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS), -- ADMIN Utiltization Category rolled-up witout detail CROSSJOIN( {[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[ADMIN]}, {[Employee Hours Time Category].[Time Category].[Admin]}) }, {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]} ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [OLSON BI] CELL PROPERTIES VALUE ,BACK_COLOR ,FORE_COLOR ,FORMATTED_VALUE ,FORMAT_STRING ,FONT_NAME ,FONT_SIZE ,FONT_FLAGS;
What I need to do is add another FILTER statement to the query. I need to filter out any employees who have a Manager Name of "Olson O-Tern". Here is how I assumed the FILTER would look:
NOT INSTR([Employee].[Manager Name].CurrentMember.MEMBER_NAME, "O-Tern"
But when I try to add this line to the existing FILTER clause I get an error stating the FILTER statement only expects two arguements.
How would I alter this query to include this additional FILTER statement without affecting the rest of the query?
Thanks in advance!!