cumulative adding of totals based on criteria in a formula .

[bhargi]

Iam calling crystal reports 8.5 from Access97 using crysta report control. I have assigned a query(SelectQuery1) to the crystal report.

I need a cumulative running total to calculate all previous months hours based on my STATUS (previous)

Initially I used Running total on my field "SumOfMember" and in the formula I used {SelectQuery1.Status}= "Previous&quot
and grouped by clientNumber.
This is working very well. But if my "SumofMember" field is having null value(" &quot and not 0 running total doesn't show any value in this formula and even my GRand total doesn't show any value.(SUM(sumofmember))= returns no rows when there is null value.

Inorder to overcome this problem I created a formula field
in which I have the following code

local Currencyvar curAmt;
if {SelectQuery1.Status} = "Previous" then
curAmt := sum({SelectQuery1.SumOfMember}, {SelectQuery1.Client Number});

THis is getting all the current and previus values. ANd not useful to me as it it NOT checking the STATUS CRITERIA.

I used other formula like this
SUM(fld,fldCond,Conditionvalue)
sum({SelectQuery1.SumOfMember},{SelectQuery1.Status},"Previous&quot

THis is giving an error cannot create summary/running total.

What is the another way for me to get the SUMMARY TOTAL based on STATUS condition.
IF status="PRevious" then
variable= sum(sumofmember) is not getting the CORRECT values for that status. PLEASE ADVICE ME.

THANKS
bhargi

 

[kenhamady]

If you want a running total then you can't use the SUM functions. You will need to use a running total object (Insert, Field Object, Running Totals).

To put in a condition you can use the "Evaluate" property based on a formula. Put in a Boolean expression that defines the records to be included. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[bhargi]

Thanks Ken,

You want me to use EVALUATE property?? What does it do? Can you give me an example?

2)WHy this doesn't work in the formula field
SUM(fld,condFld,COnd)

This sum works in the formula field when there is not condition 3 attribute.

THANKS AGAIN. I appreciate it. :->

 

[kenhamady]

the condFld argument is not for logical conditions, it is for group "conditions". The second and third argument make the formula refer to a subtotal. Crystal just picked a confusing name for this argument.

If you set the evaluate to a formula, and enter the formula as:

{Eye.color} = "Green"

Then as the running total is accumulating values it will only include (evaluate) the ones that meet this criteria. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[bhargi]

THANKS KEN,
I did use the criteria but using IF condition and I used the SUm(fld,condlf,conf) that is the reason I am getting error.

THANKS AGAIN.I GOT IT.