Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Custom Rounding and Truncation of Numbers in MDX


Custom Rounding and Truncation of Numbers in MDX

Author
Message
Boyan Penev
Boyan Penev
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 125
Comments posted to this topic are about the item Custom Rounding and Truncation of Numbers in MDX

Boyan Penev
Arto Ahlstedt
Arto Ahlstedt
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 803
This article http://en.wikipedia.org/wiki/Rounding explains various rounding techniques.

After reading the Wiki article, my guess is that MDX uses it because it is the only deterministic method which is essentially non-biased, also in presence of a predominant sign among samples.

Or simply because it is the .NET default.

Thank you for the article, it opened my eyes to looking closely at current implementation of rounding every time I encounter a new environment or revisit a supposedly familiar one after a while. Assume nothing.
Adam Aspin
Adam Aspin
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 1032
Nice article Boyan, - I found it extermely interesting.
Esalter
Esalter
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 18
I tend to use standard rounding via a user function ROUNDIT(RawVal,DecPl,RoundVal) which uses a similar approach to the example:-

FIX(RawVal * 10^DecPl + (SGN(RawVal) * RoundVal))/ 10 ^ DecPl

where
RawVal is the value to be rounded
DecPl is the number of decimal places required
RoundVal is the rounding factor

This ensures that negative values get rounded DOWN e.g. -1.5 gets rounded to -2.0Cool
(Useful where a value is contra'd out so that sum of 1.25 and -1.25 returns zero) - if this is not required then change the function to:-
FIX(RawVal * 10^DecPl + RoundVal)/ 10 ^ DecPl


If rounding OFF is required (surplus dec places removed) then RoundVal should be passed as 0.

Trainee Novicew00t
slupton1
slupton1
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
There's a reason why it's called "Banker's Rounding". Summating a column of numbers that are all rounded using arithmetic rounding may introduce a rounding error that produces a higher total than summating the same column of numbers without rounding then rounding the total. Bankers rounding mitigates this rounding error. It's not wrong, it's mearly different. As long as you're aware, you can use the proper method for your application.
Boyan Penev
Boyan Penev
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 125
Heh,

After SQL Server Central published the article, I found this really good reference on Custom Rounding:

http://support.microsoft.com/kb/196652

In the end of the page there are examples, and the SymArith function does exaclty what my rounding function does, just that it avoids a Div by 0 on a 0 number, and additionally, does not require the Excel functions to be available on the server. The actual code in MDX is:

Fix([Measures].[???] * Factor + 0.5 * Sgn([Measures].[???])) / Factor

Where Factor is the rounding factor - 1 for 0 decimal places, 10 for 1 and so on (defined by 1/Factor).

Of course, Factor of 0 will give us Div by 0 error and for truncation we can just use the truncation function in my article.

Furthermore, Chris Webb advises that if you have the Excel libraries installed on your server, you can just use Excel!Round() instead of the function I've provided (it requires Excel to be installed, as it uses Ceiling).

It is kind of bad that I did not include all this in the main article and I will ask SQL Server Central admins to include it as it is a definite miss.

Boyan Penev
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search