<< Click to Display Table of Contents >> Navigation:  Index > Configuring Local Tables

Data source configuration and management is handled via the Section Setup dialog.  This is where you add, delete, configure and specify the relationships between local tables.  For information on the basic operation and layout of the Setup Sections dialog, please refer to the previous chapter.

 

When local tables are used to supply data to a section the Setup Sections dialog will look similar to figure 28.  The figure shows two dBase tables related together.  In this example, the Invoice table is a child of the Parcel table.

 

Tables are added, deleted and configured via pop-up menu options that are available when you click the right mouse button over a table object.  The options available for local tables are:

 

Add Child Table/SQL Query

This option allows you to add a child table or SQL query to the selected table.  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 selecting 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.

 

Index

The Index option allows you to specify an existing index to use for the sort order.  This option is only available for the primary table in the data source.  Specifying this option can reduce the time required to generate a report since the sort order doesn't have to be created by ReportPro.

 

When you select the Index option you are presented with a dialog prompting you for the name of the index file.

 

You can enter the index file name directly or click on the button to the left of the Index File Name edit to be presented with a standard File Open dialog.

 

If the database driver you are using supports compound indexes, you must also specify the Index Tag name.  To select the index tag, click the button to the left of the Index Tag edit and you will be presented with a list of tags in the selected index file.  If you do not specify an Index Tag name, then the table will be accessed in record order.

 

Seek Expression

The Seek expression allows you to specify a starting point for the report.  If you specify a Seek expression, ReportPro will evaluate the expression before reporting starts and then seek to that location in the primary table.  Reporting will start at the point of the seek.

 

Seek expressions are only valid if an index for the primary table or sort order is  specified.  The seek that is performed is a soft seek; that is, it locates the point were the value returned by the expression would reside in the index even if the value is not present in the index.

 

If ReportPro creates the sort order, it is important to note that ReportPro converts all field values to character strings.  For example, if you create a sort order on a numeric field, ReportPro converts the field to a string value and then builds the index.  Generally this has no affect on reporting, but it does affect how you must specify a Seek expression since the Seek expression must also be specified as a character string.

 

Here's how ReportPro converts fields when creating an index:

 

 

While Expression

The While expression allows you to specify an expression which can conditionally terminate the report.  ReportPro starts at the top of the primary table (or at the position left by the Seek expression) and prints records that satisfy the filter condition.  If you specify a While condition the report prints until the While expression returns false at which point reporting stops.

 

Relationship

ReportPro allows you to control the relationship between tables. This feature gives you precise control over how your data is retrieved from the database.  When you select the Relationship option you are presented with the Table Relations dialog.  Here you can specify the relationship between a parent and a child table.

 

Local tables are linked by an expression in the Parent Table to an expression in the Child Table via an index.  The link between the two tables governs how the records in the Parent Table are related to the records in the Child Table.

 

Parent & Child Expressions

You can change the Parent and Child expression by choosing the appropriate Change button.  The Expression Builder is displayed to allow you to make the necessary changes.  The following algorithm is used by ReportPro to relate records between two tables:

 

PARENT EXPRESSION = CHILD EXPRESSION

 

This expression must evaluate to a logical TRUE before records are considered related.

 

Relationship Type

 

ReportPro allows you to specify how records in a child table are processed when a report is created.  There are two types of relationships supported by ReportPro.

 

The first type of relationship, the One to One relationship, occurs if one and only one record exists in a child table for each record in the parent table.  An example of this is when a Sales table is related to a Customer table.  Only one Customer can exist for each sale.

 

The second type of relationship, the One to Many relationship, occurs when more than one record exists in a child table for a record in the parent table.  This case exists when a Customer table is related into a Sales table.  Many Sales Orders can exist for each customer.

 

Refer to the figure above for the following discussion.  When the Relationship Type is set to One to One, records are processed in the following manor.  First, the record pointer in the parent table is moved.  Let's assume the current record after the move has a Customer Name of "Adams".  As you can see, there are three Sales Orders which belong in that Customer.  Next, the record pointer in the child table is moved to the first record that has a Customer Name of "Adams".  Then the Customer and Sales table records are printed on the report.  After printing, the Customer record pointer is moved to Customer "Jones".  The Sales record pointer is moved to the first Sales Order for "Jones".  As you can see, we skipped over two Sales records.  The records were not printed on the report.

 

If the Relationship Type had been set to One to Many, the following would have occurred.  After the Customer record pointer was moved to Customer "Adams" and the Sales record pointer was moved to the first record for "Adams", the records would have been printed on the report.  Next, instead of moving the Customer record pointer to the next Customer, the Sales record pointer would be moved to the next Sales record for Customer "Adams".  The Customer record pointer does not move.  The new Sales record is printed along with the Customer record.  The Sales record pointer is moved again.  This continues until there are no more Sales records for Customer "Adams".  Only at that time is the Customer record pointer moved to "Jones" and the sequence of events repeats.

 

The record movement method you choose depends on the type of report you are trying to produce.  The One to Many method can take significantly longer to traverse a large data set since it is accessing many more records.

 

Child Index for Relationship

When a local table is linked to another table, an index must be used on the child table to support the relationship.  The report engine creates this index automatically if an existing index is not specified.  

 

The drawback to automatic index creation is that it increases the time it takes to run a report.  If an index already exists that supports the relationship, it can be specified by choosing the Select button.  If an index is specified, the report engine will assume that it correctly supports the relationship and will skip the automatic index creation; this greatly speeds up the process of producing the report.

 

Index Tag

RDD Drivers such as the CDX driver, support index files that contain multiple indexes.  A tag is required to identify which index should be used in the index file.  You can select the proper index tag by choosing the Change button next to the Index Tag information box.

 

Change RDD

This option allows you to change the database driver you are using for a table.  When you select this option you will be presented with a list of available drivers to select from.  The driver you choose depends on the type of index and memo files used by the table.

 

The NTX driver should be used with Clipper indexes and .DBT memo files.  The CDX driver should be used with FoxPro indexes and .FPT memo files.  The MDX driver is used to access dBase 4 indexes and memo files.

 

Delete Item

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