3 New Uses For Subreports
by Jason Dove,
I hate subreports and always consider them the last
resort in any reporting solution.
The negative effect on performance and
maintainability is just not worth the easy ride they give the report
writer. Nine times out of ten reporting requirements can be met using a
little forethought and planning (and a solid understanding of formulas).
That said, there are a few novel ways of using subreports which will
not affect performance and actually prove a boon to the developer.
1. Report Header
Any information, graphics, logos or special fields (Date report was run
etc) which will appear in every report can be built into a sub report
which is then added to the main report.
The performance hit is minimal, and a small amount is shaved off the
development time, plus, it can go a long way to standardizing your
reports. But the real benefit comes when the business decides to update
its logo or corporate color etc. As long as the sub report is set to
"Re-import When Opening" (via the sub report's Format Editor), only one
sub report needs to be changed to impact across the entire report
2. Reconciling Conflicting Groups
Often there is a requirement to show the same information summarised by
logically conflicting groups. For example: showing the total sales for
each week within a month and totals sales per team in a month.
A typical sub report can be used to load the data again then group it by
the second value, and this is the typical way to use a sub report. But
accessing the database again for data you have is a waste of resources
which can be crippling with bigger reports.
The most efficient way to handle this is to load the information you
want into one or more arrays and pass them through to the sub report to
format and group as you want.
It is possible to display the array in the main report and forgo the
need for a sub report at all, but if you are reporting against a lot of
data there is a chance the report will finish before the array has been
3. Conditional Data Targets
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 of data
depending on 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, multiple
subreports must be used.
For example: 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 rest of the other offices, but if they fail to meet their target
they want to see the sales broken down by each rep to identify any
A report based on sales reps and one based on nation office sales
require completely different tables. The most efficient way to solve
this problem is to create a sub report for each. Whichever is not needed
is suppressed and given Record Selection criteria which will return an
empty report. The required sub report runs as normal.
Subreports 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 a way that cannot be realized by any other method.
Jason Dove is a top Business Intelligence consultant and author who has
shared his expertise with some of the most respected businesses in the
world. Crystal Reports is his reporting software of choice and the subject
of his book "Crystal Reports Formulas Explained" which is the most advanced
book on the market. Currently available with a free 70 page Crystal Reports
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.