Sum Error in Group Footer 1

[tcgoth]

Crystal XI
SQL 2005

I have the following formulas in a report:

Detail Section (Both fields evaluate correctly)
@HSActive (correctly evaluates to 1)= if {@Service} = 'Home Sale Assistance' and {rpt_service.serv_status_desc} <> '>Cancellation' and {rpt_service.serv_status_desc} <> 'Cancelled' then 1 else 0

@HSCancel (correctly evaluates to 1) = if {@Service} = 'Home Sale Assistance' and ({rpt_service.serv_status_desc} = '>Cancellation' or {rpt_service.serv_status_desc} = 'Cancelled') then 1
else 0

GF2 (Evaluates Correctly to 0)
@HSActiveCount = if {@HSActive}>0 and {@HSCancel}<1
then 1 else 0

GF1 Sum(@HSActive Count) (Incorrectly evaluates to 1)

I have intentionally selected only one record to verify the formulas are working properly. Any ideas on why (SUM) formula would evaluate to "1" in GF1 when the only record in the selection evaluates to "0" in GF2?

Any assistance would be most appreciated!

 

[Madawc]

You could do the job much more easily using one or more running totals. The use of Crystal's automated totals is outlined at FAQ767-6524. Once you get used to them, they save a lot of time and work.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Your GF2 formula is only reading the last detail record in that group. You should be using a summary on your detail records at the GF2 level. In the above scenario, you probably have an earlier detail record that = 1. You can see this by placing the detail level formulas in the detail section.

-LB

 

[tcgoth]

lbass,

Thank you for your response. I thought I was effectively Summarizing the Detail records via the formula in GF2? I tried a number of variations but each fails. I need to evaluate multiple conditions in the details section to arrive at a summary result in GF2, then be able to sum the results from GF2 into GF1.

Here are more details on my format and what I am trying to accomplish and sample data of desired outputs.

Logic: If a customer has an Active Service1 and a corresponding Cancelled Service1 in Details Sections then I want GF2 CustService1 count=0.

If a customer has an Active Service1 in Details Section and NO corresponding Cancelled Service1 then I want GF2 CustService1 count=1.

If a customer has an Cancelled Service1 and no corresponding Active Service1 in Details section then I want GF2 CancelService1 count=1.


GH1 Division

GH2 Customer "Jones, John"
Detail Section:
"Service 1" "Active"
"Service 1" "Cancel"
"Service 2" "Active"
GF2 Customer "Jones, John", "CustService1 Count =0", "CustService2 Count = 1" "CustCancelService1 count=0"

GH2 Customer "Smith, Tom"
Detail Section:
"Service 1" "Active"
"Service 2" "Active"
GF2 Customer "Smith, Tom", "CustService1 Count =1", "CustService2 Count = 1", "CustCancelService1 count=0"

GH2 Customer "Watts, Nancy"
Detail Section:
"Service 1" "Cancel"
"Service 2" "Active"
GF2 Customer "Watts, Nancy", "CustService1 Count =0", "CustService2 Count = 1", "CustCancelService1 count=1"


GF1 Division Summary "Sum(@CustService1) Count = 1", "Sum(@CustService2) Count = 3", "Sum(@CustCancelService1) count=1", "Sum(@CustCancelService2 Count = 0"

Formulas being used:

In Detail Section: All formulas evaluate correctly

@Service1 = if {@Service} = 'Service1' and {rpt_service.serv_status_desc} <> 'Cancel' then 1
else 0

@Service2 = if {@Service} = 'Service2' and {rpt_service.serv_status_desc} <> 'Cancel' then 1
else 0

@CancelService1 = if {@Service} = 'Service1' and {rpt_service.serv_status_desc} = 'Cancel' then 1
else 0

@CancelService2 = if {@Service} = 'Service2' and {rpt_service.serv_status_desc} = 'Cancel' then 1
else 0


In GF2 Section (Summarizing Customer):Formulas evaluate Correctly

@CustService1 = if {@Service1}>0
and {@CancelService1}<1
then 1
else 0

@CustService2 = if {@Service2}>0
and {@CancelService2}<1
then 1
else 0

@CustCancelService1 = if {@CancelService1}>0
and {@Service1}<1
then 1
else 0

@CustCancelService2 = if {@CancelService2}>0
and {@Service2}<1
then 1
else 0

In GF1 (formulas evaluate INCORRECTLY) - only picking up last detail record as you stated?

Sum of @CustService1, Sum of @CustService2, Sum of @CustCancelService1, Sum of @CustCancelService2

 

[lbass]

Your GF2 formulas will only reflect the last record in the group, so I would be very surprised if they appeared correctly in GF2. The GF2 formulas should be written like this:

//{@GF2svc1}:
if sum({@Service1},{table.customer}) > 0 and
sum({@CancelService1},{table.customer})= 0 then 1

Add another formula in GF2 to accumulate for the GF1 totals:

//{@accum}:
whileprintingrecords;
numbervar svc1 := svc1 + {@GF2svc1};
numbervar svc2 := svc2 + {@GF2svc2};
numbervar can1 := can1 + {@GF2can1};
numbervar can2 := can2 + {@GF2can2};

Suppress this formula, and then create separate formulas for each display in GF1, like this:

//{@displaysvc1};
whileprintingrecords;
numbervar svc1;

Add reset formulas to GH1:
whileprintingrecords;
numbervar svc1;
numbervar svc2;
numbervar can1;
numbervar can2;
if not inrepeatedgroupheader then (
svc1 := 0;
svc2 := 0;
can1 := 0;
can2 := 0
);

-LB