New to MDX - FILTER Question

View previous topic View next topic Go down

New to MDX - FILTER Question

Post  ansonee on Fri Feb 17, 2012 5:31 pm

I've got an MDX query I need to modify, but not quite sure how to proceed. I essentially know most of tye syntax, but am at a loss as to where the components of the syntax should appear in the query. Here is the original query:

Code:
WITH
 
MEMBER [Employee].[FTE vs FL].[CF Total] AS AGGREGATE([Client Facing Disciplines])
 MEMBER [Time].[Year -  Half Year -  Quarter -  Month -  Date].[Trailing 12 Months] AS AGGREGATE(LASTPERIODS(60, STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
 MEMBER [Time].[Year -  Half Year -  Quarter -  Month -  Date].[YTD] AS AGGREGATE(YTD(STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
 
SELECT

{ [Measures].[Employee Hours %], [Measures].[Employee Hours], [Measures].[Available Hours minus PTO] } ON COLUMNS,
 
NON EMPTY

CROSSJOIN(
  {
    [Employee].[FTE vs FL].[CF Total],
    DrilldownMember(
      [Client Facing Disciplines],
      {
        [Employee].[FTE vs FL].[Discipline].&[DIGITAL AND INTERACTIVE],
        [Employee].[FTE vs FL].[Discipline].&[PUBLIC RELATIONS]
      })
  },
  {
    LASTPERIODS(60, STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)),
    [Time].[Year -  Half Year -  Quarter -  Month -  Date].[Trailing 12 Months],
    [Time].[Year -  Half Year -  Quarter -  Month -  Date].[YTD]
  })
 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [OLSON BI]
 
WHERE ([Employee Hours Time Category].[Time Category].&[Client Facing])

I'm sure a FILTER clause is what I need, but not sure where to slip that in.

What I need to do is tweak the query so that the DISCIPLINE value of Loyalty is excluded from the results, along with the DEPARTMENT value of OLSON PR.

This is how I'm sure the two lines that say what should be excluded, I'm just not sure where they should go:

[Employee].[FTE vs FL].[Discipline].&[LOYALTY],
[Employee].[FTE vs FL].[Discipline].[Department].&[OLSON PR Chicago]

MDX is definitely not my strong point. Any assistance anyone can render would be greatly appreciated!!

Thanks in advance!!

ansonee

Posts : 3
Join date : 2011-08-11

View user profile

Back to top Go down

Re: New to MDX - FILTER Question

Post  John Simon on Wed Mar 07, 2012 11:56 pm

You'd be better off going to the MSDN forum for SSAS to get some of the guys there to help you out.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum