Excel output -> numbers can not be summed in excel

Question

Excel output -> numbers can not be summed in excel

Jos Janssen

Old Hand

Points: 322
More actions
October 13, 2006 at 1:48 am

#170676

Hello all,
I have created some reports that will be processed further in Excel, but the numeric values are somehow lost in translation in Excel. That is:
when I SUM() them in Excel, the total is always zero.
But when I sum them using [Cell + Cell + Cell] the total is correct.
Anyone know a solid method to keep numbers as numbers in Excel output? Without any additional handling in Excel that is...
The problem could be a regional setting: we use dutch formatting of numbers (decimal separator = comma, thousands separater = dot).
However, US and Dutch formatting in RS both gives same results as described above.
Regards,
Jos Janssen

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

James Riehl Ten Centuries Points: 1154 More actions · Answer 1

Try calling .GetType() on the field to see what exactly the type is you're returning. You may be able to cast in some way shape or form to get you're needed results.

The call would look like this =Fields!FieldName.Value.GetType().ToString()

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 2

General thoughts, not entirely related to one another <s>:

1. Are any of the cells or values null? I'm thinking (based on some stuff that RS does when assigning XSD data types) that if all the values in a column aren't of a consistent type, it may not assign appropriate Excel instructions.

2. Are you particularly concerned with the formatting of this particular report? Even if so, for a test, what happens when you export as CSV -- does Excel recognize the numbers properly?

3. What happens if you send an export of XML to Excel? If you send it directly, Excel has some smarts for working with XML schemas that may be useful. Of course, you'll lose the built in formatting of this particular report (see #2 and #4) but FWIW if you're moving up to Excel 2007 you may not even care <g>.

4. XML and a XSLT transformation really gives you the best possible Excel result, IMHO. You can apply whatever instructions you need, and format however you need.

Hope something here rings a bell,

>L<

Jos Janssen Old Hand Points: 322 More actions · Answer 3

Hello all,

Thanks for your input in the matter.

I have found a workaround in using a different formatting for the numeric values. Instead of 'g' or N0 or N2 I used #,##0.00 and this way Excel sees them as numbers. Maybe because the latter is also a valid format string for Excel?

For your curiosity:

The .GetType = DECIMAL

The XML output works like a breeze and was very enlightening. I didn't use that before but it is a very powerfull way for end-users to work with the report data. With my Access apps, I always have aa Excel output option for every report which is very appreciated by the users, and now I found the counter for RS reports.