Reports taking long to load

Recently we had a problem with reports taking a long time to load. After much investingation by the Server Administrators we discoved that it was because of the subreports, specifically subreports that were imported. What had happened was that a new server was setup and all the report files were copied accross and the old server was removed. When the report was loaded it was looking to reimport the subreport, but because the original location did not exist any more, it took about 20 seconds before timing out. What we found was that there is an option under subreport format on the subreport tab to re-import the subreport when opening. By unchecking this option the problem was solved.

Aesthetically pleasing reports

An important part of developing reports is to make them pleasant to look at. I have seen many reports that are bland and look like a real mess. There are some things that I do to make my reports aesthetically pleasing. Headings are always in bold. I usually make the heading group with a silver background as this makes the headings stand out. Columns should always be alighned to make them look neat. Row text should be alighed and should be the same size and font. There is nothing worse than looking at a report with text all over the place.

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.