Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

New to MDX - FILTER Question

2 posters

Go down

New to MDX - FILTER Question Empty New to MDX - FILTER Question

Post  ansonee 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

Back to top Go down

New to MDX - FILTER Question Empty Re: New to MDX - FILTER Question

Post  John Simon 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

http://jsimonbi.wordpress.com

Back to top Go down

Back to top

- Similar topics

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