SQL Server 2005 | SQL Server 2008
There had been quite some posts about rounding values in MDX or on a report. For most cases we want to do the rounding in the presentation layer of the application (like with other formatting issues too) using formatting or maybe built-in round functions.
One issue with rounding is that the sum over the rounded details does not necessarily match the rounded total as in the following simple example:
| Region |
Exact value |
Rounded for display |
| A |
0.25 |
0 |
| B |
0.25 |
0 |
| C |
0.25 |
0 |
| D |
0.25 |
0 |
| Total |
1.00 |
1 |
As you can see from the last column, the sum of the rounded details is zero (as every rounded detail is zero), but the rounded total is 1 (as the total is exactly 1).
Basically you have three options for this rounding problem:
1. Round details and totals individually (like in the last column of the table above)
For our example this means that we have 0+0+0+0 giving a total of 1.
2. Compute the total as the sum over the rounded details (round first, then do the sum)
For our example this would mean 0+0+0+0 giving a total of 0
3. Adjust the detail rows so that they match the total when the total is computed by rounding the exact total
For our example this would mean something like 0+0+0+1 giving a total of 1
Option 1 is my preferred solution for most cases. Rounding involves some kind of error but option 1 doesn’t have other side effects like with options 2 and 3. Anyway, especially in financial reporting, option 1 would not be accepted as the detail rows doesn’t sum up to the total. This leaves some kind of uncertainty. Is there an error in the computation? Are we missing some values? Usually you can handle this be placing a remark on the report like "Totals may not match details because of rounding".
Option 2 sounds very nice at first but has some serious drawbacks. Usually the total is more meaningful than the details, so you might want to compute the total e.g. not by region but also by product group. Imagine the details for the product group are 0.5+0.5. Both would round to 1 so the total would be 2. Now for someone comparing the total by region (0 in this case) and the total by product group (2 in this case) the result is very confusing. Furthermore the error can get really serious when there are many records. So whatever your requirement is, I don’t recommend this option.
Option 3 is more complicated than option 1 and 2. It needs to adjust one or more elements which may also be confusing. In the example above we had 0+0+0+1=1, so the total really matches the details but it seems like only the four detail row is more significant. This could lead to wrong decisions. Another disadvantage of this option is that the detail usually is a total of some kind too (an aggregated value). So you might compare the value for region D with some other report showing the same value. This could also cause confusion when those two values doesn’t match.
So option 1 and option 3 are still under consideration. While option 1 can be easily implemented in the commonly used frontends, option 3 is more difficult. The remaining part of this post is about option 3.
As option 3 modifies the values in some way, I don’t want to implement this in the cube but solve the problem in the presentation layer of the application. There would be one reason however why you may think about implementing this kind of rounding in mdx: The presentation layer is not capable of doing so.
So maybe this post is a little bit off topic, but in the following I’m showing how to do this with Reporting Services (SSRS) instead of doing it by mdx.
As an example, I’m using a report with a simple mdx query based on the Adventure Works cube:
select {[Measures].[Amount] } on 0,
[Date].[Month of Year].[Month of Year] on 1
from [Adventure Works]
where ([Account].[Gross Sales],[Date].[Calendar Year].&[2003])
In order to get some rounding issues let’s say we want the Gross Sales as millions. I added a computation to the report’s dataset like this:
Now, before getting into detail for the computation, let’s look at the result (rounded values are rounded to one digit):
The third columns is what we saw as option 2 earlier in this post. We rounded the values and added the rounded values up to the total. This results in 25.3 and as you can see this doesn’t match the rounded total as 25.574665 would round to 25.6, not to 25.3.
The last column now shows the modified detail rows. As you can see, some of the detail values are slightly different resulting in the correct rounded total.
But how could this be done? In order to make things a little bit more easy to understand I added three more columns to the reporting showing the approach I took:
The "Rounding error" column simply computes the error we made for each line. Therefor the expression looks like this:
=Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1)
The "Running rounded error" column computes the rounded running total over the previous column using this expression:
=Round(
RunningValue(Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1),Sum,Nothing)
,1)
Actually this is the cumulated error that we have to add to the original rounding. But we are just interested in the steps of the this cumulated error. For example, the first values are 0, 0.1, 0.1. For the second line our cumulated error goes from 0 to 0.1, so we will want to add 0.1 to the rounded value. The difference of the second and the third line (both 0.1) is 0, so we don’t need to make any corrections.
Therefor we also take the cumulated rounding error of the previous line (simply but subtracting the current line’s value) using this expression:
=Round(
RunningValue(Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1),Sum,Nothing)
-(Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1))
,1)
Finally we can compute the difference in the last column by combining the last two expressions into one:
=Round(
RunningValue(Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1),Sum,Nothing)
,1)
-Round(
RunningValue(Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1),Sum,Nothing)
-(Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1))
,1)
This difference gives the correction for our rounded value column, so finally here is the "Corrected detail rows" column:
=Round(Fields!AmountMio.Value,1)+
Round(
RunningValue(Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1),Sum,Nothing)
,1)
-Round(
RunningValue(Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1),Sum,Nothing)
-(Fields!AmountMio.Value-Round(Fields!AmountMio.Value,1))
,1)
Again, it’s rather complicated to handle this kind of rounding and the way I showed here might not be perfect. Maybe you want to adjust the detail rows with the highest error instead of cumulating the error from line to line until the error itself round up and can be used to correct the next detail row.
But it might still be helpful when your requirement is to present comprehensible and traceable totals on your report and you’re tempted to write complicated mdx code as the report’s data source.
