none
Same Dimensation Apply Filter And Operation

    Question

  • Hi,

    Could please look into below query for AND Operation.

    Here I applied (2008 or 2007) and 2007,then result should be 2007.but i am getting 2008,2007.

    this issue is only for single dimensation on same dimensation filter only, for multiple dimensations it is working fine.

    Could you please give me some sugestion.

    MdxQuery:

    SELECT

    { [Measures].[Internet Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,

    Filter (

      {[Date].[Calendar Year].[Calendar Year].&[2008],[Date].[Calendar Year].&[2007]},

       (( [Date].[Calendar Year].[Calendar Year].&[2008], [Measures].[Internet Sales Amount] ) > 0

       or ( [Date].[Calendar Year].[Calendar Year].&[2007], [Measures].[Tax Amount] ) > 0 )

       AND ( [Date].[Calendar Year].&[2007], [Measures].[Internet Sales Amount] ) > 0

       ) ON ROWS FROM [Adventure Works]--(2008 or 2007) and 2007


    indu

    Friday, January 10, 2014 1:02 PM

All replies

  • Hi,

    In all the items of your condition tree you replace the default [Date].[Calendar Year].currentmember (wich you don't need to put in the tuples) by a member of the filtered set. As a consequence, the condition result will be the same (TRUE) for all members of the set. you can verify this by adding an item in the set:

       {
          [Date].[Calendar Year].[Calendar Year].&[2008]
         ,[Date].[Calendar Year].[Calendar Year].&[2007]
         ,[Date].[Calendar Year].[Calendar Year].&[2006]
        }

    2006 will also appear in a row.

    Philip,


    • Edited by VHteghem_Ph Friday, January 10, 2014 2:36 PM
    Friday, January 10, 2014 2:34 PM
  • Thank you  your reply.

    here my requirement is, i need to pass filter conditon  (2008 or 2007)and 2007 on date dimensation.

    i excepted result is 2007,but i am geeting 2007,2008.

    could please help me.if need my mdx query,please check above query.

    anybody please help me urgent.

    do we get excepted result using AND Operation?

    excepted result is 2007.


    indu

    Saturday, January 11, 2014 1:41 PM
  • Hi,

    Here is an working example of your condition tree  "(2008 or 2007) and 2007" in conbination wtih Filter() by wich the you can exlude the year 2007:

    SELECT 
      {
        [Measures].[Internet Sales Amount]
       ,[Measures].[Tax Amount]
      } ON COLUMNS
     ,Filter
      (
        {
          [Date].[Calendar Year].[Calendar Year].&[2008]
         ,[Date].[Calendar Year].&[2007]
        }
       ,
          
          ([Measures].[Internet Sales Amount] > 0 OR [Measures].[Tax Amount] > 0)
        AND 
          Measures.[Internet Sales Amount] < 9790000
      ) ON ROWS
    FROM [Adventure Works];
    --(2008 or 2007) and 2007

    if you comment out 

    AND Measures.[Internet Sales Amount] < 9790000

    you no longer exclude 2007

    i had to change the last AND condition because the original one appeared to be TRUE for 2007.

    Philip,



    • Edited by VHteghem_Ph Saturday, January 11, 2014 2:53 PM
    Saturday, January 11, 2014 2:39 PM
  • hi,

    my requirement is single dimensation filter condition with OR, AND Condition.

    Is it possible or not?

    just i gave above example with (2008 or 2007) and 2007.

    could you please tell me what result we can except?

    MdxQuery:

    SELECT

    { [Measures].[Internet Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,

    Filter (

      {[Date].[Calendar Year].[Calendar Year].&[2008],[Date].[Calendar Year].&[2007]},

       (( [Date].[Calendar Year].[Calendar Year].&[2008], [Measures].[Internet Sales Amount] ) > 0

       or ( [Date].[Calendar Year].[Calendar Year].&[2007], [Measures].[Tax Amount] ) > 0 )

       AND ( [Date].[Calendar Year].&[2007], [Measures].[Internet Sales Amount] ) > 0

       ) ON ROWS FROM [Adventure Works]--(2008 or 2007) and 2007


    indu

    Monday, January 13, 2014 6:37 AM