Sum If subreport variable

[IceRuby]

Hi there
Using Crystal Version 9.0, ODBC to MS SQL 2000.

My report is setup as below. Main report and subreport (actual hours).
I am using two variables to be total Actual hours within GF#2 and GF#1. Each of these are set to 0 within GH#2.

(subrpt)
Alloc Act Var. %Comp
Hrs Hrs
GH#1 Team (String) DV
GH#2 Ord Header (String) O457
GH#3 Resource No. (String) 43 17 26 39.53
RH12 12 12 0 100.00
GF#2 55 @subsum1
GF#1 55 @totalsum
GH#1 Team (String) HM
GH#2 Ord Header (String) O558
GH#3 Resource No. (String) 22 17 4 77.27
RH12 13 20 -7 100.00
GF#2 35 @subsum1
GF#1 35 @totalsum

RF 90 ?????

I need to setup another formula to supply a grand total for actual hours BUT (the clincher) if the variance between Allocated and Actual hours is >=0 then the Actual hours should be included in sum, if the variance is <0 then the Allocated hours should be totalled.

e.g. All the actual hours would be summed in above example with exception of variance of -7, 13 should be included within sum not 20. Total would be 59.

Sample of print out
(subrpt)
Alloc Act Var. %Comp
Hrs Hrs
Derwent Valley
O986
RH21 5 8 -3 100.00
RH13 16 4 12 25.00
0986 21 12 9 57.14

0700
RH13 5 10 -5 100.00
0700 5 10 -5 100.00

26 31
(31= 5+4+12+5+5)

Thanks for your help in advance.

 

[lbass]

If only the actual field is from the subreport, then assuming you have set up the actual field as a shared variable in the subreport, you could reference it in the main report in a formula like:

//{@sumactorall} for the details section:
whileprintingrecords;
shared numbervar actual;
numbervar sumactorall;
if {table.alloc}-actual < 0 then sumactorall := sumactorall + {table.alloc} else sumactorall := sumactorall + actual;

Then you could display the result with another formula:

//{@display}:
whileprintingrecords;
numbervar sumactorall;

You would need to have a reset formula for the shared variable in Group Header #3 I think.

I'm assuming that your summaries in the GF#1 and GF#2 also refer to the shared variable, but I wonder why you have both resetting in GH#2--it seems to me the GF#1 summary would require a reset in GH#1, not GH#2.

-LB

 

[IceRuby]

Hi LB and thanks for response.
Your right my typo for reset value in GH#2.

I have a problem with formula, it is almost working but seems to be having an issue in some instances when alternating between if criteria.

I changed your @sumactorall formula as below for some reason it was supplying incorrect numbers. Outlined results for in sample below for both your original formual (all-act) and variation (@var<0)

Adjusted formula:-
(adjustment only within if line)
if(@variance<0) then sumactorall:= sumactorall + {table.alloc} else sumactorall:= sumactorall + actual

result (subrpt) result
all-act Alloc Act Var. (@var<0)
Hrs Hrs @sumact
Derwent Valley
O986
RH21 4 18 7 11 7 OK
RH13 6 13 22 -9 9 (?)
0986 31 29 2

0700
RH13 8 2 3 -1 11 OK
rh11 10 2 2 -5 13 OK
0700 4 5

1534
RH12 18 16 20 4 21 (?)
RH43 26 40 33 7 54 OK
1534 56 53

TOTALS 26 91 54 (S/B 73)
(? line is incorrect, OK selected correct value on line but sum incorrect due to above ? error)

Sorry going round in circles with this one.

Thanks for your help in advance.

 

[lbass]

I assumed that {@variance} = {table.alloc}-actual, but apparently it doesn't, so what is in your formula {@variance}?

Is actual the ONLY field returned by the subreport, with all other fields coming from the main report?

Is your example showing only group summaries, or are the rows starting with "RH..." details? My accumulation formula
was meant for the detail section and assumed that {@variance} was a detail level formula.

Do you have any suppressed groups?

-LB

 

[IceRuby]

Hi there and thanks for response.

Yes ONLY actual field is supplied from subreport, all others coming from main report.

{@variance} formula is:-

SUM({table.Allocated Hrs}, {table.Resource no.})-{@actual})

The {@actual} formula being a shared numbervar from subreport. This being aggregate of actual hours per resource, per order, per team same as main report.

I have the details section is suppressed, I need report to summarize and group each team (GH#1), order (GH#2) and resources (GH#3). (I could >200 single lines for each single resource) GF#3 only other suppression. Reporting via Group summary lines.

Thanks for your help, sorry if initial info not explained very well. (paranoid about placing huge postings)

Thanks for your help in advance.

 

[lbass]

Okay, I just tested this here, and it works. This assumes that your shared variable actual in the subreport is set up as:

whileprintingrecords;
shared numbervar actual := sum({table.actual},{table.resourceno});

...and that you have linked the subreport to the main report on resource number, and have placed the subreport in the GH#3a header. You should have {@variance} placed in GH#3b, with "underlay following sections" set for GH#3a. {@variance} should look something like:

whileprintingrecords;
shared numbervar actual;
Sum ({table.alloc}, {table.resourceno}) - actual;

Your grand total accumulation formula {@accum} should be placed in the GH#3b header:

whileprintingrecords;
shared numbervar actual;
numbervar grtot;

if {@variance} < 0 then grtot := grtot + sum({table.alloc},{table.resourceno}) else
grtot := grtot + actual;

Then place the display formula {@display} in the report footer:

whileprintingrecords;
numbervar grtot;

You can add in variables to capture the accumulation at the group levels, using a reset in the group header for the particular group variable.

-LB