none
Problem with using Round() in mdx

    Question

  • I am using Round function in MDX. It is not giving expected result. It always gives nearest even number as result.

    I am running following mdx:

     

    with member

    [Measures].[Calculated Score25]

    as 'Round(2.5, 0)'

    member [Measures].[Calculated Score35]

    as 'Round(3.5, 0)'

    select {

    [Measures].[Calculated Score25],

    [Measures].[Calculated Score35]}

    on columns from Cube1

     

    This mdx gives following result

    Calculated Score25 Calculated Score35
       2                                 4

     

    When expected result is    3, 4

    Tuesday, May 15, 2007 11:51 AM

All replies

  • I agree it's strange, and when I first looked at your results I thought this was a bug. But then I looked at some descriptions of the round function and I suspect this behaviour is intentional:

    http://www.techonthenet.com/access/functions/numeric/round.php

     

    Anyway, you can get the value you want using the Excel round function, assuming you have the Excel function library available on your server:

     

    with member

    [Measures].[Calculated Score25]

    as 'excel!round(2.5,0)'

    member [Measures].[Calculated Score35]

    as 'excel!Round(3.5,0)'

    select {

    [Measures].[Calculated Score25],

    [Measures].[Calculated Score35]}

    on columns from [Adventure Works]

     

    HTH,

     

    Chris

    Tuesday, May 15, 2007 12:31 PM
    Moderator
  • This MS Support article explains various rounding algorithms - for example, VBA Round() performs "Banker's Rounding", whereas Excel Round() does "symmetric arithmetic rounding":

     

    How To Implement Custom Rounding Procedures

    ...

    SUMMARY

    There are a number of different rounding algorithms available in Microsoft products. Rounding algorithms range from Arithmetic Rounding in Excel's Worksheet Round() function to Banker's Rounding in the CInt(), CLng(), and Round() functions in Visual Basic for Applications. This article describes what the various Visual Basic for Applications rounding functions do and provides samples of using the functions. In addition, the article includes sample functions that implement various rounding algorithms.
     
    But if you need asymmetric arithmetic rounding, it looks like inverted VBA Int() would work:
     

    with

    member [Measures].[Score25] as 2.5

    member [Measures].[Score35] as 3.5

    member [Measures].[Calculated Score25]

    as '-int(-[Measures].[Score25])'

    member [Measures].[Calculated Score35]

    as '-int(-[Measures].[Score35])'

    select {

    [Measures].[Calculated Score25],

    [Measures].[Calculated Score35]}

    on columns from [Adventure Works]

    ----------------------------------------------------------------
    Calculated Score25 Calculated Score35
    3 4
    Tuesday, May 15, 2007 6:27 PM
    Moderator