How do I total fields where table rows may not exist?

[JKrumins]

Hi, I am new to Crystal, though have worked with SQL for some time. I guess this is a simple problem but I'm confused by all the different tools avaialble in Crystal...

I want one row on a report for each product (table A.part_no, tableA.description, tableA.qty_available etc). I also want another 2 fields for each row :

sum(tableB.future_demand_qty) where tableB.part_no=tableA.part_no

and

sum(tableC.qty_issued) where tableC.part_no=tableA.part_no and tableC.type (is of a certain type) and tableC.transaction_date (is between parameterised dates)

The problem is that in either of the tables B and C there may be no rows that satisfy the condition. I still need the fields of table A displayed, and ZERO for the table B and C sums.

I have looked at formulas, sub-reports, sub-totals etc but need some help to point me in the right direction. I don't know how to do the Summing and the conditional testing and the sum=0 if none found! All suggestions gratefully recevied, Thanks, Jan.

 

[synapsevampire]

Join the tables to A using a Left Outer.

Group by the a.part_no and display results in the group footer.

The sum(b.future_demand) is simple, the only complication is the c.qty_issued.

Edit the record selection formula to have something like:

(
isnull(tableC.type)
or
(tableC.type = {?typeparm}
and
tableC.transaction_date = {?transparm})
)

Should work.

-k

 

[Ngolem]

Subreports are probably your best approach...linked by Part_no between the maain report and subreport

so your Main reort row would look like this

{TA.part_no} {TA.description} {TA.qty_available} {S-Rpt1} {S-Rpt2}

where S-Rpt1 & S-Rpt2 are the 2 "summing" Subreports

You cannot just join the tables with a left outter join since you are specifying conditions in some of the fields of the B and C tables and that makes it an equals join by the time Crystal does it

In your subreports in the report headers initialize the sum to be zero

//@Initialize
numberVar SumValue := 0.00;

In the report footer display the value after another formula adds the parts of the sum...if there are no records there is no problem then since the report footer will still give you a nice 0.00 to display in your report

//@DisplaySum
numberVar SumValue ;

SumValue;

For each subreport...suppress every section except the report footer...and in the main report size the subreport so just the Sum shows up nicely.

that should do it

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[JKrumins]

Thank you both very much for your prompt replies.

I have tried Jim's sub-report approach and it almost works!
The @Initialize works OK, the formula that adds the 'found' records to the initialised value works OK...
but the @DisplaySum in the footer seems to return the value set in @initialize, as if it were evaluated before the records in the detail have been added.

Where am I going wrong?

JK

 

[JKrumins]

Oh, by the way, can I use the result of the sub-report in further computations within my main report?