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.

Figure: New Query wizard, Table Section page


This figure is a screenshot of the 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.

Figure: New Query wizard, Define joins page


This figure is a screenshot of New Query wizard, Define joins page.
  • 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).

Figure: New Query wizard, Result Set page


This figure is a screenshot of the New Query wizard, Result Set page.

Press Next to Continue.

Order By Page

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

Figure: New Query wizard, Order By page


This figure is a screenshot of the New Query wizard, Order By page.

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.