none
How to use CASE function in MDX query.

    Question

  • Hi,

    I have simple MDX query:

     

    SELECT

    NON EMPTY { [Measures].[User Count] } ON COLUMNS,

    NON EMPTY { ([USERS].[Length].[Length].ALLMEMBERS ) } ON ROWS

    FROM [MyCube]

    I would like to use function like CASE to make grouping on specific condition. For example:

    CASE (dimension)

     WHEN [User]  =  5  THEN     "X"

     WHEN [User]  <  5  THEN     "Y"

     WHEN [User]  >  5  THEN     "Z"

     END  

    I tried many different combination, but without success. Could you tell me how to do this? Is it possible?

    Thank you

    Tomasz.

     

    PS. I found just one similar example but it was just for measure, it doesn’t work for me with dimension:

    WITH MEMBER [Measures].StringSales AS
    CASE
    WHEN [Measures].[Reseller Sales Amount] >= 40000 THEN "40,000+"
    WHEN [Measures].[Reseller Sales Amount] >= 30000 THEN "30,000-40,000"
    WHEN [Measures].[Reseller Sales Amount] >= 10000 THEN "10,000-30,000"
    WHEN [Measures].[Reseller Sales Amount] < 10000 THEN "< 10,000"
    END
    SELECT Calendar.[Calendar Year] on 0,
    [Geography].[Postal Code].Members on 1
    FROM [Adventure Works]
    WHERE [Measures].StringSales

     

     

    Friday, December 21, 2007 3:28 PM

All replies

  • Does this Adventure Works sample help?

     

    Code Block

    With
    Member [Measures].[PromoDiscountRange] as
    case
    when [Promotion].[Discount Percent].MemberValue < 0.10
    then "< 10%"
    when [Promotion].[Discount Percent].MemberValue < 0.25
    then "< 25%"
    else "25% +" end
    select
    {[Measures].[PromoDiscountRange],
    [Measures].[Sales Amount]} on 0,
    Non Empty [Promotion].[Discount Percent].[Discount Percent] on 1
    from [Adventure Works]
    ----------------------------------------------------------------
     PromoDiscountRange Sales Amount
    0 < 10% $102,335,515.04
    2 < 10% $4,896,451.91
    5 < 10% $1,037,643.33
    10 < 25% $131,597.36
    15 < 25% $468,929.09
    20 < 25% $612,324.54
    30 25% + $49,986.08
    35 25% + $250,927.70
    40 25% + $25,899.14
    50 25% + (null)

     

    Friday, December 21, 2007 10:23 PM
  • My appollogies for hijacking this a bit, just wondering if the IIF statment was more efficient than the CASE statment in MDX?  Never really tested it and just told it was, are there cases where it is better to use one over the other?

     

    Cheers

    Matt

    Friday, December 28, 2007 9:34 AM
  • Hello Matt,

     

    here you can find detailed information

     

    http://sqljunkies.com/WebLog/mosha/archive/2007/01/28/iif_performance.aspx

     

     

    Best regards

    Jörg

     

    Friday, December 28, 2007 10:24 AM
  • Cheers Jörg
    Friday, December 28, 2007 11:00 AM
  • Dear friend,

    I dont know about your project and requirements, but why you dont put those conditions in a dimension in spite of make all these calculations in MDX that affects the performance?

    Example:

     

    CategoryDimension

    IDField      DescriptionField

    1              Range between 10 to 1000

    2              Range between 1001 to 2000

     

    or

    CategoryDimension2

    IDField      DescriptionField

    1              X

    2              Y

    3              Z

     

    Maybe I'm wrong but the objective is to try to help you!

    Regards!!

    Friday, December 28, 2007 1:19 PM