Selecting records based on fields in subreports

[smibarb]

I have a report that has three sub-reports. Each detail line in the main report shows operator information based on operators selected by specified site parameters. Each of the three subreport fields contains a count of work done for a specified period of time.

I would like to have a variation of this report in which only operators who have "0" showing in all three subreports count fields show up in the main report.

Any suggestions how to do this? Being a novice, I originally thought I would use a formula or select expert in the main report, but I realize now that is not an option.

 

[synapsevampire]

Please post technical information, such as your version of software and where in the Main reports the subreports are placed, and where the data is displayed in the main report.

In general subreports are a bad idea, so consider changing the architecture.

You might receive reasonable suggestions were you to post:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[smibarb]

Sorry, I overlooked posting this important information.

Crystal Version 10.0.0.533

Database/connectivity OLE DB(ADO) (Microsoft OLE DB Provider for SQL server).

The three subreports extract data from three separate tables that have vast amounts of other data. I am not sure that I can pull this specific combination of data without using subreports. The subreports are the numbers that appear in the last three columns in the example below. The subreports are located in Details. The numbers are summary fields in the subreport which are Counts of the data that appear in those reports. Here is an example of how the report currently appears.

Example Data:

Op ID Name Inservice Site #QClow #QChigh #Pt

1234 Doe,Jane 01Jan03 RAH 1 1 5
4567 Jones,Ann 16Jun00 RAH 0 0 0
7890 Low,Frank 05May05 RAH 1 1 2

My expected output would be to have a report that would only show operators like Ann Jones in my example above, who have the number "0" in all of the three sub-reports.

 

[synapsevampire]

As you might imagine, this type of information is consistent with many requirements for those not using Crystal, so there are alternatives, such as:

-Create a View or Stored Procedure
-Use a SQL Expression to create the equivalent of a subquery

Either of these will prove MUCH faster as the database does the work, as opposed to Crystal firing the subreports for EVERY detail row in the main report.

An example of this type of query in generic terms would be:

select table1.opid, table1.name, table1.inservice, table1.site, (select sum(amount) from table2 where table2.opid = table1.opid) MySum
from table1

You can also use a derived table (aka inline query) which is generally the SQL Server approach:

select table1.opid, table1.name, table1.inservice, table1.site
from
table1,
(select opid, sum(amount) from table2
group by opid) Table2
where table.1.opid = table2.opid

This sort of query can be pasted in as a Command Object in CR 10 and will prove infinitely faster.

If you have further questions about this sort of thing, solicit your dbas assistance.

-k