Multi-Table Filtering w/ Subreports

[mgrejtak]

Crystal Reports 8.5 on Oracle

Table A has inventory and is main table of report

Table B,C,D are accessed via subreports and display information about what is needed vs what is scheduled.

Subreports B,C,D all drive to a total per subreport that is displayed in the main report.

HOWEVER - to complicate the matter the user (a scheduler) says that given the nature of their work - they should have over 50% of the items on the report "covered" - meaning that they dont need to see it on the report. It is possible to determine what is "covered" vs not by comparing totals via subreports B,c,D - but then feed back to main report and not display the record in Table A whatsoever is where I get lost!

So in other words the report pulls lots of data that is good - but almost 50% of the records shown should not - if computations in B,C,D compute that it is so. In that case don't even show it in A.

Any ideas on how to best achieve w/o making the process very long?

 

[synapsevampire]

If you show the data in table A AFTER running the subreports, use a shared variable as the suppression.

Keep in mind that subreports are a bad idea, and that good SQL in either a View or a Stored Procedure will eliminate the need, check with your dba.

Shared variables might work as:

Main Report formula prior to running Subreports:
shared numbervar BSum:= 0

subreport B formula:
shared numbervar BSum:= sum({subB.amount})

After subreport B fires (in a section afterwards in the main report), you can reference that variable in a supress formula, such as:

shared numbervar BSum;
BSum < 50000 // whatever criteria to suppress

-k

 

[mgrejtak]

thanks for the reply - but you lost me on how to suppress in main report. Is there more than one detail section in the main report to pull this off?

 

[synapsevampire]

Right click any section and select insert section below if you need additional sections.

I didn't address details specifically because YOU didn't state where the subreport was.

-k

 

[mgrejtak]

To clarify - all of the subreports are currently stemming from the main report. So A calls B,C,D. This is done because B,C,D are all pulled from different tables.

How does the supression know to do suppress after vs before?

Thanks in advance.

 

[mgrejtak]

One MAJOR clarification - the subreports B,C,D called by main report A are run after the data in A is displayed. Being that as the case - will the previous suggestions still apply?

Thanks in advance!

 

[synapsevampire]

The main report won't know what's in the subreports prior to running them, so either display the main data AFTER running the subreports, or you'll have to run the subreports twice.

Further demonstrating that subreports are puuuuuuuuuuuure eeeeeeevuuuuuuuuhl.

Again, consider using some better SQL by eitehr using Views, a Stored Procedure, or the more tricky SQL Expressions.

-k

 

[mgrejtak]

In addition to your point about displaying A after the subreports are run - it should be pointed out that in the case where we would not want to display A if the calculation results a particular way - we ALSO would not want to display B,C, OR D in that case as well. It is a clear case of ALL/NOTHING processing.

That being the case - does it offer other ways of going about this?