Coding for sub reports

A sub report is a report that is started from within another report using a START REPORT statement.

The START REPORT statement for the sub report takes no arguments. Do not add TO XML HANDLER for the sub report call.

Sub reports can be called from any control block wherever printing in loops is allowed: ON EVERY ROW, BEFORE GROUP, AFTER GROUP, or ON LAST ROW.

The START REPORT must be in the calling report block. The START REPORT statement cannot be placed inside a function called from the report.

The START REPORT, OUTPUT TO REPORT, and FINISH REPORT statements must be in the same control block. The OUTPUT TO REPORT and FINISH REPORT statements can be placed inside a function or functions called from the report, however those functions must be in the same control block as the START REPORT statement.

You can have multiple calls to sub reports from the same control block. However, the calls cannot be nested.

Valid:

REPORT master_report(...)
...
FORMAT
  BEFORE GROUP ...
  ...
  START REPORT detail_report_1
  FOREACH ...
    OUTPUT TO REPORT detail_report_1(...)
  END FOREACH
  FINISH REPORT detail_report_1
  START REPORT detail_report_2
  FOREACH ...
    OUTPUT TO REPORT detail_report_2(...)
  END FOREACH
  FINISH REPORT detail_report_2
...
END REPORT

Not valid:

REPORT master_report(...)
...
FORMAT
  BEFORE GROUP ...
  ...
  START REPORT detail_report_1
  START REPORT detail_report_2 -- Not allowed until after 
                               -- FINISH REPORT detail_report_1
  ...
END REPORT

Sub reports can be nested to arbitrary depth, but recursion is not allowed.

REPORT master_report(...
 ...
FORMAT
  ON EVERY ROW    
    ...
    START REPORT detail_report
      FOREACH ...
        OUTPUT TO REPORT detail_report(...
      END FOREACH
      FINISH REPORT detail_report 
END REPORT

REPORT detail_report(...
  ...
  FORMAT
    ON EVERY ROW
      ...
      START REPORT other_detail_report -- Since recursion is not allowed, we are 
                                       -- not allowed to call "master-report" or 
                                       -- "detail_report" from here.
      FOREACH ...
        OUTPUT TO REPORT other_detail_report(...
      END FOREACH
      FINISH REPORT other_detail_report
END REPORT

Detected sub report calls are shown in the report designer. They appear as nodes in the data view, triggers the structure view, and images in the document view.

Example 1

In this example, the master report (master_report) calls two distinct sub reports (acct_order_subreport_1 and acct_order_subreport_2). The invocation of a sub report requires the START REPORT, OUTPUT TO REPORT and FINISH REPORT from within the master report.

FUNCTION run_master_report(handler)

DEFINE handler om.SaxDocumentHandler

    START REPORT master_report TO XML HANDLER HANDLER
    OUTPUT TO REPORT master_report()
    FINISH REPORT master_report

END FUNCTION

REPORT master_report()

-- Report definition for master report
    DEFINE data acct_order_data

    FORMAT
        ON EVERY ROW
            DISPLAY "SUBREPORT 1"
            DECLARE cur_sr1 CURSOR FOR
                SELECT 
                    account.*,
                    country.*,
                    item.*,
                    lineitem.*,
                    orders.*,
                    product.*,
                    category.*
                FROM 
                    account,
                    country,
                    item,
                    lineitem,
                    orders,
                    product,
                    category
                WHERE 
                    country.code = account.country AND 
                    product.productid = item.productid AND 
                    item.itemid = lineitem.itemid AND 
                    orders.orderid = lineitem.orderid AND 
                    account.userid = orders.userid AND 
                    country.code = orders.billcountry AND 
                    country.code = orders.shipcountry AND
                    product.catid = category.catid
                ORDER BY
                    account.userid,
                    orders.orderid
            START REPORT acct_order_subreport_1
            FOREACH cur_sr1 INTO data.*
                OUTPUT TO REPORT acct_order_subreport_1(data.*)
            END FOREACH
            FINISH REPORT acct_order_subreport_1
            CLOSE cur_sr1

            DISPLAY "NOW FOR SUBREPORT 2"
            DECLARE cur_sr2 CURSOR FOR
                SELECT 
                    account.*,
                    country.*,
                    item.*,
                    lineitem.*,
                    orders.*,
                    product.*,
                    category.*
                FROM 
                    account,
                    country,
                    item,
                    lineitem,
                    orders,
                    product,
                    category
                WHERE 
                    country.code = account.country AND 
                    product.productid = item.productid AND 
                    item.itemid = lineitem.itemid AND 
                    orders.orderid = lineitem.orderid AND 
                    account.userid = orders.userid AND 
                    country.code = orders.billcountry AND 
                    country.code = orders.shipcountry AND
                    product.catid = category.catid
                ORDER BY
                    account.userid,
                    orders.orderid
            START REPORT acct_order_subreport_2
            FOREACH cur_sr2 INTO data.*
                OUTPUT TO REPORT acct_order_subreport_2(data.*)
            END FOREACH
            FINISH REPORT acct_order_subreport_2
            CLOSE cur_sr2
            
END REPORT

REPORT acct_order_subreport_1(data)
    DEFINE
        data acct_order_data,
        item_total DECIMAL(10,2),
        order_total DECIMAL(10,2),
        account_total DECIMAL(10,2),
        report_total DECIMAL(10,2)
    ORDER EXTERNAL BY 
        data.account.userid,
        data.orders.orderid

    FORMAT
        FIRST PAGE HEADER
            LET report_total = 0
        BEFORE GROUP OF data.account.userid
            LET account_total = 0
        BEFORE GROUP OF data.orders.orderid
            LET order_total = 0
        ON EVERY ROW
            LET item_total = data.lineitem.quantity*data.lineitem.unitprice
            LET order_total = order_total + item_total
            LET account_total = account_total + item_total
            LET report_total = report_total + item_total
            PRINTX data.*, item_total, order_total, account_total, report_total
END REPORT

REPORT acct_order_subreport_2(data)
    DEFINE
        data acct_order_data,
        item_total DECIMAL(10,2),
        order_total DECIMAL(10,2),
        account_total DECIMAL(10,2),
        report_total DECIMAL(10,2)
    ORDER EXTERNAL BY 
        data.account.userid,
        data.orders.orderid

    FORMAT
        FIRST PAGE HEADER
            LET report_total = 0
        BEFORE GROUP OF data.account.userid
            LET account_total = 0
        BEFORE GROUP OF data.orders.orderid
            LET order_total = 0
        ON EVERY ROW
            LET item_total = data.lineitem.quantity*data.lineitem.unitprice
            LET order_total = order_total + item_total
            LET account_total = account_total + item_total
            LET report_total = report_total + item_total
            PRINTX data.*, item_total, order_total, account_total, report_total
END REPORT

Example 2

In this example, the master report (master_report) calls the same sub report block (sub_report) for both sub reports.

SCHEMA grwdemo

TYPE storeorder_data RECORD
        orders RECORD LIKE orders.*,
        items RECORD LIKE items.*,
        customer RECORD LIKE customer.*
END RECORD

FUNCTION run_report(hand)
    DEFINE hand om.SaxDocumentHandler
 
    START REPORT master_report TO XML HANDLER hand
    OUTPUT TO REPORT master_report()
    FINISH REPORT master_report
END FUNCTION

REPORT master_report()
-- This is the master report. 
-- PREPARE and DECLARE the cursor to define data to be read in.
-- For each of the sub-reports, include a START REPORT|FOREACH|FINISH REPORT

    DEFINE data storeorder_data
        
    FORMAT
        ON EVERY ROW
        PREPARE s1 FROM 
           "SELECT orders.*, items.*, customer.*
            FROM orders, items, customer
        WHERE 
            orders.order_num = items.order_num AND
            customer.store_num = orders.store_num 
        ORDER BY
            customer.store_num, orders.order_num"
            
        DECLARE cur CURSOR FOR s1

            -- Notice that both reports are using the sub_report() function.
            -- They don't have to, but they can; 
            -- It means that the data being streamed by the sub_report() function
            -- is valid for both reports.
            
            # First sub-report      
            START REPORT sub_report
            FOREACH cur INTO data.*
                OUTPUT TO REPORT sub_report(data.*)
            END FOREACH
            FINISH REPORT sub_report

            # Second sub-report
            START REPORT sub_report
            FOREACH cur INTO data.*
                OUTPUT TO REPORT sub_report(data.*)
            END FOREACH
            FINISH REPORT sub_report
            
END REPORT

REPORT sub_report(data)
-- This function is taking in each row, streaming to report, and managing 
-- the store_total number needed by the reports.

    DEFINE
        data storeorder_data,
        store_total DECIMAL
        
    ORDER EXTERNAL BY 
        data.customer.store_num,
        data.orders.order_num
    
    FORMAT

        BEFORE GROUP OF data.customer.store_num
            LET store_total = 0
            
        ON EVERY ROW
            LET store_total = store_total + (data.items.price * data.items.quantity)    
            PRINT data.*, store_total
END REPORT