CR 8.5 Subreport groups & subtotals not included in Main report

[rradelet]

I am working in Crystal 8.5 with SQL 2003.

I have a main report with 4 subreports and I am using shared variables to pull summary totals from the subreports to the main report.

My problem is that some group names that occur in the subreports do not exist among the group names in the main report, so those totals do not come across.

I am using only one table in the report.

The report involves earnings for delivery drivers where up to 5 drivers may complete different stages of the delivery and each driver is paid for each stage.

The fields being used are:
Driver1, Driver2, Driver3, Driver4, Driver5
(These contain the number of the driver that completed each stage)

Driver1Pay, Driver2Pay, Driver3Pay, Driver4Pay, Driver5Pay
(These contain the earnings paid for each stage)

The details of the main report contain:

Driver1 and Driver1Pay which are then grouped by Driver1 and summaries are created for the count of orders for each group as well as the sum of Driver1Pay.

The subreports are the same structure and produce subtotals grouped on Driver2, Driver3 etc.

The main report contains a formula for the shared variables:
Whileprintingrecords;
Shared numberVar Driver2Count;
Shared numberVar Driver3Count;
Shared numberVar Driver4Count;
Shared numberVar Driver5Count;
Count ({Table.Driver1},{Table.Driver1})+Driver2Count+Driver3Count+Driver4Count+Driver5Count

I also have a formula in the group header to reset the subtotal count for each group

whileprintingrecords;
shared numbervar Driver2Count := 0;
shared numbervar Driver3Count := 0;
shared numbervar Driver4Count := 0;
shared numbervar Driver5Count := 0;


Each of the Driver2 Driver3 etc fields in the subreports are linked to Driver1 in the main report.

The problem I am having is that there are drivers that never do the first stage of the delivery and therefore the group names for those drivers occur only in the subreports. Those group names never occur in the main report, so those subreport totals don’t get included in the main report totals.

Does anyone know a solution to this?

 

[synapsevampire]

Your problem is that you don't have data, so get the data.

What you want is a list of all drivers as a main report, so change what is used as the main report and move your main report out as a subreport of that, or add all drivers to your current main report, if that's possible.

Keep in mind that subreports are generally a bad idea as they are slow because they fire new sql for every instance (4 additional sql statements are fired for each group).

So the query for the main report could have as it's datasource something as simple as selecting the distinct {table.drivernumber}'s from your table.

Make sense?

Alternatively, you might create all of the data in a UNION ALL query or a database View or Stored Procedure, but you'd need to learn what your database type is and if it's capable of this, perhaps your dba can help.

Keep in mind that successful posts generaly include technical information:

Crystal version (you did)
Databse/connectivity used
Example data
Expected output

-k

-k

 

[rradelet]

Thanks Synapse. Your suggestion worked. I have all the driver numbers in a separate table, so creating a simple main report grouped by driver number and linking 5 supreports did the trick.