Conditional Running Total in Crystal Reports

2
votes

Using VS 2008 Crystal Reports, I would like to do a running total on a formula that is calculated on a group change. When I click on add a running total, this formula does not appear in the Available Tables and Fields list.

This is the logic:

On Change Group of group

if CalculatedValue > 0 then
    ReportRunningTotal1 += CalculatedValue  
else
    ReportRunningTotal2 += CalculatedValue

Can I specify a condition in a running total? If not, how else could I do this?

More info: I am doing a running total called GroupRunningTotal of the value of db field BillableHours. At change of group, I am comparing GroupRunningTotal to a db field for that group MaxHours, and I display a result of MaxHours - GroupRunningTotal at the group level.

Appropriate today - Think of it like the electoral college - the person who wins the election does not depend on total number of votes, but of number of votes in the electoral college.

2
crystal-reportscumulative-sum
This doesn't answer your question completely (I don't quite understand you), but yes you can specify a condition for your running total. In the Edit Running Total Field menu, play around with the Evaluate section. – PowerUser
I realize I asked the question incorrectly. I would like to do a running total on a formula that is calculated on a group change. When I click on add a running total, this formula does not appear in the Available Tables and Fields list. – Lill Lansey
"I am doing a running total called GroupRunningTotal of the value of db field BillableHours. At change of group, I am comparing GroupRunningTotal to a db field for that group MaxHours, and I display a result of MAxHours - GroupRunningTotal at the group level." Do you mean that you want to do this but can't, or that you are doing this and want to do something else? If the latter, what do you actually want to do? – user359040
@Mark . Currently, in the group header section, I am showing the difference between MaxHours ( a constant based on the group ) and ActualHours. This will be positive or negative. At the end of the report I want to show two totals - number of overhours and number of underhours as shown at the group level. Ie group1: 3 ; group2: -2; group3 :1 . In report footer I want to show Over:4 , under:-2. A running total at the field level would just show: Over:2 – Lill Lansey

2 Answers

3
votes

I'm interpreting your question to mean that you want to add up all the negative values in one running total (RT_Neg) and all the positive values in another (RT_Pos). How about this:

  1. Make the RT_Neg running total. Under Field to Summarize, sum your {Tbl1}.{Amount}. Under evaluate, enter "{Tbl1}.{Amount}<0" as your custom formula. Never reset.

  2. Make the RT_Pos running total. Under Field to Summarize, sum your {Tbl1}.{Amount}. Under evaluate, enter "{Tbl1}.{Amount}>0" as your custom formula. Never reset.

  3. Insert both running totals in the group footer (if you put them in the header, it may not sum properly)

Alternatively, you can:

  1. Make a custom formula "If {Tbl1}.{Amount}<0 then {Tbl1}.{Amount} else 0" and make a running total based off that.

I think one of these 2 options will get you to your goal.

1
votes

You most likely cannot use one RT field as condition for other RT field. You can use formulas, placed on group footer and evaluated 'whileprintingrecords()'; in these formulas you can assign/sum into some variables and display these variables at the end of report. About like next (generic idea only, you need initialization and display routines as well):

numbervar rtcurrent := sum({somefield}, {groupfield});
numbervar rtplus;
numbervar rtminus;
if (rtcurrent > 0)
then rtplus := rtplus + rtcurrent
else rtminus := rtminus + rtcurrent;