Crystal Report Version Control

An important process of software development is to have version control. There are many software applications around that can handle this for you. Control is maintained by checking a file out, making a change and checking the file back in.

If you do not have version control software application, you can maintain it manually although it not as secure. What you can do is create a formula field in crystal and call it something like 'Report Version'. In this formula field store the version number, the person who modified the report and the change that was made. Another good idea is to store the name of the person who requested the change and reference numbers if you have a job logging application.

It is also useful to get you software application that runs the report to show the version number by retrieving it from the formula field. This way a user knows which version of the report thay will be running. Also, printing the version number on the report is helpful.

Storing old versions of the report can easily be done by copying the report file to and archive directory.

Optimisation of SQL Query

One of the biggest issues facing a Crystal Reports developed is the performance of the report. Many times I've heard users complain that a report takes too long to generate. A good approach to take to optimise performance is to analyse the SQL Query. In Crystal Reports you can view the SQL Query by going to the Database menu and selecting 'Show SQL Query'. You can extract this query and run it through a query analyser.

For Oracle databases you can use Toad to 'explain the plan'. This can tell you what indexes are being used or if the query does a full table scan to get the required data. Always try to make use of the indexes. Sometimes it may be worthwhile creating new indexes on the tables. In some cases you can add additional conditions to the selection formula record which will cause the database optimiser to select a different set of indexes.

At times there may be nothing more you can do so you should speak to your database administrator to see if they can improve the performance on the database side. There was a case where a report took a long time to run because it was not selecting best index. This was because the sample size was too small. The database adminstrator has set a sample size of 2000 records and this was used to specify which index to use for a table of 6 million records.

An alternative to Outer Joins

In Crystal Reports there is the option to link tables using outer joins. This allows you to show data from one table even though there is no corresponding data in another table. On large complex databases this can somtimes cause performance issues because a full table scan will be performed on the outer join table. This can be overcome by creating a view that always has a corresponding record and the outer join table is replaced by a field or fields that return null if there is no corresponding report.

An example would be:
create or replace view outer_join_vw (field1, field2) as
select field1, (select field2 from tableB B where A.field1 = B.field1)
from tableA A

Performance affected by Deep Linking

We had a Crystal Report that on average took 10 minutes to run. A job was logged that the report was taking a very long time to run when a certain set of criteria was specified. On running this report with this criteria, the report had not completed generating after 2 hours. So on analysing the report I discoved it had 'deep links'.

What I mean by deep links is that table A links to table B which then links to table C which then links to table D and so on. What I did was change the linking structure so that table A links to table C and table A also links to Table D, thus going from 4 levels of linking to 2 levels. By removing this 'deep linking' the time taken to generate the reports was now 5 minutes irrespective of the criteria specified.

Exporting report with subreports

A project I worked on was to combine several compex reports into one report using Crystal 8.5. This was accomplished by adding each of the seperate reports as a subreport. Each of the subreports would be executed if they met a certain criteria and this was achieved by using suppression. They main aim of the consolidated report was to have it exported to excel. But when exporting the report it would take an extremely long time. The cause of the problem was that when exporting Crystal would still executed the subreport even though it was being suppressed. The work around was to create each row as a single text containing all the fields seperated by a comma. The report was then exported as PDF and we then used a PDF to text converter to create a text file. This file could then be imported into Excel. Hopefully this Excel exporting problem has been fixed in later versions of Crystal Reports.

Barcode Fonts in PDF

As part of the invoicing requirements I had to add Barcodes to the reports. We obtained the Barcode font as well as a User Function Library which would encode the font. A problem we faced when emailing the invoices as PDF to the client was that the barcode did not work. This was because we were using Crystal 8.5 and the barcode font has to exist on the clients machine for it to come out. The best way to solve this problem is to upgrade to a later version on Crystal because the later versions can embed the font in the PDF.

Another problem we encountered with the barcode font was that when exporting to PDF the barcode would shrink to half its original size. To get around this we had to create the font double the size that it should be then when it was exported to PDF it would shrink to the correct size.