Problem with using Round() in mdx

_{Tuesday, May 15, 2007 11:51 AM}

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 12:31 PM | 1 vote}

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 6:27 PM}

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

...

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