Ensure you have fully understood the report requirement and identified the correct data source, data source connection, table joins (if you need to use tables in the report), fields, formulas and formatting the report requires.
2. Use the most appropriate Data Source
If the report can be designed using a stored procedure as the data source then this will be the most efficient method. If there is no stored procedure, then using a database view or SQL Command will ensure the report runs as efficiently as possible.
3. Database Optimisation
If the report requires tables from a database, ensure that the fields you will use for linking are indexed. Typically if you link tables on un-indexed fields and then run the report, you may see the ‘Accessing Database’ message at the bottom left of the screen. This means that the SQL query is processing on the database server before returning records to the client computer.
If you have no choice but to link on an un-indexed field, then there are two approaches you can take. Firstly, contact the database administrator to ask if the un-indexed field can be indexed. Secondly, if one of the fields is indexed, then Linking TO the table with the indexed field is more efficient.
4. Record Select Expert
Try not to use Crystal formulas in the Record Select Expert if possible. Crystal formulas are processed on the client computer. For example, your report may return one million rows of data, if your record selection relies entirely on Crystal formulas, then your record selection formula will evaluate one million times. Try to use database fields and parameter fields in your record selection process.
5. Use of Crystal Formulas
Formulas are a necessary part of many Crystal Reports. However, if a formula can be reproduced in SQL, the best practice is to use an SQL Expression. This will achieve exactly the same result as the formula, but will be processed on the database server, not the client computer.
6. Sub-Reports
Occasionally you may need to use a sub-report or multiple sub-reports in your Crystal Report. Wherever possible, try not to place these objects inside the Detail section of your main report. Each time a row is read in the main report, the entire sub-report is processed.
7. Report Performance
If the ‘Accessing Database’ message appears very briefly in the bottom left hand corner of the screen, but the report still takes a considerable time to run then check the Database|Show SQL Query menu option and pay particular attention to the WHERE clause. This should closely, if not exactly, match the record selection set up in the report.
8. Return Summary Information Only
If your report has a requirement where only summary information needs to be viewed then you can utilise the ‘Perform Grouping on Server’ feature. This will return one row per group and leave the detail information on the database server. This means the report will run much faster as fewer rows are sent to the client computer.
You will need to ensure that you have at least one group with a sub-total. That you have hidden the Detail section and that you have turned on the ‘Perform Grouping on Server’ feature from the Database menu.
This feature will not work if you are using a Distinct Count or Average summary type, or if you have any formulas visible on the report.
9. Ensure the figures are correct before releasing the report
This is common sense really, but if you have a known set of trusted data to work with and compare the results of your report against, then this will ensure the information will be trusted.
10. Learn SQL
If you have been designing reports for a while, but never ventured into SQL, then it is worth learning the basics as this will improve your report design skills immensely.
Written by: Steve Chapman, Senior BI Consultant, DSCallards
For more information, visit www.crystaltraining.co.uk.
