CODEWITHSUNDEEP
crystal-reportscrystal-reports-2010crystal-reports-xirounding
Kristen
Kristen

Reputation: 453

Crystal Reports Rounding Issues

I'm working on creating a report in Crystal reports that is summarizing customers that were activated and deactivated in the same year. My report is looking something like this:

<table border=1>
  <tr>
    <td>Customer</td>
    <td>Create Date</td>
    <td>End Date</td>
    <td>Days Active</td>
    <td>Months Billed</td>
    <td>Invoice Amount</td>
    <td>Average Invoice Amount</td>
    <td>3 Year Lost Revenue</td>
    <td>5 Year Lost Revenue</td>
  </tr>
  <tr>
    <td>Customer 1</td>
    <td>11/20/2014</td>
    <td>08/05/2015</td>
    <td>258</td>
    <td>9</td>
    <td>$2200.00</td>
    <td>$244.44</td>
    <td>$6600.00</td>
    <td>$12466.67</td>
  </tr>
</table>

Basically the problem I'm having is the 3 year and 5 year lost revenue are not rounding properly.

I'll explain how I'm calculating everything and maybe someone can explain why those 2 columns are rounding weird.

Ok so customer, create date, and end date are all pulled from a database table.

Days Active = End Date - Created Date
Months Billed = Ceiling(Days Active/30)

For invoice amount I have a formula that basically says:
if isnull(column in the table I'm pulling from) then 0
else running sum total for the column with the Invoices in it

Average Invoice Amount = Invoice Amount / Months Billed

3 Year Lost Revenue = (36*Average Invoice Amount) - Invoice Amount
5 Year Lost Revenue = (60*Average Invoice Amount) - Invoice Amount

The numbers I used in my table for Invoice Amount, Avg Invoice and the 3 and 5 year revenue are actual numbers I'm getting. So the months billed, invoice amount and average invoice amount are all correct.
But as you can see
($244.44 * 36) - $2200 = $6599.84
($244.44 * 60) - $2200 = $12466.40

Both these columns are formatted the same too but somehow they're both rounding differently...any idea what I'm doing to make it round like this?

Thanks!

Upvotes: 0

Views: 3250

Answers (2)

Arvo
Arvo

Reputation: 10570

You likely forgot to round intermediate value of Average Invoice Amount. 2200/9=244.4444444..., but your manual calculation uses just 244.44 - sure results will differ.

Upvotes: 1

Ajay2707
Ajay2707

Reputation: 5798

Rather than you apply new logic that why this happened, you simply format decimal field by formatting the field. Crystal report have feature to format field as per data-type of field.

This below text taken from this link

Actually, you can select the field in a text box, right click, and format it. However, here's a better way of doing percentages.

  1. Put the number field on the report.
  2. Right-click and format.
  3. On the Number tab, click on the Customize button.
  4. On the Number tab, set the number of decimal places.
  5. On the Currency Symbol tab, Enable the currency symbol, Set the Position to the right side of the number, and change "$" to "%".

This will give you the percent sign without having to use a text box.

Check this link too for visualizing

https://www.webucator.com/blog/2010/05/crystal-reports-rounding-function/

Upvotes: 0

Related Questions