See also:

• Crystal Reports: SQL introduction

• Crystal Reports: Generate SQL to Change Selected Records in Crystal Reports

• Crystal Reports: SQL Commands in Crystal Reports

• Crystal Reports: Useful SQL Utility: Crystal SQL Designer

• Crystal Reports: SQL Left Join

• Crystal Reports: SQL Expressions and Sub Selects

Use SQL commands to create audit records of your reports.

SQL commands are a powerful feature, introduced in Crystal Reports 9. In other articles, we mentioned that you can use them with UNION joins, Sub-SELECT clauses, and Parameters. 

But, there is more power there than first appears. You can also use SQL commands to perform DELETEs and UPDATEs in your database. 

To create an audit record every time the report is processed, use an SQL command of:

INSERT INTO Audit (User, RunDate) VALUES (‘{?UserID}’, GETDATE())
SELECT * FROM Customers

If this raises any security concerns, use the Database security to restrict your Crystal reports designers to “read only” access and these SQL commands will not process. 

Now, you'll want to track and document what these SQL commands do. The perfect tool for that is:

You can more effectively analyze your Crystal Reports by using Report Analyzer: http://www.mindconnection.com/Merchant2/merchant.mvc? Screen=CTGY&Category_Code=CR-ANALYZE With this powerful tool, you can: Discover unused formulas, fields, and variables. Identify group settings which cause blank pages and footers.

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.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.