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.
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.
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
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
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
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.
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