Round function in MDX

SQL Server 2005-2012

In most cases you will want to perform a round-operation of your MDX query results by applying a format string. Alternatively, you may want to use client functionality (for example formatting capabilities in Excel) to show the results in the required format.

However, there are some cases, in which you may want the cube to return a rounded result directly. This could be the case for a financial cube where you want to have exactly the same type of rounding as in the ERP software behind.

First let’s look at the formatting of a measure. The following simple query shows how to format a given value to a fixed number of decimal places:

with member testvalue as 0.5, format_string=’#,##0′
select [Measures].[testvalue] on 0 from [Adventure Works]

image

The method used for rounding here is often referred to as ‘round half up’ because half way values are always rounded up. For example, if you round 0.35 to one digit after the decimal point you would get 0.4.

Since we used format_string the formatting is applied to the value as an additional property. For example

with
member testvalue as 0.57372843213
member testvalueformatted as testvalue, format_string = ‘#,##0.00′
member derivedvalue as testvalueformatted, format_string=’#,##0.000000′

select {
    [Measures].[testvalue]
    , [Measures].[testvalueformatted]
    , [Measures].[derivedvalue]
    }
on 0 from [Adventure Works]

image

In this example we created a formatted value with a precision of two digits. Then we created a new measure based on the formatted one but this time with 6 digits. What you can see, is that no information is lost because of the formatting, it’s just for display.

I wrote about more complex formatting capabilties here, here and here. And there is another post explaining how to make sure that the rounded sum corresponds to the rounded detail rows.

Now, let’s assume you want to perform the rounding directly in MDX. One option to do so is the VBA round function. The function takes two parameters:

  1. The value that you want to round
  2. The number of digits after the decimal point

For example, to round a given value to 2 digits after the decimal point you could write something like this:

with member testvalue as VBA!round(0.57372843213,2)
select { [Measures].[testvalue] }
on 0 from [Adventure Works]

image

However, the reason I’m writing this blog post is a warning:

Different rounding-methods exist and are in practical use and therefore two implementations of a rounding function do not necessarily get to the same result.

In fact, the method used be VBA!round(…) is actually ‘round half to even’, not ‘round half up’ (as with our format_string). The difference can be clearly seen if you perform a rounding operation to the integer value. In this case 0.5 is rounded to the nearest even integer value which is 0 (not 1). This method is more statistically balanced because it doesn’t prefer half way values and is therefore widely used in bookkeeping. You can see the same effect in Excel. The worksheet round function uses ‘round half up’ while the build-in VBA function uses ‘round half to even’. Here is the corresponding effect in MDX:

with
member val1 as VBA!round(0.5,0)
member val2 as VBA!round(1.5,0)

select { [Measures].[val1], [Measures].[val2] }
on 0 from [Adventure Works]

image

  • 0.5 rounds to 0
  • 1.5 rounds to 2

You can find a lot more about different rounding methods on Wikipedia. So, if the requirement is to show rounded values (in the cube, on the report or wherever) you should always be sure to understand the rounding method.

To get back to the original rounding requirement in MDX you could perform a rounding operation (round half up) for the value x by using this formula: Int( x + sgn(x)*0.5)

This is shown in the following MDX query:

with
member x as 0.5
member x_rounded as VBA!Int(x +VBA!Sgn(x)*0.5)

select { x, x_rounded }
on 0 from [Adventure Works]

image

And of course, if you need to round to a giving precision you could multiply/divide the value appropriately, for example like this

with
member x as 0.2353
member x_rounded as VBA!Int(100*x +VBA!Sgn(x)*0.5)/100

select { x, x_rounded }
on 0 from [Adventure Works]

image

If you have a lot of such operations I would recommend to put the rounding-functions in an Assembly. And again, best choice is to round by format, not by calculation, so the procedure above is only recommended if you really need to return a properly (or specifically) rounded result from the cube.

About these ads
This entry was posted in Allgemein. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s