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:

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

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:

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:

• 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:

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

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.