Using the Report Data Wizard

In the Report Data Wizard, you can specify the details of your SQL query, including the database and tables, the joins between the tables, the table fields, and the sort order.

A Wizard is now available to generate BDL code for your Genero report program (4gl). Select File > New > Reports > Report from Database (.4gl) to display the dialogs that guide you in creating the SQL query statement that will extract the report data from your database.

Table Selection Page

Select the database and tables for the query.

This figure is a screenshot of the New Query wizard, Table Section page. — Figure: New Query wizard, Table Section page

Press Next to continue.

Define Joins Page

The New Query dialog is used to add the joins between the tables to be used in the SQL query. The information is pre-filled from the database meta-data in the database meta-schema file (4db). You can select different columns in the tables, or select a different join method.

Left column: The table on the left side of the join operator.
Right column: The table on the right side of the join operator.
Operator: the method to be used to join the tables for the result set.

Press Next to Continue.

This table illustrates the effect of the operators on the query result set:

Table 1. Effect of the join operators on the query result set
Operator	Description	Example WHERE clause generated for BDL code
INNER JOIN	Result set will contain columns from only the rows in the two tables where the specified column value matches.	`WHERE orders.userid = account.userid`
LEFT OUTER JOIN	Result set will contain columns from all the rows in the left table, joined with columns from the rows in the right table where the specified column value matches. Where there is no matching value, the columns from the right table in the result set will contain nulls.	`WHERE (orders.userid = account.userid OR account.userid IS NULL)`
RIGHT OUTER JOIN	Result set will contain columns from all the rows in the right table, joined with columns from the rows in the left table in which the specified column value matches. Where there is no matching value, the columns from the left table in the result set will contain nulls.	`WHERE (orders.userid = account.userid OR orders.userid IS NULL)`
FULL OUTER JOIN	Result set will contain columns from all the rows in the left table, and columns from all the rows in the right table, joined on the specified column value. Where there is no match in one of the tables, the columns from that table will contain nulls.	`WHERE (orders.userid = account.userid OR account.userid IS NULL OR orders.userid IS NULL)`

Result Set Page

Select the database table fields for the result set of the SQL query. The data in the result set is passed by the BDL REPORT function to the Genero Report Engine and the Report Definition file (4rp).

Press Next to Continue.

Order By Page

Select the column(s) by which the result set should be sorted.

Press Finish to generate the code and close the Report Data Wizard.

The Wizard creates a BDL file (4gl) containing the generated code, which you can save as part of your application files.

Creating reports containing lookup tables

When using the Table selection page, selecting lookup tables – such as the target table in a foreign key relationship - can lead to logic errors in the WHERE clause of the generated SQL query.

In the example below, the country table is the target table for three defined foreign keys.

This type of join definition would results in three statements in the where clause, as seen in this pseudo code:

WHERE account.country = country.code AND orders.billcountry = country.code AND orders.shipcountry = country code ...

If these three country values are not identical, the row would be rejected, which is clearly not the intent. The intent is to provide the full country name for each of these three country-related fields - and it could differ for each field.

To resolve this, do not select a lookup table in the Table selection page. Instead, select the other tables using the wizard and generate your BDL code, then updated the SQL contained to add subqueries that look up the country for each of the lookup fields.

The code snippet example below updates the generated BDL to define three variables to hold the three different country names and the generated SQL to select the values into variables by using subqueries.

SCHEMA officestore

--The RECORD definition has been modified
TYPE acct_ord_data RECORD
    account RECORD LIKE account.*,
    account_country LIKE country.codedesc,
    item RECORD LIKE item.*,
    lineitem RECORD LIKE lineitem.*,
    orders RECORD LIKE orders.*,
    orders_billcountry LIKE country.codedesc,
    orders_shipcountry LIKE country.codedesc,
    product RECORD LIKE product.*
END RECORD

FUNCTION run_acct_ord_to_handler(handler)
    DEFINE
        data acct_ord_data,
        handler om.SaxDocumentHandler
    DECLARE cur CURSOR FOR
--The SELECT clause has been modified
        SELECT 
            account.*,
            (SELECT country.codedesc FROM country WHERE country.code = account.country) AS account_country,
            item.*,
            lineitem.*,
            orders.*,
            (SELECT country.codedesc FROM country WHERE country.code = orders.billcountry) AS orders_billcountry,
            (SELECT country.codedesc FROM country WHERE country.code = orders.shipcountry) AS orders_shipcountry,
            product.*
        FROM 
            account,
            item,
            lineitem,
            orders,
            product
        WHERE 
            product.productid = item.productid AND 
            item.itemid = lineitem.itemid AND 
            orders.orderid = lineitem.orderid AND 
            account.userid = orders.userid
        ORDER BY
            account.userid,
            orders.orderid

    START REPORT acct_ord_report TO XML HANDLER handler
    FOREACH cur INTO data.*
        OUTPUT TO REPORT acct_ord_report(data.*)
    END FOREACH
    FINISH REPORT acct_ord_report
    CLOSE cur
END FUNCTION

REPORT acct_ord_report(data)
    DEFINE
        data acct_ord_data
    ORDER EXTERNAL BY 
        data.account.userid,
        data.orders.orderid

    FORMAT
        ON EVERY ROW
            PRINTX data.*
END REPORT