Monday, February 25, 2008

To Subreport or Not to Subreport (Crystal Report)

I don't know whether there is a general rule about when to use subreports. Many times you will see people recommend against the use of subreport because they slow reports, and in general, they are right, but there are some cases when using a subreport actually speeds up reports. Some cases when subreports make sense:

1-To avoid a geometric increase in records due to joining multiple tables that have one to many or many to many relationships. If you have two tables joined by an ID field and there are two or more records in the first for each ID, then if you join it to a table with two or more records per ID, the records from the second table would repeat for each record in the first table. Instead you can use a subreport for the second table, place it in a group header for a group based on ID, link the subreport to the main report by ID, and the display will be limited to one set of records for the second table.

2-To show a limited set of information in the report header. For example, to show description fields for a parameter based on a numerical value.

3-To link data from two different datasources that otherwise cannot be linked, because CR doesn't support the the use of multiple datasources in the visual linking expert, e.g., data from an Excel database and from an Oracle database.

4-To link data from two tables where one field is of a different datatype or length and therefore must be converted by formula in order to be linked to a second table. Again, you can't do this in the visual linking expert, but you can link a formula and a field in the subreport links.

5-Subreports also can help in complex reporting situations--
charting on print-time formulas, creating ranks of data without sorting the displayed data, grouping on the number of records on a page, limiting the vertical display of multiple column data, etc.

6-To display a summary report that displays summary data from many different analyses based on different datasets or based on different selection criteria.

7-On-demand subreports can be used to display detail on an as-needed basis.

8-To display data from two tables where there are no fields that can be linked.

9-To allow the sequential display of all records from one table, followed by all records from another.

10-To create multiple copies of the same data, e.g., on different pages.

11-To allow drill-down on each of several values in the same row.

This is all I can think of for now. Subreports are sometimes very useful, and even essential in many situations. Besides the (usually) slower speed caused by subreports, another downside is that to use subreport data in calculations in the main report, you have to use shared variables, which can become quite complex.


Anonymous said...


Read urs article . But i have a query using subreports . Let me explain the scenario,
I have main report and 5 different subreports attached. Now out of 5 reports 4 reports are directly invoked in the main report, but theres a case where in 5th sub report is invoked in the 3rd sub report and then further 3rd sub report is invoked in the main report. Here I Have an issue,I can see the data from the 3rd sub report in the main query but it doesnot display the data from 5th sub report which is a subreport to 3rd subreport. But If I refresh 3rd subreport seprately it dispalys the data from the 5th subreport.
So pls advice how to invoke subreport within a subreport

Brij Mohan said...

From your description what I feel the problem is that your 5th subreport is not linked to main report,

Lets take an example:

You have a table called employee, Employeedetails and employee address

Now your main subreport is linked to employee.

3rd Subreport is linked to employeedetails

And 5th Subreport is linked to employeeaddress

Now in main table employee you have employee id 1,2,3,4,5,6

In employee details you have entry of employee id 2,3,4,5,6,7,8,9 only and in your employee address which 5th subreport is linked has entry for employeeid 7,8,9,10

So you can see here your 5th subreport data is nowhere linked to main report data, but your 3rd subreport data has got some empid's common so its displaying the data from 3rd subreport.

So when you load main report, common data(empid 2,3,4,5,6) of main report and 3rd report is displayed, and when you open your 3rd report, common data(7,8,9) between 3rd report and 5th report is displayed, but when you open main subreport there is no data common between main report and 5th report so nothing is displayed in 5th subreport.

I hope this will help you, if not then do let me know, and give some more inputs like how these reports are linked to tables and database.

Brij Mohan

Unknown said...

I know there is currently NO information on the internet about this that I could find, but I did some playing around with subreports in Crystal Reports because they are SO INCREDIBLY SLOW that it was making our applicaiton unusable. The call that was killing us specifically was

tbl.Location = tempReportFilename;

Calling this line for each table in the subreport was costing 200ms per table at a rate of 5 subreports per report and 5 tables per subreport, that is an incredible amount of time to sit and wait for a report to load.

Here is the kicker though... Don't call that line of code. As long as all of your linked subreports have the EXACT same data mapping as the main report, you don't have to call .Location on each table in your subreport. Now keep in mind it has to be EXACT. This saved in our case almost 3000ms and now other then memory leaks (which have always existed in Crystal), Crystal Reports is now usable again.


Anonymous said...

Any Ideas on linking sub report in another sub report using crystal XI?????

Kirk Teutschbein ( said...

Hi Brij. Thanks for this article. I am hoping you can help me based on your points 9 and/or 10.

I have a report for spectrographic data with 2 main subgroups: details of each element (call it "E"), and identification data for the sample ("I"). My report now prints E for sample #1, then I for sample 1, then E for sample 2, etc.

What I am trying to do is get all of the E for samples 101 through 105 (or what ever is selected), then list all of the I for the same samples.

If I use a subreport, I get a subreport for E for #1, then a subreport for I for #1, then E#2, then I#2 etc. I can't figure out how to run through all of the data twice; it wants to push everything just once. I've tried moving the second subreport to the footer, but then I only get info on the last sample. Do I need to create a new link to the same table? If so, how is this accomplished? Also, will the user have to input the data point selection twice?

Thanks in advance for your help.

Anonymous said...

Can you please suggest me the best way to represent the data I have?
I have to display scores for the game. There are six different sports with different scoring patterns.
For now I have created single stored procedure which gives dynamic scores based on sport.I have created one crystal report , underneath that report I have created 6 different sub-reports.
Please tell me should I continue making sub-reports Or should I go for creating 6 different reports?

Post a Comment