Creating a simple report

Initial statements

Import the FGL modules greruntime and gredesigntime.
```
IMPORT FGL greruntime
IMPORT FGL gredesigntime
```
Important: Ensure that the Language property of the Group or Application node is set to Genero (no link). See Reporting API Functions for further details.
Set the database schema.
```
SCHEMA officestore 
```

Define a User Type that consists of a single record containing all the fields from all the referenced tables.

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

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.

MAIN program block

The MAIN program block contains the program logic that configures the report design and sets up the handler, which will handle the streaming of the data to the Genero Report Engine.

Defines a report handler object.
```
DEFINE handler om.SaxDocumentHandler
```
Calls the mandatory BDL functions that configure the report.
```
IF fgl_report_loadCurrentSettings("myreport.4rp") THEN                                                  
   LET handler = fgl_report_commitCurrentSettings()     
ELSE
   EXIT PROGRAM
END IF
```
- 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.

 IF handler IS NOT NULL THEN          
     CALL runReportFromDatabase(handler)   
 END IF

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.

The Report Driver

The runReportFromDatabase() function uses SQL to extract the data from the database officestore. The 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, and defines handler passed as a parameter to this function.
```
DEFINE orderline ORDERTYPE,            
       handler om.SaxDocumentHandler   
```
Makes a connection to the database in unique-session mode, because the program will not need to connect to other databases.
```
DATABASE "officestore"
```

Declares a cursor for the SQL statement.

DECLARE c_order CURSOR FOR  
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

Uses the ORDER BY clause of the SQL statement to define the sort order of the data.
```
ORDER BY orders.userid, orders.orderid, lineitem.linenum 
```

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.
```
START REPORT report_all_orders TO XML HANDLER handler
```
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.

FOREACH c_order INTO orderline.*                  
  OUTPUT TO REPORT report_all_orders(orderline.*)                                                      
     IF fgl_report_getErrorStatus() THEN            
          DISPLAY "FGL: STOPPING REPORT, msg=\"",fgl_report_getErrorString(),"\""
         EXIT FOREACH
     END IF 
END FOREACH

The FINISH REPORT terminates the BDL report process.
```
FINISH REPORT report_all_orders 
```
Closes the SQL cursor.
```
CLOSE c_order 
```

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 document (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: Because 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.

In the example REPORT block:

Variables are defined to allow calculations on the data values; the calculations are output along with the data.

DEFINE
    orderline     ORDERTYPE,
    lineitemprice LIKE lineitem.unitprice,   
    overalltotal  LIKE orders.totalprice,   
    ordertotal    LIKE orders.totalprice

The ORDER EXTERNAL BY statement informs the report that the data was retrieved, and will be output, in the specified sort order.
```
ORDER EXTERNAL BY orderline.orders.userid, orderline.orders.orderid,
                     orderline.lineitem.linenum 
```
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).
```
FORMAT
```
The variable overalltotal is set to zero at the control break at the beginning of the report (FIRST PAGE HEADER).
```
FIRST PAGE HEADER
       LET overalltotal=0 
```
The variable ordertotal is re-set to zero each time the value of orderid changes in the data received from the report driver (BEFORE GROUP OF).
```
BEFORE GROUP OF orderline.orders.orderid 
       LET ordertotal=0
```

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.

ON EVERY ROW
  LET lineitemprice = orderline.lineitem.unitprice * 
      orderline.lineitem.quantity 
  LET overalltotal=overalltotal + lineitemprice 
  LET ordertotal=ordertotal + lineitemprice 
  PRINT orderline.*, lineitemprice, overalltotal, ordertotal

Example: SimpleReport.4gl

In this example, you have the code you need to run a simple report application. Copy and paste the code to your module, and edit as required.

IMPORT FGL greruntime
IMPORT FGL gredesigntime

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

   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

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
        IF fgl_report_getErrorStatus() THEN            -- stop the report if an error occurs or the user aborts the report
            DISPLAY "FGL: STOPPING REPORT, msg=\"",fgl_report_getErrorString(),"\""
            EXIT FOREACH
        END IF 
   END FOREACH
   FINISH REPORT report_all_orders 
   CLOSE c_order 
   
 END FUNCTION

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