Crosstabs and charts allow you to look at your data in various and creative ways. They allow you to focus on subsets of data. Often, you need to gather the data in your tables into a cross-tabulating summary. You can do this with either a crosstab or a chart. A crosstab shows you the raw data, while a chart displays the data graphically. This chapter delves into creating and using crosstabs and charts.

Crosstabs give you a whole new way to analyze your data. A crosstab is an object you place on a form or report that enables you to summarize data in one field by expressing it in terms of two other fields. These spreadsheet-like structures are easy for the user to understand. Unfortunately, crosstabs aren’t used enough by developers. So, keep in mind that another way to improve the look and feel of an application is to use crosstabs to show summaries of information. In today’s technological world, people are bombarded with information. Users expect to be presented with neat little packages of information.

Just like queries, crosstabs are valuable for getting at hidden information in your tables. A crosstab does the following:

• Classifies data by one or more categories
• Sorts the summarized information
• Summarizes the data within these categories
• Displays information in a spreadsheet format with rows and columns

A table’s fields often represent separate sets, or categories, of data. Cross-tabulating data focuses on summaries of data from one or more fields of a table that are divided into the categories of unique values from one or more other fields of the table. A crosstab shows this data, while a chart presents the data graphically.

A simple crosstab is one-dimensional. You analyze one type of data in light of another. For example, you can see how order amounts break down when classified by the method customers used to pay for them.

Crosstabs can be designed on either a form or report. To create a crosstab, use the Crosstab tool. The quickest way to get to the Crosstab tool is to open the table and select Tools | Quick Design | Quick Crosstab. The Quick Crosstab feature enables you to define your crosstab and places it on a form for the user to modify. The Quick Crosstab feature eliminates keystrokes for the user; however, it does not enable you to define a crosstab with a query—only with a table. Figure 13-1 shows a quick crosstab based on the VENDORS.DB table. In this case, the crosstab is a one-dimensional horizontal crosstab showing the count of vendors per state.

Figure 1: A 1-D horizontal crosstab

To create the crosstab in Figure 13-1, follow these steps.

Paradox generates the data in a hidden table in your private directory and creates a new form with a crosstab object on it with the settings you indicated. Notice Paradox brings the form up in View Data mode, as Figure 13-1 shows.

A more complex type of crosstab summarizes information by more than one category. Two-dimensional crosstabs add an additional element to the design stage. This enables you to display a cross-reference to some other piece of information. For example, the Orders table contains a Month field, whose values are month-of-sale dates. With this field and the Payment Method field, you can generate a crosstab that presents the sum of orders by payment method and by month. The numbers are spread out to reflect both the months when the orders were placed and the method used to pay for them. Values from the Payment Method field appear across the top, and values from the Month field appear down the left side.

Suppose you wish to know how many customers placed orders during each month, sorted by payment type. To do this, you need to create a two-dimensional crosstab that displays the months across the columns, payment methods down the rows, and sums the amount paid for each payment type by month.

Illustration 5

Suppose you wish to know how many orders each customer is placing, broken down by each month and separated into payment type. To do this, you need to create a two-dimensional crosstab that displays the payment type across the columns, customer and months down the rows, and counts the order number for each payment type.

Illustration 8

Whether your crosstab is one-dimensional or two-dimensional, you need to specify the field(s) that contain the data you want to summarize in the crosstab. A one-dimensional crosstab summarizes just within each of the categories represented by the column headings or just within each of the row categories. A two-dimensional crosstab summarizes by the categories of both the column and the row.

Paradox distinguishes whether the field has summary features by the field type. For example, if you select a numeric field, you can perform Sum, Count, Min, Max, and Average functions on the field values. If you select a character or date field, Paradox gives only the options of Count, Min, and Max to be summarized.

You can choose as many fields as are available and that are valid from the tables of the data model. The order in which you choose them determines the order in which the summarized data appears in each block, or cell, of the crosstab.

Suppose you wish to know how many orders and the total of those orders broken down by Payment Method and categorized per month. To do this, you need to create a two-dimensional crosstab that displays the Payment Method across the columns, the Month down the rows, and then counts the Order No for each Payment Method and sums the Amount Paid.

Illustration 11

You can save the data generated by a crosstab using the following line of code:

This save the table to a table named CROSSTAB.DB in your private directory. The following saves the data from the previous example and opens the generated table.

Paradox runs a query to calculate a crosstab’s summary information. This hidden temporary table is put in your private directory. The process might fail if the resulting answer table contains more than 1,000 fields, or if you have inadequate disk space for the query. When the crosstab fails, an empty grid appears in its place.

You can create a crosstab that takes its information from more than one table. A crosstab can draw information from any number of tables that are linked in a single-valued (1:1 or M:1) relationship. For example, if you want to view the number of items in stock by equipment class and the vendor that supplies them, you can link the Stock and Vendors tables. You’re then free to define the rows, columns, and summary fields of the crosstab using any field from either table.

Using this type of crosstab, you can look at combinations of information in a new way and analyze your buying strategies accordingly.

Suppose you have a linked multiple-valued (one-to-many) relationship and you want to see a summary crosstab of only those records in the detail table that apply to a record of the master table. For example, you can link the Customer and Orders tables. In this relationship, each customer can have many orders. You can link the two tables and create a crosstab on the detail table, Orders. You can then place the Customer No or Name field (or both) from the master table, Customer, on the form. Because of the data model, Paradox knows that the information in the crosstab applies only to the current record of the master table.

Later in this chapter, you’ll see a crosstab of the detail table Orders that sums the Total Invoice field by Payment Method and by Month for each Customer. This crosstab is derived from the fields of the detail table, Orders, that summarizes information by customer; that is, by the Name field from the master table, Customer. As you scroll through the Customer table, the crosstab is updated to show each customer’s order information.

The following example will not function as stated above. The crosstab will only contain information about the first customer listed in the customer table and it is not scrollable.

Illustration 14

Illustration 15

Charts add visual excitement and flair to your forms and reports. Using a chart to represent data visually is appealing to the eye and brain. You can use many types of charts, including line, bar, and three-dimensional pie charts. You can even combine line and marker charts. Charts change as data is changed. They are wonderful visual aids. Most database users agree that charts are not used enough, especially for analyzing data.

You have heard that a picture is worth a thousand words. It’s true. If you are doing a business application, you find this even more true, because sometimes quick decisions must be made from a glance at a form or report.

When you create a chart, Paradox first cross-tabulates the data before it generates the visual representation of it. You should understand how crosstabs work before you work with charts. Examine the following terms associated with charting:

• Title/subtitle These properties of the chart are used to explain what the chart represents. Limited space is available, so keep it to the point.
• Data series This comprises the charted values, actual numbers.
• Legend This is a text description of the Data Series numbers.
• Axis When charting you must define a "versus." The X- and Y- axes are essentially X versus Y on a chart, in which case the X- axis is represented by a set of numbers and the Y- axis is represented by a number. The axis has labels, scales, and titles.
• Chart frame This is a frame that surrounds the chart data.
• Chart The chart itself objects sit on the chart object.

Like the Define Crosstab dialog box, the Define Chart dialog box enables the user to choose the type of chart, define the Chart field X- and Y- Values, and perform 2-D summaries on fields. The Define Chart dialog box is displayed when you use the Quick Chart, or if you use the Property Inspector on the Crosstab object, in which case there’s a Define Chart option. For those who like to do everything manually, you can avoid using this dialog box. Paradox helps create charts quicker when you are not completely sure how to define the field values with the Define Chart dialog box. In the Define Chart dialog box, you can do the following:

• Choose which type of data you’re charting—tabular, one-dimensional summary, or two-dimensional summary.
• Specify the field whose values you want to use as X-axis values.
• Specify the field(s) whose values you want to chart against the Y axis (the different series of the chart).

• If you’re creating a two-dimensional summary chart, specify an additional field whose values you want to group the charted summary data by.

• If you’re creating either a one-dimensional or two-dimensional summary chart, specify the type of summary operation to perform on each Y value field you choose.

The "Fields used in" options are similar to those of the crosstabs. Choose the radio button of choice and select the field from the table drop-down box. Both the X- and Y- Values of the "Field used in" option must be defined to continue to the Define Chart dialog box. What makes this dialog box different from the Define Crosstab dialog box is the Data type options. The Data type options enable you to define charts differently. Descriptions of each of the data types are in the following sections.

In this next example, we will add a pie chart to the PaymentMethod.fsl form you created in the last example.

Illustration 16

Illustration 17

In the lower-left corner of the Define Chart dialog box, select the data type of the chart:

• Tabular (the default)
• 1-D Summary
• 2-D Summary

The "Field used in" panel on the right side of the dialog box changes, depending on which data type you choose.

When you first open the Define Chart dialog box, X- Axis is selected by default in the "Field used in" area. With X Axis selected, choose a field from the drop-down menu of the table whose unique values you want to be the X-axis values. You can only choose one field to supply X-axis values for all three data types—tabular, one-dimensional summary, and two-dimensional summary.

With Y-Value selected, choose the field(s) from the drop-down menu of the table whose values you want to define and be charted according to the Y-axis measure.

Understanding the three data types is the key to understanding charts. You can chart three types of data: tabular, one-dimensional, and two-dimensional. Once you understand the differences between these data types, charting specific data becomes easier.

A tabular chart measures the values in one numeric field within each category represented by the unique values in another field. The Tabular Chart type enables the designer to plot the data in the X-field value versus the Y-field value. There are no Summaries on any of the fields. The tabular format enables you to add many fields to the Y-field value area so that you can compare other field values side by side. In a tabular format, the Y-field values must be numeric. Paradox grays out all other fields that are not numeric. You must specify the following for a tabular chart:

• One field for X-axis values.
• One or more fields for the Y-value (each field becomes a series in the chart).

Illustration 18

Illustration 20

The 1-D (one-dimensional) Summary is similar to the tabular format, except you can summarize on the Y-Field value, which cannot be done in the Tabular format. In addition to the default value of the Sum operator, you can perform additional functions on the Y-field value, such as Count, Min, Max, and Average. Like the Tabular data type, you can select multiple fields to summarize on. Similar to what you learned about the Define Crosstab dialog box, you can remove fields from the Y-Value area by highlighting the field and choosing the Remove Field button. You must include the following when defining a one-dimensional summary chart:

• One field for the X-values
• One or more summary fields for the Y-value(s)
• The summary operation to perform on each Y-field

You can only choose one field to supply the X-value for all three data types: tabular, one-dimensional summary, and two-dimensional summary.

If the data type of the chart is one-dimensional summary, you can choose as many of the table’s available and valid fields to define the Y-value. When you choose a Y-value field, Paradox automatically couples it with a default summary operation.

If you don’t want the default summary operation Paradox chooses by default, select the summary field in the Y-Value panel whose summary operation you want to change. Then, choose one of the available summary operations for that field from the Summary drop-down list.

In the previous examples, you’ve learned about the basics behind building a chart in a 1-D view, so take a look at designing and running a 2-D chart. You must define the following for a two-dimensional summary chart:

• One field for the X-values
• One field to group by (each unique value will be a series)
• One summary field for the Y-value
• The summary to perform on the Y-value

The 2-D (two-dimensional) summary has the same features as the 1-D summary with the addition of a Group By radio button. The Group By option allows a second grouping addition to the X-axis of the charted data. The Group By option only allows one field selection; if the 2-D summary data type has been selected, Paradox requires a field value to be defined. To define the group field, select one of the available fields from the table drop-down button.

In addition to these options, the Define Chart dialog box includes the Data Modeling button in the top-left corner. If you select this option, Paradox brings up the Data Model dialog box and enables you to redefine your data model. As we mentioned earlier, you can avoid the Define Chart dialog box by using the Property Inspector and manually defining the field values. You will probably save time, though, designing your crosstab if you use the Define Chart dialog box. Paradox tries to help steer you in the right direction if you are unsure of the defining of values.

You can only choose one field to supply X-axis values for all three data types—tabular, one-dimensional summary, and two-dimensional summary.

While the data type of the chart is two-dimensional summary, the title area’s menu contains Define Group. This choice isn’t on this menu when the data type is tabular or one-dimensional. If you’re creating a two-dimensional summary chart, choose Define Group to choose one field whose unique values you want to group the summary data by. The summary data is also grouped by the categories of the X-axis values.

If the data type of the chart is two-dimensional summary, you can choose any one of a table’s available and valid fields to group the summary data by. The data is also grouped by the X-axis categories. With Grouped By selected, choose from the drop-down menu of the table the field you want to group the summary data by.

If the data type of the chart is two-dimensional summary, you can choose any one of a table’s available and valid fields to define the Y-axis. When you choose the Y-axis field, Paradox automatically couples it with a default summary operation. See the previous section for a description of the default summary operations Paradox chooses for specific field types and how to change the summary operation.

While the data type of the chart is two-dimensional summary, you can only choose one field for the single series allowed for this data type. The Y-axis menu contains Define Y-Value. Choose Define Y-Value to choose one field whose values you want to define and be charted according to the Y-axis.

Today’s users expect applications to be sophisticated. Using color wisely, adding charts, and sticking with the 32-bit Windows paradigm will go along way to creating easy-to-use applications. Adding charts and crosstabs to your business database applications will give them true sophistication.