amount total on a subreport with multiple detail lines

[iwm]

I am working with four sub reports, pages 1, 2, 3 & 4 respectively. Each sub report is will contain 18 detail lines. The first sub report will contain detail lines 1-18 and the second will contain 19-36, etc. The sub reports are in sequential report footers of the main report. Each sub report should contain a subtotal for the 18 items contained in that report.

EXAMPLE of data returned from SQL query:

# date destination meals Trans. misc.
1 3/1/06 Atlanta 50.00 300.00 10.00
2 3/2/06 Columbia 45.00 275.00

Only one expense can be printed per line. Therefore, the desired result is for line #1 to appear as 3 lines and line #2 should appear as 2 lines (total of 5 detail lines). I have accomplished this by creating detail sections a, b,& c,(meals, Trans, misc.)and conditionally suppressing them.

EXAMPLE of conditional suppression of detail section a:

{Travel.meals}<=.00

My problem is creating the subtotal formula summarizing only 18 detail lines per report.
For example:

The total amount for all 4 sub reports is $700.00.
sub report # 1 subtotal $250.00
sub report # 2 subtotal $250.00
sub report # 3 subtotal $50.00
sub report # 4 subtotal $150.00

I need help creating a formula for the subtotals. I need to limit the amount of data summarized per sub report to 18 lines.

Normally, I would use the Record Number to determine the amt of detail lines contained per page. However in this case that will not work. Crystal considers the three occurrences of line # 1 are one record (Record Number) and the two occurrences of line two are considered one record.

How can I make my sub report count each detail line and not just the # of records returned from SQL?

Any assistance in this matter will be tremendously appreciated.

Ingrid

 

[Madawc]

Have you tried DistinctCount?

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

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

 

[lbass]

I think you could create a formula like this:

whileprintingrecords;
numbervar cnt;

if {table.meals} > 0 then
cnt := cnt + 1;
if {table.trav} > 0 then
cnt := cnt + 1;
if {table.misc} > 0 then
cnt := cnt + 1;

Place this in one of the detail sections. Then reference numbervar cnt in another formula or for suppression, as in:

whileprintingrecords;
numbervar cnt;
numbervar sumamt;
if cnt in 19 to 36 then
sumamt := sumamt + {table.amt};

//etc.

-LB