Published:
11 May 2010
|
Abstract
I hate sub reports 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).
With that said, there are a few novel ways of using sub reports which will not affect performance and actually prove a boon to the developer. |
 |
by Jason Dove
Feedback
|
Average Rating: 
Views (Total / Last 10 Days):
21597/
40
|
|
|
|
| Introduction |
I hate sub reports 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 sub reports which will not affect performance and actually prove a boon
to the developer.
|
| 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
standardising 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 library.
|
| 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 fully displayed.
|
| 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 sub reports 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
problem areas.
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.
|
| Summary |
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 a way that cannot be realised by
any other method.
|
|
|
|
User Comments
Title:
RE: But Crystal could do this better
Name:
Ray S.
Date:
2010-11-23 11:39:27 AM
Comment:
To: Bill Geake
I'm sure others will weigh on the capability of passing
parms from Main report to subreports based on SQL commands.
Here's what I do:
1) In the subreport, when you are setting up the command
in the "Modify Command" window, create a parameter,
that has the same type as the calling parameter in the
Main report.
2) From the Main report, right click on the subreport
and select "Change subreport links...". Then link
the Main report's parameter to the subreport's
parameter.
Is this what you had intended to do? If not, sorry for
confusing the issue.
RS
|
Title:
My Thoughts
Name:
PA
Date:
2010-11-23 5:37:51 AM
Comment:
Hi All,
With due regards and respect to the ideas that have been discussed, I failed to understand the "hatred" factor and the "3 New Uses For Sub Reports" concept.
Reasons:
1. Report Header:
This is generally achieved with report templates
2. Reconciling Conflicting Groups:
A single report cannot contain grouping based on multiple logics and combinations , so this is the obvious use of a sub report. I failed to understand, what's new?
3.Conditional Data Targets:
As an alternative, this could be achieved by providing hyperlinks to individual reports by enabling the hyperlink(s) based on condition(s)
Your comments are appreciated.
PA
|
Title:
But Crystal could do this better
Name:
Bill Geake
Date:
2010-11-23 4:38:36 AM
Comment:
If only there could be a way of making it easy to pass values from the main report through to the sub report SQL. Jason hates sub reports; I hate selection formulae, and these hatreds are connected. The performance hit on sub reports isn't just the extra query per sub report instance - it's also the failure to filter data on the server and the increased network traffic that results.
Perhaps it's just my ignorance, but I've never been able to pass parameters to Oracle SQL in sub reports. If SAP could sort this out with an intuitive GUI it would make rich reports so much easier.
|
Title:
Mr
Name:
David WHite
Date:
2010-11-23 3:55:37 AM
Comment:
Amazing! They say the best ideas are really simple. I would never have thought of holding common components in a subreport. What a brilliant idea!
|
Title:
Mrs.
Name:
Preetha Raju
Date:
2010-11-22 9:25:33 PM
Comment:
Really, very useful tips. Specially header concept with the sub report, which I never thought.
Thank you very much, looking forward for more tips from you.
|
|
Product Spotlight
|
|
|
