Sum total: PLEASE HELP

[B1naryPro]

I would like to get results of the sum total of a certain field. I tried to use the sum function but it returns an error. Here it is:
Sum ({Ward.Ward})= "10"
any help greatly appreciated. need some nice examples Jimmy
MCSE, MCSA

 

[kenhamady]

You need to write a formula that says:

if {Ward.Ward}= "10"
then {Numeric.field}
else 0

and then sum this formula. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

"10" isn't a number, it's text, so it can't be summed without being converted to a number.

Create a formula that has:

val({Ward.Ward})

Use this formula in a Running Total and place something like:

{Ward.Ward}= 10 in the Evaluate->Use a Formula area.

This would sum the ward field for those that have the text 10.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[B1naryPro]

if {Ward.Ward}= "10"
then {Numeric.field}
else 0
and then sum this formula.
NOW how would the sum formula look like??
sum{Numeric.field}???
thanks, much appreciated Jimmy
MCSE, MCSA

 

[kenhamady]

You put the formula on the report.
Then you right-click on the formula and select:

Insert - Grand total
or
Insert - Subtotal

I am assuming that you are summing another numeric field for all recrods with a Ward of 10. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[B1naryPro]

the situation i am in is that i need to know the total number of wards that = 10 in the database for a particular month. so it's almost counting the number of wards that equal 10.
count{table.ward}
sorry for being confusing
Jimmy
MCSE, MCSA

 

[kenhamady]

Then use this:

if {Ward.Ward}= "10"
then 1
else 0

and then sum the formula. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Reebo99]

The last way does work, but it involves 2 calculations. You can do it in one :

if {Field Name} = "10" then Count ({field name}, {group name})

and place this formula field in the Group header or footer.

Juan
Scotland

 

[Naith]

Juan,

Did you test that? Because I don't think that's going to come up with the goods here. You can't quantify a condition like that at a record level in the group header. You either do it the way Ken suggested, use a conditional running total, or use a variable to accumulate the count.

Either way, it's all at the detail level.

Naith

 

[Reebo99]

Naith,

Yeah, it works fine. Because I hand over a load of reports to our support people thay like as few calculations as possible, so I've used this as a replacement to bring down the number of calcs from 2 to 1. Have a go at recreating the formula, I think you'll be suprised.

Juan

 

[Naith]

So, in this instance, you reckon that if you had the following:

Group Header: Your formula
Details:
Ward 10
Ward 11
Ward 12
Ward 10

you'd get 2 instead of 4 in the group header?

Naith

 

[B1naryPro]

thank you guys for the help. but now what do i put in the group field paramter??
thanks Jimmy
MCSE, MCSA

 

[B1naryPro]

Still not coming up with the records for ward 10 it is giving me records for all wards. Jimmy
MCSE, MCSA

 

[Reebo99]

Naith,

I've just tested the scenario you highlighted, and yes it gave me 2 not 4. The If statement stops it counting all records and it only counts where there is an instance of "10".

Juan

 

[Reebo99]

Naith,

Looks like I've got to swallow a bit of pride here. It must be a pretty unstable way of doing it, because on the first test it worked fine, on subsequent tests it sometimes works, and sometimes doesn't. Sorry for my misleading comments.

Juan

 

[Naith]

No sweat, Juan.

You just had me curious because in theory, that IF shouldn't be possible, being as the group header only gets processed once, while the details for the same group may get processed 100s of times.

Anyway, have a good weekend - and see if you can grab those reports back before your support guys notice.

Naith