Accessing Parent Report’s Data from Sub-Report

You can access parent report’s data from within the sub-report. The 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.  These parameters can be used in sub report as it is, as well as in SQL as filter.

For example, 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 9: Report Layout of the sample report

Figure 10: 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.