Monday, January 26, 2009

Crystal Reports : Sum a non-numeric field

Sometimes you may need to sum a "non numeric" (or text) field. Crystal will not allow this to happen until we convert the text field to a numeric one.
Let's assume the text field we want to sum is called "Price".
Step #1 : Create a new formula field.
Step #2 : Within the new formula field, use the following : ToNumber(Price).

The "ToNumber" function in crystal will now allow us to Sum the formula field within the report.

7 comments:

Daniel said...

Hello!
It doesn´t work because when you try to do this you will get the following message: "A field is required here" and the Crystal´s formula editor highlights the piece of code that contains the calling of ToNumber function... Any idea to solve this problem?
Thanks anyway...

Anonymous said...

I would think that Price is not a plain text, but the field holding the price. Something on the format {my_database_table.Price}

Daniel said...

Yes, it can be... but the field data type isn´t float, money or double. It´s string.
I couldn´t find any way to make the formula works properly.
I´m trying the following:

Sum(ToNumber({Table.Price}))

Table.Price is a text field. And, the formula doesn´t works...
Thanks anyway...

Justin Hill said...

Try splitting it into two formula fields:
Make one formula called "Price" with the following code:
ToText({Table.Price})
Then create a new formula to create your sum, thusly:
Sum({@Price})

The Crystal function SUM gets confused when you try to pass functions to it, so just try passing it the final, numerical value you want to summarize.

You can also use the INSERT|SUMMARY command from the top level menu.

Anonymous said...

its realy helpfull.

Anonymous said...

Thnks dear...it really helped...
can you also tell how to find total of entries on single pages only, in case we have multiple pages, i would like to have independent sum on each page

Anonymous said...

thanks really helpful