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
The report is doing exactly what you've told it to d o :)
you could try
total(round([Union3].[Current Year Actuals]/1000,0))
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
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?
instead of /1000 use the scale = -3 in display properties to avoid eliminating number under $1000
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!