none
MDX Case Expression equivalent to sql

    Question

  • Hi All,

    I trying to write below sql query into MDX .Anyone could you please help me writing this query

    Sql Query

    Case when productName<>"NONE" then  Productname elso end

    MDX

    with set x as

     Case when [Product].[Category].member<>"NONE" then [Product].[Category].members else 0 end

    Monday, August 26, 2013 8:13 PM

Answers

  • Hi Ralph1234,

    Please refer to the following MDX query:

    WITH MEMBER [Measures].[Attribute] AS
     IIF([Product].[Product].Currentmember.Properties("Color")<>"Blue"
     , [Product].[Product].Currentmember.NAME
     , 0)
    
    SELECT {[Measures].[Attribute]} ON COLUMNS,
    
    {[Product].[Product].Members} ON ROWS
    
     FROM [Adventure Works]
    

    Fore more information, please take a look at the following article:
    http://bimoss.wordpress.com/category/mdx/

    Best Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, August 28, 2013 7:23 AM

All replies

  • Hi

    All you need is to take of the None Product so try this

    [Product].[Product Categories].[Product].Members - [Product].[Product Categories].[Product].&[None]

    Consedering, you have "None" as the Key, make changes accroding to your Product Key


    Prav

    Monday, August 26, 2013 10:12 PM
  • Thanks!  for the reply...But i'm still facing an issue here is my query

     with set x as  [Product].[Category].[Category].members - [Product].[Category].&[1]
     member y as case when  [Product].[Category].currentmember.level is x.members then 1 else 0 end
     
     Select Y on 0 ,[Product].[Category].[Category].Members on 1
      from [Adventure Works]

    Thanks again for the reply 

    Monday, August 26, 2013 10:55 PM
  • Hi

    We can't appy CurrentMember.Level to Member of Set and I think, you are trying to assing 0 for [Product].[Category].&[1] and 1 for remaining Categories

    Try this

    with 
      member y as case when  [Product].[Category].currentmember is   [Product].[Category].&[1] then 0 else 1 end  
      Select Y on 0 ,
      [Product].[Category].[Category].Members on 1
       from [Adventure Works]


    Prav

    Monday, August 26, 2013 11:36 PM
  • Hi Ralph1234,

    Please refer to the following MDX query:

    WITH MEMBER [Measures].[Attribute] AS
     IIF([Product].[Product].Currentmember.Properties("Color")<>"Blue"
     , [Product].[Product].Currentmember.NAME
     , 0)
    
    SELECT {[Measures].[Attribute]} ON COLUMNS,
    
    {[Product].[Product].Members} ON ROWS
    
     FROM [Adventure Works]
    

    Fore more information, please take a look at the following article:
    http://bimoss.wordpress.com/category/mdx/

    Best Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, August 28, 2013 7:23 AM