Crystal Sub Reports

Description

It is possible to use SubReports within a single ICXE Report File – this provides a means of creating Dash Board Style report display. Each section of the Dash Board is a unique Report with its own data query.

Each Data Query is linked to the master report via a series of PARAMETERS. The subreport does not use the RECORDSELECTION of the master report, instead you need to bind fields from the master report to each subreport.

The following support note demonstrates how to create a master report containing two subreports. Note there is no limit on the number of subreports that can be contained within a master report – it is more dependent on readability and execution time as each subreport operates independently and the database queried by each inturn.

Detail Steps

Important Information

To create a report that will contain subreports it is critical to understand what PARAMETERS are set by the ICXE in the master report. Any field (database or parameter) can be LINKED to a subreport but it is recommended that PARAMETERS are used where possible. Note also Inventory Controller XE sets FORMULAS as PARAM_XXXX where XXXX is the variable name. Crystal Reports uses SPECIAL PARAMETER fields (not formulas) to link to subreports. To clarify, the ICXE sets FORMULAS as PARAMETERS from the program to the master report while Crystal sets PARAMETERS to use from the master report to the subreport.

Creating a Master Report

The first step in creating a Dash Board report is to open a new master report. For this example the Managers Summary is used to illustrate the process of creating the Dash Board.

Create a new report and link to a key table; note it is not essential to link to tables used by the subreports as each subreport has its own ERD. The ERD of the Master Report is used in the case where a Record Selection formula is being passed to limit the number of detail records the master report will process.

In this example the Managers Summary could use Sales and the PickList table joined as shown.

With reference to the ICXE Formula Parameters – create matching PARAM that link to the ICXE filter dialog, in the case of the Managers Summary a LocationID and ReportDate are passed into the master report.

      objReportFilter.Insert "RecordSelectFilter", psSelection
      objReportFilter.Insert "PARAM_Location", cboLocations.LocationID
      objReportFilter.Insert "PARAM_ReportDate", DateToLong(dtToday.value)

It is recommended that a default value is placed in the formula editor as this sets the DATA TYPE – otherwise the PARAM Formula defaults to a STRING – causing difficulty when trying to create filters or other formulas that use the PARAMETER. An example is shown below for location – also set DEFAULT VALUES FOR NULLS.

At this point you can drag a few fields onto the DETAILS area of the Master Report to test that it connects correctly with the ICXE report dialog. Once connection and PARAMETER links are verified a subreport can be inserted.

Creating a SubReport

SubReports are a special case of Report for Crystal – based on this review a subreport could not easily be used as a standalone report and needs to be created based on links from a master report.

In this example the subreport will simply be a list of Invoices entered on the date and location selected from the master report.

Create a new report adding in the tables required to complete the necessary ER and tables. Do not attempt to apply any filtering as this can only occur once your SubReport has been INSERTED into the master report.

It is again recommended that the report is verified against the database – using the first 100 records to check all relationships and formulas function correctly.

Open the master report – choose Insert from the Menu and SubReport – then choose the SubReport and click OK.

The Report can be inserted in any section of the Master Report – for this example we use the Header.

Now Right-Click on the SubReport field and select CHANGE SUBREPORT LINKS.

 
Select the Report FORMULA PARAM Fields from the Master Report and click the > to indicate this field is required to link to the SubReport. Notice that the Master Report field is created as a PARAMETER field in the SubReport with a new name shown in the bottom left of the Links Dialog. In this example the PARAM_Location will be created in the SubReport as Pm-@PARAM_Location.

By checking the option to SELECT DATA IN SUBREPORT BASED ON FIELD, you can force the PARAM to be the record selection criteria. An example use for this option is the MANIFEST where the PKL_PICKLISTID is the only parameter passed and is used to select a single PICK LIST, PACK LIST and SALE ORDER.

Unless the data query is very specific we are recommending the use of the PARAMETER method described above so this option should be UNCHECKED.

Click OK to return to the Master Report and right click on the SubReport field and choose EDIT SUBREPORT. Note the subreport is shown as a SUB TAB next to the design tab.

The next step is to configure the new PARAMETERS with the correct data type. Right click on each parameter and set the type as shown below.

At this point the Master and SubReport are now bound together – it is possible to test the report to ensure both Master and SubReport return records from the Database as shown below.

Setting SubReport Filters

The next step is to use the SubReport PARAMETERS to limit the records – you can also verify the PARAMETERS are being correctly initialised by adding them to the subreport while still refining the overall design.

In this case ALL LOCATION is selected and the current date:

In order to apply a filter either a RECORD SELECTION FORMULA can be applied or a SECTION SUPPRESS filter can be used. It is recommended that to limit the total number of records query from the DATABASE a combination of both Record Selection and Suppression be used. Make sure your Record Selection is made while EDITING THE SUBREPORT and not the master report.

For example a Record Selection formula could be applied based on the date as per this example. Firstly, choose Report from the main menu, then Selection Formulas, then RECORD.

From the Report Fields enter the expression to filter Database records on… for example…

Save the report set – saving the SubReport will save any Master Report changes.

Run the report and note the Record Selection filter in operation as shown above.

To add a second SubReport follow the same process as above; in this example the PickList Table is used to show information regarding order Sales By State.

Link the PARAM_Location and PARAM_ReportDate fields. Use the Parameter fields for section suppression as required.

Run the Master Report – and both SubReports are rendered as shown below.

Notes:

Once a SubReport is embedded within a Master Report changes made to the SubReports DONOT update the original subreport RPT file. This also goes for changes made to the external RPT file. For example if a new field was added to FRM_ManagerSR1.RPT it would not update the SubReport.

SubReports are effectively IMPORTED as a SNAPSHOT of the RTP file at the time of import – and changes made to the SubReport within the Master Report stay within the master report. To keep a “backup” of the subreport any changes made within the Master Report also need to be made to the external RPT file.

To recreate a Master Report all the subreports need to be REIMPORTED and RELINKED. An option exists in the SubReport menu as shown below to re-import a SubReport that has been changed externally.

Related Information

None