-
Identify missing standard
Crystal documentation best practices (Report Title, Author,
Description special fields).
-
Locate reports which may use
the "Convert null field to default" setting
inconsistently.
-
Keep versions of your
documented reports for historical comparison.
-
Automatically detect reports that do not take advantage of
server side database processing.
-
Detect record selection formulas which use data type
conversion functions.
-
Detect group fields which use formulas instead of SQL
Expression fields.
-
Find report tables which use less than an optimal number of
fields.
-
Discover special fields which cause an additional pass over
the report data source.
And so much more! Click here
to see what you're missing by not having Report
Analyzer.
While originally developed for Crystal
Reports, Report
Analyzer became a far more powerful analysis tool with the Version 5
release.
Going back to our initial conversation, here's another SQL trick.
Use CR to generate SQL to change selected records. The database was wrong. We could see it had incorrect values for some of the records. We could write a Crystal Report
to find the errors and even to calculate the correct value to put back in the field. So why not use CR to generate the SQL
we needed for the update?
Use the error report as the foundation
for a new report. Remove all fields and create a new formula that looks
like:
“UPDATE myTable SET ThisField=’” + {@NewValue} + “’ WHERE RecordID=” + {Table.RecordID}
The report is now a long list of SQL
UPDATE commands. Export the Report to a Text File and then use the Text in
a SQL Query tool. This can update hundred of records with a few mouse
clicks.
You can also use SQL to change records.
A great feature introduced in Crystal Reports 9.0 is
the SQL command, which you can use to retrieve your data.
Sometimes we need to manipulate our data with
SQL prior to processing a report.
For example, we combine a SELECT DISTINCT,
UNION join and Sub SELECT to retrieve some data from our SQL server.
The SQL command can be added to the repository,
so you can share it between several reports. If you wish to change your SQL, you need to detach the
SQL from the repository, make your changes and then place it back in the repository.
The SQL Command feature is why Crystal Reports 9
does not include the Crystal SQL designer. You can do far more with Crystal Reports SQL commands.
SQL command parameters
Use SQL command parameters. Using SQL commands as the source of data for your
report has several benefits. This SQL command feature also includes the ability to include a parameter
in the SQL command.
For example, we have created a parameter {?Region} and use that to select a single region
from our table.
The SQL command now says Select * from Customer
where Region = '{?Region}'
The parameter editor in the SQL command dialog box
lets you enter only a single default value. So if you wish to load more default values, return to the Field Explorer and add
more default values to the parameter there.
This feature supports string, date and numeric parameters, but they must be a single value. Multi value parameters are not
supported.
The real benefit of this feature is where you need to extract a few records from a large table. Doing the selection in an
SQL command will have a major improvement in report performance.
|
