We all know what a report is!! Now the question comes what a subreport is!!!! The word "sub" defines the answer itself. Sub means to act as a substitute for another i.e. a report which works as a substitute of another report.
Why to use subreports?
Again the word "sub", defines the answer. A subreport is preferred to be used when a bigger amount of data is needed to be shown that too using may be more that one data sources. So basically for presenting data in an arranged manner we use the subreports. A subreport can be inserted by clicking on Insert -> Subreport.
Here we can choose an existing report that has already been created earlier and insert that as a report object. Or we can create a new report with the report wizard which is much similar to the Standard Report wizard. (There is an option to make the subreport an On-demand subreport that would be discussed in the Linking Concepts).
Types of Subreports
Precisely there are two types of subreports
- Linked subreport
- Unlinked subreport
When we need to show different sets of unrelated data, we use unlinked subreport which is nothing but an individual report shown as a report object. A report can have a number of subreports as per requirement. Each subreport has its own set of properties.
Now we get to learn another term that is "Link".
But Why Link?
Linking subreport means to relate the dataset of the subreport to the dataset of the main report in such a way so that the final output data set would be the combination of dataset from the two reports based on some defined criterions.
The subreport and the main report are typically related by the nature of their content.
There are basically two techniques to link the subreport with the main report:
- Linking an existing subreport to a main report
- Creating a new subreport ad hoc and linking it to the main report
The most famous way of linking is Binding Subreports to Main Reports with Parameters. Generally, we can link a subreport to a main report by passing a value from the query in the main report as a parameter to the subreport; this value filters data in the subreport query. We can link the reports through the Subreport Parameters property of the subreport component.
To link the subreport (Right click on Subreport -> Change subreport links).
The Subreport Parameters property is an expression usually mapped to a field (here it is a parameter) in the main report.
Linking a Report with an Existing Subreport
We can nest an existing report as a subreport inside another report. We just need to click Insert -> Subreport. In the Insert Subreport Dialog box, we select the option for Choose an existing report. This is much like inserting a picture file in to a Word document (Note: other than the path selection, there is nothing else to match out, as the subreport can be linked and the data can be coordinated, which is not a feature of Word). Similar way we give the path of the existing report by clicking on Browse in this particular option.
How Linking Works:
When we talk about Linking, there are three important components to discuss:
- SQL generated by CR
- The Record selection
- Passes
The basic concept behind linking is that the Crystal Reports uses the link to match up records from the primary report to those in the subreport. Linking ensures that data in the subreport is only according to the corresponding data set of the primary report.
Now we discuss the components as below:
SQL generated by Crystal & The Record Selection Formula
In the Link dialog box, when a field is selected from the main report to link to the subreport, Crystal automatically generates a parameter for the subreport. This parameter is used in the record selection formula of the subreport automatically.
Crystal retrieves the data by passing an SQL statement to the database for the primary report. We can see this Crystal generated SQL query by clicking on Database -> Show SQL Query.
Crystal generates and passes the query for subreport again according to the defined parameters. If we see the SQL query, we would find the parameters are defined as Where clause in the query.
Passes
The most important factor that should be kept in mind while inserting and linking a subreport is the Passes.
There are several passes used to execute a report.
Subreport gets executed at Pass 2, so it’s very important while linking the subreport using shared variable, that we get the shared variable executed before the subreport is called.
Subreports data may appear incorrect if the main report has a group selection formula. As the grand totals and summaries are calculated during pass 1 but the group selection formula filters the data again in Pass 2.
On-Demand Subreport
Subreports get executed on Pass 2 that means our primary report becomes a 2-pass report if we insert a subreport in it. However we can insert a subreport and still keep our main report as a Single Pass by making the subreports On-Demand.
With On-Demand subreports, Crystal makes a second pass however only when we drill down on the subreport. This increases the performance quantitatively.
