Handling Many Criterion in Single Report

[shelby55]

Hello

I am connecting to an SQL database via ODBC using Crystal 8 on a Windows XP computer.

The database is hospital/patient data and the report is to look at the province issuing health card field with the demographics fields i.e. postal code, residence code, RFP.

Because it is not advisable to include formulae in the selection criteria (because it isn't passed to SQL query anyway) I select on the patient type and period of time and then suppress the group to only show the erroneous abstracts.

My problem is that I'm not sure which is the most efficient way to go about this For instance is the manual running total way best? I think it is necessary to create a criterion formula for each.

Example
ON1 (initialize) placed in Group 1 Header
whileprintingrecords;
numbervar ONT;
ONT:=0;

ON2 (evaluate) placed in Group 2 Header
whileprintingrecords;
numbervar ONT;
If {Demographics.Prov HCNO} = "ON" and Not({Demographics.RFP} in ["01","02","07"]) then ONT:=ONT+1 else
If {Demographics.Prov HCNO} = "ON" and Not({Demographics.Res Code} in "0101" to "5042") then ONT:=ONT+1 else
If {Demographics.Prov HCNO} = "ON" and Not IsNull({Demographics.Postal}) and Not({Demographics.Postal}[1]
in ["K","L","M","N","P"]) then ONT:=ONT+1 else
If {Demographics.Prov HCNO} = "ON" and IsNull({Demographics.Postal}) and
{Demographics.Prov} <> "XX" then ONT:=ONT+1 else
If {Demographics.Prov HCNO} = "ON" and Not({Demographics.Prov} in ["ON"]) then ONT:=ONT+1 else
If {Demographics.Prov HCNO} = "ON" and Length({Demographics.HCNO})<>1 and
Not (Length({Demographics.HCNO}) in 10 to 12) then ONT:=ONT+1 else
If {Demographics.Prov HCNO} = "ON" and Length({Demographics.HCNO})= 1
and {Demographics.HCNO} = "1" then ONT:=ONT+1;

ON3 (Display)placed in Group 2 Footer
whileprintingrecords;
numbervar ONT;
ONT;

I suppress the information within the section by using formula ON3 = 0.

Should I be using the Initalize and Evaluate to include data from all provinces and then a formula for each for the Display portion of the running total? Is there another more efficient manner?

All help greatly appreciated.

Shelby

 

[Bobbber]

You should create an SQL Stored Procedure to handle this, and only give you back the information you want to see.

Bob

 

[shelby55]

Hi Bob

Creating a Stored Procedure isn't an option in this instance. So what is the second best thing to do?

Shelby

 

[lbass]

Shelby,

What would your selection formula look like if it were okay to include formulas? There are certainly some parts of the above formula that could be incorporated and pass to the SQL.

-LB

 

[shelby55]

Hi LB

I would create a formula like the one provided for EACH province and then in the select formula I'd indicate ONT or AB or BC etc

Thanks.

Shelb