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.

defines a report handler object.
call the mandatory BDL functions that configure the report:
- fgl_report_loadCurrentSettings accepts the name of the Report Design document (4rp) as a parameter. The return value is a boolean indicating whether the load of the settings from the 4rp file was successful.
- fgl_report_commitCurrentSettings returns a SaxDocumentHandler object, defined as handler.
calls the Report Driver function, runReportFromDatabase, to run the report, passing handler.

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:

handler previously created by the fgl_report_commitCurrentSettings function is passed to this function
defines a record variable orderline using the User Type defined in MAIN
defines handler passed as a parameter to this function
makes a connection to the database in unique-session mode, since the program will not need to connect to other databases.
declare a cursor for the SQL statement.
uses the ORDER BY clause of the SQL statement defines the sort order of the data.
These next statements are only used when the data is being retrieved from a database.
- the BDL statement START REPORT <reportname> TO XML HANDLER must be used to instantiate the report driver, using the handler that was passed to this function; this specifies that the report data should be output in XML format.
- the FOREACH loop opens the cursor and fetches the data from the database into the record variable, one row at a time.
- the OUTPUT TO REPORT statement outputs each data row to the REPORT program block.
- the FINISH REPORT terminates the BDL report process.
- closes the SQL cursor.

 
 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 Genero Business Development Language 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:

FIRST PAGE HEADER - specifies the action that the runtime system takes before it begins processing the first input record.
BEFORE GROUP OF/AFTER GROUP OF - specifies the action the runtime system takes before or after it processes a group of input records.
ON EVERY ROW - specifies the action the runtime system takes for every input record that is passed to the report definition.
ON LAST ROW - specifies the action the runtime system takes after it processes the last input record that was passed to the report definition and encounters the FINISH REPORT statement.

See BDL Reports in the Genero Business Development Language 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:

define variables to allow calculations on the data values; the calculations are output along with the data.
the ORDER EXTERNAL BY statement informs the report that the data was retrieved, and will be output, in the specified sort order
the FORMAT section of the report uses control blocks to set the values of the report variables used to store calculations and to send the report data and calculations to the report. Unlike a Genero report, the PRINT statement does not contain any formatting of the data or the report line ( SPACES, LINE_NO, and COLUMN operators aren't used, for example.) The report format is specified in the Report Design document (4rp).
set the variable overalltotal to zero at the control break at the beginning of the report (FIRST PAGE HEADER).
re-set the variable ordertotal to zero each time the value of orderid changes in the data received from the report driver (BEFORE GROUP OF).
For each row of data received from the report driver (ON EVERY ROW), these lines store some calculations in variables. The PRINT statement outputs the database data and variables to the report, in XML format.

 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