SQL Query

[shelby55]

Hello

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

The question I have is about report optimization. The database I'm using is a hospital one and I create error specific reports (meaning that if the report is blank, there aren't any errors) with many criteria. I create formulae called criteria1, criteria 2 etc. (create as many as required for the report) and in the select statement include (@criteria1) or (criteria2) etc.

The statements may look like:

If {patserv} = "54" and {Sex} = "M" then true
If {entry} = "E" and {Disp} = "01" then true

When I look in the SQL query the parameters were passed and I get the records I'm looking for.

Another report writer in the office selects all criteria and then suppresses the detail and/or group sections to only show the erroneous reports.

Which method produces more optimal record selection? Thanks for any and all comments.

Shelby

 

[paulmarr]

Shelby,

I reckon your way is the best way - your colleague is drawing down every record and then performing the selection/suppression on their own PC - you are doing it at the database level - which should be much faster.

Cheers
paulmarr

 

[shelby55]

Thanks, Paul. My only concern is that I've read that for record selection optimization you shouldn't base your report on a formula but if I don't do that I can't include as many criteria as I do and I would have to have one report per criteria which isn't an option. I've also read that if Crystal isn't reading the formula correctly it just selects all records anyway and then does the sorting on the local computer and not the server.

Just wondering....