<< Click to Display Table of Contents >> Navigation:  »No topics above this level« Configuring SQL Sources

 

If you create reports that use ODBC, you'll eventually need to customize and configure the query that is used by ReportPro to retrieve information from the SQL server.  As with sections and DBF tables, the SQL query is configured in the Section Properties dialog.  Here you can add and delete SQL tables from your query.  You can also control the relationship between tables and even customize clauses in the SQL Select statement.

 

 

The figure above illustrates a typical SQL query that uses three SQL tables.  The SQL query is represented by the icon, and the SQL tables are represented by the icon. SQL tables are considered attributes (rather than children) of the query since the 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.

 

SQL Query Options

 

Configuration options for the SQL query are available via a pop-up menu that is activated by clicking the right mouse button over the query icon in the Section Properties dialog.  Each of these options is discussed below.

 

Add Child Table/SQL Query

 

This option allows you to add a child DBF table or query to the currently selected 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 how to specify 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 query here does not modify the SQL Select statement of your query.  This feature is implemented by ReportPro to allow information from dissimilar data sources to be combined relationally in a report.  If you want to add a table to the SQL Select statement, you must add a child table to one of the SQL tables in your query.  This process is discussed under the SQL table options.

 

Login

 

This option allows you to specify a different login to use when connecting to the SQL server.  By default, ReportPro saves the login you used when you created the report.  You can change the login via this option should your user ID change or password expire.  When you select this option, you are presented with a dialog where you can specify your login information.

 

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.

 

SQL Properties

 

To provide complete control over the data retrieval process, ReportPro allows you to manipulate the SQL Select statement via the SQL Select Statement dialog.  The dialog contains three tabs: Query, Parameters, and Generated SQL that allow you to customize or view the SQL Select statement.

 

Delete Item

 

This option deletes the selected SQL query.  It is important to note that when you delete the query, the children of the query as well as all SQL tables that make up the query are deleted also.

 

SQL Table Options

 

Configuration options for the SQL tables are accessed via a pop-up menu like all objects in the Section Properties dialog.  Each of the options is covered below.

 

Add Child SQL Table

 

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.

 

Delete Item

 

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.

 

SQL Table Relationships

 

This option allows you to specify the relationship between SQL tables.

 

 

Also See:

 

SQL Select Statement

SQL Table Relationship