Three ways to put sub reports to work

Some imaginative ideas for boosting the power of Crystal Reports

By Jason Dove

I hate sub reports. For me, they're a last resort in most reporting situations. The easy ride they might give the report writer is just not worth the negative effect on performance and maintainability. Nine times out of ten, you can meet the requirements of a report by using a little forethought and planning - and a solid understanding of formulas - rather than a sub report.

That said, there are a few interesting ways in which sub reports can prove a boon to the developer without seriously affecting performance.

Standard Report Headers

Do you need to add certain information to a large number of reports? For example, you might need to incorporate a corporate graphic or logo, or use a special field (such as the date the report is run) as part of a standard header. This sort of information can be built into a sub report which can then be added to the main report.

In cases like this, the performance hit is minimal; you can shave a small amount off the development time; and it can go a long way to standardising your reports. But the real benefit comes when the business decides to update its logo or corporate styling, or change the standard information that is to appear in every report. As long as the sub report is set to "Re-import When Opening" (via the sub report's Format Editor), you only need to change one sub report in order to update your entire report library.

Reconciling Conflicting Groups

Often there's a requirement to show the same information summarised by logically conflicting groups. For example, you might need to show the total sales for each week within a month, and totals sales per team in a month.

A sub report can be used to load the data again, and then group it by the second value. This is the typical way to use a sub report. But accessing the database again for data you already have is a waste of resources, and this can be disastrous with large reports.

The most efficient way to handle this situation is to load the information you need into one or more arrays, and then pass these through to the sub reports to format and group as you want.

It's possible to display the array in the main report and forgo the need for a sub report, but, if you are reporting against a lot of data, there's a chance the report will finish before the array has been fully displayed. Using sub reports in the way described here neatly solves the problem.

Conditional Sets of Data

I come across this issue quite often: a report is needed which always shows the same set of data, plus one of two (or more) other sets depending on either the user's choice or the results returned from the first set of data.

Because a single report can only have one set of linked tables, you have to use multiple sub reports in this situation.

As an example, suppose a sales report shows revenue for a particular office. If the office has met its target, the managers want to see how they compare to the other offices. But if they fail to meet their target, they want to see the sales broken down by each rep to identify the problem areas.

A report based on sales reps and one based on offices require completely different tables. The most efficient way to solve this problem is to create a sub report for each. You conditionally suppress the one that's not needed, and run the other as normal.

It's true that sub reports can kill a report's performance, but, when used with a little imagination, they can be a helpful tool in expanding Crystal Reports' functionality in ways that cannot otherwise be easily realised.

About the author: Jason Dove is a senior consultant at Scry Business Intelligence and an instructor who has specialised in Crystal Reports and Business Intelligence his entire career, utilising it for everything from selling paint to counter-terrorism. He has provided Business Intelligence consultancy for some of the world's leading companies and is currently making the same service available to smaller businesses. He is also the author of Crystal Reports Formulas Explained, the most advanced book on the market which specialises in formulas. It's currently available with a free 70-page Crystal Reports XI tutorial. Expertise: Crystal Reports, Business Intelligence, SQL, ITIL. Email: Jason.Dove@scry-business-intelligence.com. Phone: 447779723043

Mike Lewis Consultants Ltd. April 2010.