Sub-Report links and formatting behaviors

[Former Member]

Purpose

To understand the difference between the placement of Subreport and ways it can be manipulated for achieving desired result set

Overview

Sub Report has its own beauty of linking to the main report and extracting the relevant data from the database for the reporting purpose. This behavior changes with the different approaches which are been adopted to link the Sub Report to the main report

Placement of the Subreport

If you have an existing report based on a particular data source and you require populating the same report with the result set which is been fetched from a different data source. This could be achieved using a sub
report in the main report.

If you place the sub report in Report Header of the main report and right click on it and change the links, you would observe that once you select the field to link from the main report a parameter is automatically created in the sub report to link to the main report field.

One more option you can look at in the same link window is ‘select data in sub report based on field’. This particular check box is checked by default. If this checkbox is unchecked then the parameter won’t be linked to the field of the sub report

With such links if you refresh the report you would observe that the sub report will be populated with only one record and that will be the first record of the main report.This is due to the links between the sub report and main report, where the parameter in sub report is going to fetch the value from main report. And that’s the reason why during runtime only one particular value is passed to the parameter and it fetches the relevant data and populates in the sub report.

In similar fashion if you place the sub report in the Report Footer the data populated in the sub report will be based on the last record of the main report.

If you run the sub report individually you will understand by taking a look at the SQL query which is generated in Crystal Reports. Following is the example of the query of one of test sub report based on the Xtreme sample database

Example1:

SELECT `Customer`.`Customer ID`, `Customer`.`Region`
FROM   `Customer` `Customer` WHERE  `Customer`.`Customer ID`=4

In this query a ‘where’ clause is created due to the link setup between the Main report and the subreport.

If you uncheck the box ‘select data in sub report based on field’, the value from the main report won’t be linked to a field in the sub
report and the sub report will populate the results depending upon the Record Selection formula it has.

Linking the Main report with the Subreport(consisting parameters)

Now if the sub report consists of its own parameters/record selection for the report then the query generated in the sub report will differ.This query will consist of the linked parameter and the record selection of the sub report in where clause.

In this case the result set populated in the sub report will be dependent on the two conditions in the where clause.

Example 2: if I have a record selection in the main report to show the data above $1000 salary, and in the sub report I have the record selection where the condition is to fetch the data above Id number 10. Now if the first record in the main report is

ID-5 with salary $1500 then in this case if the sub report is placed in the report header it won’t fetch any data as the sub report consists of one more record selection apart from the link parameter.

The links in the sub report to main report would make sense if you place the sub report in the details section. Where in the sub report will be executed for each and every record and will fetch the relevant data for
each row.

In case of example2 the row which would not return the true value for the query it won’t display any data for that particular row i.e. if in main report the id populated in the id column is 7 with salary above $1000 then the record selection formula generated in the sub report will be

{Customer.Salary} = {?Pm-Customer.Salary} and {Customer.Customer ID} > 10

And query will be

SELECT `Customer`.`Customer ID`, `Customer`.`Region`,`Customer`.`Customer Name`

FROM   `Customer` `Customer`

WHERE  `Customer`.`Salary`>$1000 AND
`Customer`.`Customer ID`>10

The above given query is self explanatory that to fetch the data both the conditions in the query should return true value, because by default crystal places AND clause between the multiple condition and in case of AND clause both the conditions should be true.

However, it should be noted that placing a Subreport in the Details section is not advised because there will be significant performance issues with the report. For every row in the details section a separate query will be sent to the database to get results for the subreport.

So, just imagine a report with over 1000 rows and a linked subreport in the details section!

One additional scenario: For Example I have a Report where in detail section I have these three columns

Col1

Col2

Col3

Now the subreport is placed in the detail section and its linked to Col2

This Col2 has duplicate values, So when the duplicate values will be passed to the subreport it will reproduce the similar duplication. There are lot of chances of the data being repeated at the granular level and it could vary for different business logic implementations.