Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Cannot SUM a field -- a text issue? Post Reply Post New Topic
Author Message
Dan Bollinger
Newbie
Newbie


Joined: 27 Mar 2011
Online Status: Offline
Posts: 9
Quote Dan Bollinger Replybullet Topic: Cannot SUM a field -- a text issue?
     Posted: 03 Apr 2011 at 2:33pm
We are using Crystal Reports to access a new set of databases via ODBC. All of our reports are running just fine except for one issue that I have not seen before.

We cannot SUM a colum of fields using either the Total tab in the report wizard or by adding a sum function in SQL.

I think Crystal Reports is treating this field of numbers as if they were text. The Total drop-down selection list has maximum, minimum, count, etc., but not sum.

The DBbuilder says the database fields are all character strings. The problem persists even when they are set to numeric.
IP IP Logged
saoco77
Senior Member
Senior Member


Joined: 26 Jun 2007
Online Status: Offline
Posts: 104
Quote saoco77 Replybullet Posted: 04 Apr 2011 at 2:11am
have you tried converting the text field to a numeric field in Crystal Reports?

ToNumber({table.fieldname})

Doing this might allow you to sum the values
IP IP Logged
Dan Bollinger
Newbie
Newbie


Joined: 27 Mar 2011
Online Status: Offline
Posts: 9
Quote Dan Bollinger Replybullet Posted: 04 Apr 2011 at 6:04am
I'm not familiar with conversions. I tried a number of syntax variations including this one:

Sum(ToNumber({OP_ORD_LIN.ORG_SLS_ORD_QTY}))

At least it gave me a pregnant error:

"The summary/running total field could not be created."
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 04 Apr 2011 at 6:37am
Don't know what the issue might be. Try this:

Create a formula that returns a number type using the ToNumber function.

Then create another formula that sums up that new formula, which is now a number type.

Maybe crystal still sees it as a string field cause the DB tells it it's a string field, regardless how you want to convert it.

You mentioned you tried to set it as a numeric type on the DB itself. I don't know what the issue might be then.

Edited by Keikoku - 04 Apr 2011 at 6:40am
IP IP Logged
Dan Bollinger
Newbie
Newbie


Joined: 27 Mar 2011
Online Status: Offline
Posts: 9
Quote Dan Bollinger Replybullet Posted: 06 Apr 2011 at 12:25pm
Keikoku, Creating the formula did the trick! Thanks. This issue is resolved.

Dan
IP IP Logged
Dan Bollinger
Newbie
Newbie


Joined: 27 Mar 2011
Online Status: Offline
Posts: 9
Quote Dan Bollinger Replybullet Posted: 14 Jul 2011 at 6:23am
I have run across a related problem. What I'm doing is converting a character string to a number so I can get totals. While creating a #ToNumber forumula that I then summed worked on a field within one file, the same field in another file gives me this error:

The special variable "Formula" must be assigned a value within the formula.

Any ideas what this error means?

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Jul 2011 at 6:31am
make sure you do not have any records in your db that are not able to be converted to numeric
like a quatnitiy of 'alot'
IP IP Logged
Dan Bollinger
Newbie
Newbie


Joined: 27 Mar 2011
Online Status: Offline
Posts: 9
Quote Dan Bollinger Replybullet Posted: 14 Jul 2011 at 7:15am
I am not familiar with 'alot'. The fields are both total order quantities, one is in the open order file (which can be converted) and the other is in the shipped history file (which cannot be converted).
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Jul 2011 at 7:36am
'a lot' was a sample of bad data entry in a text field that should only have numbers.
if you use NOT(isnumeric(table.field)) in your select expert it would show you any rows that had a string that could not be converted to a number
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.