Configuring SQL Sources

If you create reports that use ODBC, you'll eventually need to customize and configure ReportPro's SQL Query object. The SQL Query object is used by ReportPro to retrieve information from ODBC sources.

As with sections and local tables, the SQL Query object is configured in the Setup Sections dialog. Here you can add and delete tables from a query. You can also control the relationship between tables and even customize the SQL Select statement.

Figure 33 illustrates a typical SQL Query object that uses three SQL Tables. SQL Tables are considered attributes (rather than children) of the SQL Query object since a query is simply a collection of related tables.

Child objects are normally separate entities that are joined to a parent object by a relationship as is the case when SQL Tables are related to each other. In the figure above, note that the Orders table is a child of the Customers table.

Configuration options are available via a pop-up menu which is activated by clicking the right mouse button over the SQL Query object. Each of the options available in the pop-up menu is covered below.

This option allows you to add a child local table or SQL Query to the SQL Query. If you choose this option, you will be prompted to select a database driver and data source. See "Creating a Standard Report" for more information on specifying a data source.

When you add a child table or query, ReportPro automatically defines the relationship between the parent and the child. To modify the relationship, select the Relationship option for the child table or query.

Adding a child table or SQL Query does not modify the SQL Query. The procedure to add SQL Tables to a query is discussed under the SQL Table options below.

This option allows you to specify a login to use to connect to the SQL server. When you select this option you are presented with a dialog where you can specify a User ID and Password. The specified User ID and Password will be used to connect to the server the next time the report is opened. The current connection remains unchanged.

To provide complete control over the data retrieval process, ReportPro allows you to manipulate the SQL Select statement that is sent to the ODBC driver. A discussion of SQL is beyond the scope of this manual. Furthermore, it is assumed that the reader has a basic understanding of SQL.

The SQL Select Statement dialog is broken up into sections. Each section represents a particular clause in the SQL Select statement. Each section and option is discussed below.

Select Clause	Description
Select	By default, ReportPro retrieves only columns from an SQL data source. Some data sources, however, support calculated columns which are not accessible through ReportPro's design environment. This edit allows you to retrieve calculated columns and system variables from a SQL data source. The columns you define here are accessible in a report expression by calling the RpSQLCol() function. For example, if you specify "Count(*)" here, you could access that column in a report with "RpSQLCol(1)".
From	This edit allows you to modify the SQL From clause. This information is automatically maintained by ReportPro and normally should not be modified.
Where	ReportPro splits the Where clause in two sections. This edit allows you to modify the table join specification portion of the Where clause. This section is automatically maintained by ReportPro and normally should not be changed.
Filter	This edit allows you to specify the selection specification portion of the Where clause. If you are using a single SQL Query, you should specify the filter criteria at the Section level via the Filter pop-up menu option. If the Section Optimize Filter option is selected, the Section filter is automatically passed to the SQL source. Specifying a filter at the this level is useful if you are using multiple SQL Queries in a report.
Group By	This edit allows you to modify the SQL Group By clause. This information is passed directly to the SQL source and is not used by ReportPro.
Having	This edit allows you to modify the SQL Having clause. This information is also passed directly to the SQL source and is not used by ReportPro.
Order By	This edit allows you to modify the SQL Order By clause. This information is automatically maintained by ReportPro via the Order main menu option or the Section's Sort Order pop-up menu option. If the Section Optimize Sort Order option is selected, the Section sort order is automatically passed to the SQL source. This section should only be modified if you can not achieve the proper results at the section level.
Union	This edit allows you to modify the Union clause. This information is passed directly to the SQL source and is not used by ReportPro.
Distinct Check box	This option forces ReportPro to issue "SELECT DISTINCT ...."
Always Use * Check box	This options forces ReportPro to issue "SELECT *" instead of explicitly stating the column names.
Delimit With	This edit allows you to change the character delimiter used for the column names. This information is retrieved from the ODBC driver and normally should not be modified.
Show SQL	This button allows you to view the SQL Select statement that is sent to the SQL data source. Note that the Section's sort order and filter information is not included in the Select statement.
Test SQL	This button sends the SQL Select statement to the SQL source and displays a dialog window that identifies if the statement executed successfully. If execution fails, an error message is displayed. Note that the Section's sort order and filter information is not included in the Select statement/

This option deletes the selected SQL Query. It is important to note that when you delete the query, the children of the query are also deleted.

Configuration options for the SQL Tables are accessed via pop-up menu like all objects in the Setup Sections dialog. Each of the options are covered below.

This option adds a new table into the SQL Query as a child of the selected SQL Table. ReportPro automatically creates the relationship between the parent and child table.

This option deletes the selected SQL Table. It is important to note that when you delete a table you also automatically delete all the children of that table.

The SQL Table relationship implementation is different than that used for local tables since SQL hides the user from the implementation aspects of the table relationship and leaves that to the server.

The SQL Table Relationship dialog focuses on specifying conditions that relate tables rather than how they are related. The dialog contains three list boxes. The left list box holds columns from the parent table. The right list box holds columns from the child table. The center list box holds the operator that defines the relationship between the parent and child columns.

To add a new condition, click the Add button and a new row will be added to the list boxes. To change either the parent or child column, select the desired column from the combo box located below the appropriate list box. To change the relationship operator, click the desired button in the Relationship Operators group.

To delete a relationship condition, highlight the desired row and click the Delete button.

The Join Type group allows you to specify the SQL join type. This feature is server dependent and may not be supported by all servers. This feature also affects how the SQL Select statement is generated. If the Inner Join option is selected, ReportPro generates a SQL 1.0 compatible Select statement. Any other option causes ReportPro to generate a SQL 2.0 compatible statement.