The Report Designer Part 2

Part 1 of the report designer explained the overhead concepts needed to create reports. This part discusses other topics you will need to understand for creating an effective report.

ABOUT DATABASES

OK, time to go to school for a minute. Office Express is a relational database program. How the database works is, and should be invisible to you. But, there's always a but, to create reports you will need a crash course in database design 101.

Simply, relational databases consist of files, called tables, where the data in these files are related to each other. For example, in Office Express, the customer table only contains the "overhead" information about each customer. Customer data is then spread out into other tables. The Scheduled table contains scheduled jobs, the transaction table contains the completed jobs and payments while the invoice table contains all the invoices.

Each customer does not have it's own scheduled table. The scheduled table contains the scheduled jobs for all customers. The same holds true for the transaction and the invoice tables. How this is done is described in the next section.

Parent and Child Tables

The scheduled, invoice and transaction tables are considered child tables of the customer table. Just as in real life, a child is related to a single set of parents while parents can have many children. And just as in real life the term "relationship" applies. To continue with the real life analogy, a family tree can be built.

To simplify, if there is a Maintenance scheduled for customer Jones, the maintenance can have only one parent-- Jones, but Jones can have many other jobs scheduled for him (the other children).

To continue...

Data is broken down into separate related tables. For example, the scheduled work for all your customers are stored in one single table. Each customer is automatically assigned an ID number and the scheduled jobs are "tagged" with the ID number of the customer they belong to. The same holds true for invoices, payments and completed work. All the separate tables are joined by the customer ID number. While the customers are joined to the Category table by Category ID and the Categories are joined to the Company by Company ID

Don't get confused by this, what you'll need to define is simple. If you are creating a scheduled job report, the parent table is the customer table and the child table is the scheduled table. For a completed job report, the child table is the transaction table and for an invoice report, the child table is the invoice table.

In the Office Express design, the ultimate parent table is the company table-- it has no parent. The customer table is a child of the company table and the scheduled, transaction and invoice tables are child tables to the customer table. This diagram illustrates: COMPANY-->CUSTOMER-->THE CUSTOMER CHILD TABLES: (Scheduled, Transactions, Invoices, Proposals, Contracts)

To see it in action, open the report designer and look at how the reports that came with Office Express are designed.

Defining the Child/ Parent in a Report

Select the File option, then Document Setup... A dialog box will appear. Enter a name for your document then click the Data Tables tab at the top of the dialog. Choose the Parent and Child tables. If you are printing collection letters the Parent will be the Company table and the child will be the Customer table. If you are designing a report that will display information from one of the customer child tables then the parent table is the Customers table and the child table depends on the information you want-- (Scheduled, Contracts, Invoices, Transactions). View the pre-designed reports that came with Office Express to visually see how it works.

In the above example, the Parent table is the Company table and the child table is the Customers table. 

Defining a Row Filter and Sort

In the image above notice the child table, Customers, has a Filter and Sort defined. These two fields allow you to narrow the list of customer that the report will print. In the above example, the filter is "[Balance]>0" which tells the designer that only customers with balances greater than zero should print and the Sort tells the designer to sort the customers by last name.

For a detailed explanation of Row Filters click here...

The Office Express Table Relationships

Below is a diagram showing the table relationships in Office Express:

The next page will show how to place the database fields along with other objects into the designer so you can finally create a professional looking report.

To go to Designing Reports Part 3 click here...

Home | Getting Started | Table of Contents | Report Design Part 1 | Report Designer Part 3 | About Row Filters |