locked
How to perform OR operation in MDX? RRS feed

  • Question

  • What is the equivalent of T-SQL OR operation in MDX? See the query below. I only returns the records where both [Has EA] & [Has Open] set to true. How can I make it Or?

    SELECT
       [Measures].[Customers] ON 0
                ,[Dim Area].[Area Name].
    MEMBERS ON 1
    FROM     [MyCube]
    WHERE   (
                      [Dim Customer].[Has EA].[True]
                     ,[Dim Customer].[Has Open].[True]
                 );

    Thanks,
    Irfan

    • Moved by Mangal Pardeshi Tuesday, November 3, 2009 6:51 AM (From:Transact-SQL)
    Monday, November 2, 2009 11:28 PM

Answers

  • this one should work:

    SELECT   
    [Measures].[Customers] ON 0,
    [Dim Area].[Area Name].MEMBERS ON 1
    FROM [MyCube]
    WHERE (
    {([Dim Customer].[Has EA].[True], [Dim Customer].[Has Open].defaultmember),
     ([Dim Customer].[Has EA].defaultmember, [Dim Customer].[Has Open].[True])}
    )
    greets,
    gerhard
    - www.pmOne.com -
    • Proposed as answer by Raymond-Lee Wednesday, November 4, 2009 5:38 AM
    • Marked as answer by GregGallowayMVP Tuesday, December 22, 2009 6:14 PM
    Tuesday, November 3, 2009 10:54 PM
    Answerer

All replies

  • Hi Irfan,

        This link will help you http://msdn.microsoft.com/en-us/library/aa216737(SQL.80).aspx


    Lakshman
    Tuesday, November 3, 2009 5:02 AM
  • Hi Irfan,

        This link will help you http://msdn.microsoft.com/en-us/library/aa216737(SQL.80).aspx


    Lakshman

    Hi Lakshman,

    Thanks for sharing the link. I tried it in my query but it throws an error. Here is what the revised query look like

    Revised Query
    SELECT   [Measures].[Customers]              ON 0
                ,[Dim Area].[Area Name].
    MEMBERS ON
    1
    FROM
         [MyCube]
    WHERE
       (
                      [Dim Customer].[Has EA].[True] OR [Dim Customer].[Has Open].[True] = TRUE
                   );

    Error Message
    The WHERE clause function expects a tuple set expression for the argument. A string or numeric expression was used.

    Any ideas??

    Thanks,
    Irfan
    Tuesday, November 3, 2009 4:37 PM
  • Parenthases placement?

     ([Dim Customer].[Has EA].[True] OR [Dim Customer].[Has Open].[True]) = TRUE
    Tuesday, November 3, 2009 4:40 PM
  • this one should work:

    SELECT   
    [Measures].[Customers] ON 0,
    [Dim Area].[Area Name].MEMBERS ON 1
    FROM [MyCube]
    WHERE (
    {([Dim Customer].[Has EA].[True], [Dim Customer].[Has Open].defaultmember),
     ([Dim Customer].[Has EA].defaultmember, [Dim Customer].[Has Open].[True])}
    )
    greets,
    gerhard
    - www.pmOne.com -
    • Proposed as answer by Raymond-Lee Wednesday, November 4, 2009 5:38 AM
    • Marked as answer by GregGallowayMVP Tuesday, December 22, 2009 6:14 PM
    Tuesday, November 3, 2009 10:54 PM
    Answerer
  • this one should work:

    SELECT   
    
    [Measures].[Customers] ON 0,
    
    [Dim Area].[Area Name].MEMBERS ON 1
    
    FROM [MyCube]
    
    WHERE (
    
    {([Dim Customer].[Has EA].[True], [Dim Customer].[Has Open].defaultmember),
    
     ([Dim Customer].[Has EA].defaultmember, [Dim Customer].[Has Open].[True])}
    
    )
    greets,
    gerhard
    - www.pmOne.com -

    Thanks Gerhard!!! This will help but it will grow very fast if I have more than two filters to OR. Is there any other alternative way? How about Filter or something else?

    Irfan
    Sunday, November 15, 2009 3:57 AM
  • Hi Irfan,

    you may could use Filter()-function
    do a crossjoin of all necessary hieararchies and then to the check in the filter

    so for the example above:

    FILTER([Dim Customer].[Has EA].members*[Dim Customer].[Has Open].members,
      [Dim Customer].[Has EA].currentmember IS [Dim Customer].[Has EA] OR
      [Dim Customer].[Has Open].currentmember IS [Dim Customer].[Has Open].[True])

    havent tested it yet but should work i think

    greets,
    gerhard
    - www.pmOne.com -
    Monday, November 16, 2009 6:11 PM
    Answerer
  • If you want to use this sproc, it may make the code easier to read:
    http://asstoredprocedures.codeplex.com/wikipage?title=AsymmetricSet

    But double check that it doesn't impact performance to use the sproc.
    http://artisconsulting.com/Blog/GregGalloway
    Monday, November 16, 2009 8:25 PM