Rounded numbers with incorrect total on report

[bren1519]

Hi all,

I am creating a report that outputs to Excel.  Our balance sheet is rounded and shown in thousands.  In our excel version of the balance sheet I would use round((AMOUNT/1000),) but in excel, then sum of the above for the total.  I've recreated the balance sheet in Cognos to output to excel and using the formula below have the numbers rounding and displaying correctly, however the total is (correctly) the total of the amounts rounded not the total of the rounded numbers above (if that makes sense).  I've put some sample numbers below to show what I mean.

round ((TOTAL([Union3].[Current Year Actuals]))/1000,0)

Is there a way to sum the rounded numbers and have the report add correctly?
FULL NUMBER         ROUNDED            CORRECT
     9,045,349.68                9,045                 9,045
   65,362,246.86              65,362               65,362
   32,239,326.15              32,239               32,239
  106,646,922.69            106,647              106,646

[BigChris]

The report is doing exactly what you've told it to d o :)

you could try

total(round([Union3].[Current Year Actuals]/1000,0))

[bren1519]

Hi Chris,

Yeah....I know it is but I want it do do what is in my head lol!  So the change in formula didn't work as it was doing the /1000 before totaling and thus eliminating some of my numbers that are under $1000.  Still trying different things but I'm resigned to the fact I will probably have to check and re-total the balance sheet if need be once in Excel.

Thanks!
Brenda

[BigChris]

This is purely a side observation...and it could just be my maths background rather than accountancy...but.

If you've got 20 figures, all at say 10,450 your balance sheet will show a total of 200,000 when the real total would be 209,000...is that ok?

[Francis aka khayman]

instead of /1000 use the scale = -3 in display properties to avoid eliminating number under $1000

[bren1519]

Hi Chris,
So yes, that would be the case.  In my current excel workbook, I am using embedded queries (from another product), it pulls in the detailed number then the total is divided by 1000 and rounded.  It works out as I've run the statements without any rounding, division etc to check and it's usually a couple of thousand out in rounding but all comes out in the wash lol!

@khayman - I will try that.  I did think of that but wasn't sure how it would affect the figures, so I will give it a try and see.  thx for the input.

Thanks to both for the assistance!