1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Rounding issue

Discussion in 'SQL PL/SQL' started by jagadekara, Jun 10, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Friends,

    I have a xmlp report which is giving excel output.

    Opening Balance
    -----------------
    208,708,625.62
    11,896,120.30
    55,886,126.41
    57,103,938.52
    9,529,484.00
    ----------------
    343,124,295.00 :Total
    -------------------

    When we sum all 5 values it shows 343,124,294.85 but it is rounded of why?

    Opening balance is rounded in query. and total is summary column of that column.

    In rtf used same format for both. #,##0.00
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    Are you amount & total fields of type "Number" ?

    This usually occurs where you define it as type "Regular text" (the default type) and Excel ignores the format mask and does a rounding.
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Rajen,

    Both are Type: Number
    and Format: #,##0.00
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    This is really strange ... but I admit, I have seen strange outputs in Excel (specially in large/big reports).
    Can you please paste the XML output portion of this report.
    Thanks.
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    In your Excel spreadsheet, is the value in the 'Total' cell a number or a formula that SUMs the five columns above? If it is a formula, then Excel is doing the rounding. If the cell contains a number (and that number is 343,124,295.00), then the code generating the report is doing the rounding. That doesn't explain why it's happening, but at least will aid diagnosis.
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    SELECT fc.segment2 asset_major_category,round(sum(a.cost),2) opening_balance ,round(sum(a.deprn_reserve),2) depreciation_reserve
    FROM xxst_FA_RESERVEr_LEDGER_GT a
    ,FA_ADDITIONS b
    ,fa_categories fc
    ,(select max(b.request_id) req_id from xxst_fa_reserver_ledger_gt b) sq
    where a.asset_id(+)=b.asset_id
    and fc.category_id=b.asset_category_id(+)
    and (a.request_id=sq.req_id or a.request_id is null)
    and a.request_id(+)!=-1
    group by fc.segment2
    order by 1;

    This will display opening_balance.
    Then in rdf I took one summary column CS_OPENING_BALANCE
    which is data type Number and source as opening_balance.

    In rtf i took two form fields and put type as Number and format is #,##0.00
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Rajen,

    I sent .xml file to your email due to confidential...
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Thanks Jagadekara.

    Just checked the XML file; from what you stated in your mail:

    and from the values in XML file:

    I seems that the issue is not from Excel (rtf template) but rather from the program generating the output and the value of "CS_OPENING_COST" is itself rounded ("392066306." instead of "392066306.43").

    Please verify how this field is computed and declared.
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thank rajen,

    I too observe that CS_OPENING_COST shows 392066306. in .xml file.

    Why I don't know.

    It's just a summary column at report level. which has number data type and source as cost.

    cost is the field from query.
     
  10. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    I suppose you have formatted the total column output with decimals.
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Rajen,

    Finally I find the mistake.

    For CS_OPENING_COST width is 10. that's why it displays only 392066306.

    now i increased it to 20. now it showing 392066306.43
     
  12. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Ok great ! I suspected something wrong in the fields definition in report itself ..

    What I had meant in my previous post (sorry couldn't reply earlier ... taken up !) was the following - "Width" setting in the Summary column definition

    (In my case: defaulted to NUMBER(14,2) as my fields were of size NUMBER(14,2)) - see attachment.
     

    Attached Files:

  13. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Ok No problem.

    In my report default width is 10.

    Any how Thanks A Lot.