Sometimes report designers face report design needs like:
- Data for report coming from two data sources
- Data presentation needs like chart in detail section
- A part of report uses SQL and another uses XML
- Master – Detail type of report having fields positioning constraints
Use sub-report component of Studio to handle such needs. For a sub-report component:
- Newly designed report, or
- A pre-designed report can be used. A pre-designed sub-report can be:
- Embedded in the parent report
- Referenced from reports deployed in repository
Data for sub-report
A sub report can use:
- XML source as well as SQL source
- Data from parent report’s connection, a separate connection or Intellicus server’s default connection
Creating a Sub Report
To create a sub report:
- From Insert menu, click the option Sub report. The cursor changes to a cross (+) symbol.
- Click the layout pane and drag your mouse to mark an area.
Figure 1: Creating Sub Report
To edit the sub report, from Edit menu click the Sub Report option. This will invoke a window for designing a sub report. All the designing methods discussed in the Layout Editor are available to the sub reports.
Using a pre-designed report as a Sub Report
Embedding an un-deployed report
You can embed a pre-designed sub-report in the main report. To embed a report,
- Create a sub-report holder in a report or a sub report.
- Right click on the sub report holder and select the Edit SubReport option.
Figure 2: Edit Sub Report
This displays the Create a Sub-Report from dialog box.
Figure 3: Create a Sub-Report from Dialog Box
- Select the Existing Report
- Click the Browse option from the list.
- Click OK.
- In the Open dialog box, select the parent report from which you want to insert the sub report.
- Click Open. The following dialog box is displayed.
Figure 4: Insert Sub Report from Existing Report
- Select the sub report to be included and click Open.
The sub report is embedded in the current report.
Note: To embed a deployed sub-report, download it from the server, save it locally and embed it in your main report.
When a sub-report is referenced, its name and category details are placed in parent report. Actual report is referred at run time.
Tip: Use Referenced report when a sub-report may be used in multiple parent reports. Also, it is a good idea to use referenced report when a sub-report may undergo frequent changes.
- Create a sub report holder in a report or a sub report.
- In SubReportType property, select Referenced.
- In Source property, select the report.
Value in CategoryName and ReferencedReportname property will auto-appear based on value in Source property.
Converting an embedded sub-report to referenced
When an embedded sub-report is changed to referenced, Intellicus Studio will upload that report on Intellicus server in user specified folder and mark it as referenced.
- Select the sub-report component.
- In SubReportType property, select Referenced. Click Yes on Confirm convert dialog box.
- In Upload to Server dialog box, specify category (folder) and report name.
- Click Upload.
Sub-report type is changed to referenced.
Creating a new report to be used as sub-report
- Create a sub-report holder in a report or a sub report.
- Right click on the sub report holder and select the Edit SubReport
- In Create a new Sub-report from dialog box, select New Report and click Ok.
A new window will open to create a new sub-report. Create the report. Click Save button to save the work. Click Close button to return to main report.
Note: A sub-report may need to use parameters of main report. If that is the case, create a new sub-report using wizard. This way you can use parameters of main report in sub-report also.
Accessing Parent report’s data from sub-report
You can access parent report’s data from within sub-report. Values of parent report’s fields are passed to sub-report as parameters in sub-report. These parameters are of the same name as field name (or alias if alias is used) in parent report. For example, value of parent report’s field “customerid” is available in sub report in the parameter named “customerid”.
When you create an embedded sub report, Intellicus automatically creates these parameters. When you use a referenced sub report, you need to make sure you have manually made those parameters in the sub report.
This parameter can be used in sub report as it is, as well as in SQL as filter.
SQL in the parent report: SELECT ORDERS.ORDER_NO, ORDERS.ORDER_DT FROM ORDERS
Parameters in sub-report: ORDER_NO, ORDER_DT
SQL in sub-report: SELECT ORDRDETL.ORDER_NO, ORDRDETL.PROD_NO, ORDRDETL.PRICE, ORDRDETL.QTY FROM ORDRDETL WHERE ORDRDETL.ORDER_NO = <%ORDER_NO%>
Figure 5: Report Layout of the sample report
Figure 6: A sample report preview displaying data from parent report and sub-report SQLs
Important: Before using a numeric parameter as filter in SQL, make sure default value has been set for the parameter. When a numeric parameter without a default value is used in SQL it will result in SQL Error.
If parent report has more than one fields with same name, for example, product.prod_no and invoice.prod_no, then use alias in parent report’s SQL, for example, prod.prod_no AS PprodNo and invoice.prod_no AS InvProdNo. In sub report, Intellicus will make parameter with alias – PprodNo and InvProdNo.
Using Stored Procedures with Sub Report
The method of using stored procedures in sub reports is the same as used in parent report.
Fetching Sub Report Data from Other Databases
To generate sub reports, you can either use the database connection of the parent report, or use another (configured) connection.
To do this, from the Tools menu click Report Properties option from the sub report layout window.
This would invoke a dialog box, as shown below. This dialog box is resizable.
Figure 7: Tick indicates that the connection status as active.
The database connections mentioned here are configured connections in the report engine. These connections were defined while configuring the Intellicus Report Engine.
Important: To let the sub reports use the parent report’s database connection, you need to select for all sub reports check box from the database connection window.
Selecting the Source Connection
From the Engine tab you can define the database (connection) to be used to fetch data for the sub report.
You can choose from the following options available in this dialog:
Use Patent’s: This option will appear (and in selected state) when you are working for a sub-report.
Use Engine Default: This option will use the connection, which was marked as default on the Database Connections dialog. This option will appear as default selected by default when you are working with a report that is not a sub report.
Use Named: Select this option to provide the named (configured) connection to fetch data for the report. This will override the default connection.
Sub Report Summary information
Using the Summary tab you can specify summary information for the sub-report.
Figure 8: Sub Report Summary Tab
Simply enter the information relevant to the report in this dialog.