Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: FILTER For MDX Query

    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])
    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. I believe a FILTER statement is what I need, but not sure how to format it.

    [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!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    how cute...
    Introduction

    Analysis Services offers a powerful server-based calculation engine. It's a spreadsheet on steroids that can do virtually anything. This document describes how Multidimensional Expressions (MDX) for Microsoft SQL Server 2005 can be applied to common business problems.
    Common Business Problems...yeah, right...that would be ignorance

    Sorry...I can't help you
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Spreadsheet on Steroids...yeah that's a technical term

    No wonder why the business section wonders about M$ Future
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The criteria go in the WHERE clause of an MDX expression, just like they do in a conventional SQL expression.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Actually found the answer...the EXCEPT clause doesn't go in the WHERE clause - actually comes before the where clause.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •