See also:

• Crystal Reports: SQL Commands (2002)
• Crystal Reports: SQL Designer (2001)
• Crystal Reports: SQL Expressions and Subselects (2007)
• Crystal Reports: SQL For Extracting the Date Value in Crystal Reports (2010)
• Crystal Reports: SQL Generation to change selected records (2003)
• Crystal Reports: SQL Introduction (2001)
• Crystal Reports: SQL Left Join (2004)

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.crystalkeen.com/crystalreporttools/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.