Crystal Reports:
Varying the number of
decimal places
to suit the precision of the number
In a Crystal Report, it is easy to change the number of
decimal places on a numeric field. But at a site recently, a given field
could range from 1.753 to 5,000,000. The decimal places were not
appropriate for large values, but vital if the smaller values required
them.
The answer is to use conditional formatting on the
decimal places of the number. The formula is:
if {table.field}=truncate({table.field},0) then 0
else
if {table.field}=truncate({table.field},1) then 1
else
if {table.field}=truncate({table.field},2) then 2
else 3 //no more than 3 decimals ever used
This example could be expanded to allow for a higher
precision if required. The other place we’ve used it is in a time-cost
system where the transactions are normally in whole hours, but will show
.5 or .25 of an hour if required.
This brings up the question of how many decimal places you
should display. The answer depends upon the business intelligence question being
answered by the report and how much granularity that answer requires.
Generally, the more aggregated the view the less
granularity you want. Suppose, for example, you worked in a government agency
that needed to know how many grams of sugar the average American consumed per
year. Let's say the answer works out to something with 21 decimal places.
Probably no agency would need to go out that far. If your
agency's concern was for, say, addressing healthy choices then no decimal place
would be needed. Obviously, your agency is after broad dietary trends. If a
person eats an extra tenth of a gram of sugar per year or reduces by that much
it does not matter.
But if your agency needs to extrapolate from that
aggregate number or in some other way use it for further mathematical analysis,
then you might want to go out several decimal places.
This example probably doesn't apply to your particular
situation. But the concept does. Before you get crazy with decimals, decide on
the amount of granularity actually needed. Often, the decimals merely could the
actual picture and you are better off without them.
When you do decide on how many decimal places to go out,
stay consistent. Don't show one quantity with one decimal place and another with
four. That just forces the user's mind to resolve the discrepancy; even if the
user isn't consciously aware of the discrepancy. This makes the report less
clear and reduces its value.
So if you show 17.389 for one value, don't show 15.0 for
another. Show 15.000. This consistency principle also applies to fonts, colors,
and line thicknesses. Keep differences to a minimum, because the user's brain
has to process all of them. Differences are information, and if it's useless
information it needs to be removed.
Some people will argue that having more decimals is better, because that
means the numbers are more precise. But again, we get back to the useless
information problem.
Determine the best granularity, not the most granularity possible. And go
with what is best.
This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd.
It may be freely copied and distributed as long as the
original copyright is displayed and no modifications are made to this
material. Extracts are permitted. The names Crystal Reports and Seagate
Info are trademarks owned by Business Objects. |
