Creating a simple report

This example BDL program (SimpleReport.4gl) uses data from the officestore database to create a report. There are more complex examples in the Reports project provided as a demo with Genero Report Writer.

Type Definition

This section gives the database schema used in variable definitions, and defines a User Type that consists of a single record containing all the fields from all the referenced tables.

Creating a TYPE allows the record definition to be specified only once in the program; thereafter, the name of the TYPE is used in the program wherever that record definition would be required.
SCHEMA officestore 

TYPE ORDERTYPE RECORD
          orders    RECORD LIKE orders.*,
          account   RECORD LIKE account.*,
          country   RECORD LIKE country.*,
          lineitem  RECORD LIKE lineitem.*,
          product   RECORD LIKE product.*,
          category  RECORD LIKE category.*,
          item RECORD LIKE item.*
     END RECORD

MAIN program block

The MAIN program block contains the program logic that allows a user to run a report.

Note: The example runReportFromDatabase function contains the START REPORT statement. Do not place any code between the call to fgl_report_commitCurrentSettings and the START REPORT statement that would allow the user to cancel the report
MAIN

   DEFINE handler om.SaxDocumentHandler-- report handler

   --call the mandatory functions that configure the report  
   IF fgl_report_loadCurrentSettings("myreport.4rp") THEN -- if  the file 
                                                            -- loaded OK
     LET handler = fgl_report_commitCurrentSettings()     -- commit the file 
                                                            -- settings
   ELSE
     EXIT PROGRAM
   END IF

  -- run the report by calling the report driver contained 
    -- in your function runReportFromDatabase
   IF handler IS NOT NULL THEN          
     CALL runReportFromDatabase(handler)   
   END IF

 END MAIN
Important: The libgre.42x library contains these mandatory functions and other BDL helper functions for Reports. This library must be included in any report application. List this library in the external dependencies property of any Genero Studio Project Manager application node that uses Genero Report Writer.

The Report Driver

The runReportFromDatabase function uses SQL to extract the data from the database officestore:

 
 FUNCTION runReportFromDatabase(handler)
   DEFINE orderline ORDERTYPE,                  -- User Type defines record
          handler om.SaxDocumentHandler -- definition for parameter 
                                          -- passed to this function
     
   DATABASE "officestore"    -- database connection
   DECLARE c_order CURSOR FOR  -- cursor declaration
   SELECT orders.*,
         account.*,
         country.*,
         lineitem.*,
         product.*,
         category.*,
         item.*
   FROM orders, account, lineitem, product, category, item, country 
   WHERE
    orders.orderid = lineitem.orderid 
    AND orders.userid = account.userid 
    AND lineitem.itemid = item.itemid 
    AND item.productid = product.productid 
    AND product.catid = category.catid 
    AND country.code = orders.billcountry 
  ORDER BY orders.userid, orders.orderid, lineitem.linenum 

   START REPORT report_all_orders TO XML HANDLER handler -- handler that was 
                                                        -- passed to this function
   FOREACH c_order INTO orderline.*                   -- use cursor to fetch data
     OUTPUT TO REPORT report_all_orders(orderline.*)  -- send data to report 
                                                        -- function
   END FOREACH
   FINISH REPORT report_all_orders 
   CLOSE c_order 
   
 END FUNCTION

See the BDL User Guide for additional information about the use of cursors, connections, and BDL report statements.

The REPORT program block

This program block accepts the data from the driver, specifies the order in which the data was sorted, and outputs the data to be formatted as specified in the report design page (4rp).

The FORMAT section specifies the control blocks for a report. The use of each control break is optional, depending on the requirements of your report document. We recommend that you restrict your usage to these control blocks:

See BDL Reports in the BDL User Guide for a complete discussion of the BDL statements associated with a REPORT block.

Note: Since PAGE HEADER and PAGE TRAILER are triggered based on the line count of the BDL report, which does not correspond with the actual page breaks, their usage should be avoided. Create page headers and footers in the report design document instead.

Example REPORT block from SimpleReport.4gl:

 REPORT report_all_orders( orderline )
   DEFINE
      orderline     ORDERTYPE,
      lineitemprice LIKE lineitem.unitprice,   -- total price for item
      overalltotal  LIKE orders.totalprice,    -- accumulator for total price 
                                                 -- for report
      ordertotal    LIKE orders.totalprice     -- accumulator for total price 
                                                 -- for order

  -- specify the order of the sorted data resulting from SQL statement
   ORDER EXTERNAL BY orderline.orders.userid, orderline.orders.orderid,
                     orderline.lineitem.linenum 

   FORMAT
     FIRST PAGE HEADER
       LET overalltotal=0                      -- initialize report total

     BEFORE GROUP OF orderline.orders.orderid 
       LET ordertotal=0                        -- initialize ordertotal for 
                                                 -- each new order  
  
    -- after calculations for each data row, output the data and 
      -- the calculations to the Report Engine
     ON EVERY ROW
       LET lineitemprice = orderline.lineitem.unitprice * 
           orderline.lineitem.quantity 
       LET overalltotal=overalltotal + lineitemprice 
       LET ordertotal=ordertotal + lineitemprice 
       PRINT orderline.*, lineitemprice, overalltotal, ordertotal 

 END REPORT