I.T. Discussion Community!
 Message Board
 Members Only

#### Prestwood eMagazine

January Edition
Subscribe now! It's Free!

 ► KB ► Desktop Data... ► Paradox & Ob... ► P9 Book: Pow...
From the August 2011 Issue of Prestwood eMag
 Paradox P9 Book: Power Programming:Power: Chapter 13, Crosstabs and Charts
Posted 17 years ago on 3/22/2003
Take Away: Chapter 13, "Crosstabs and Charts" from Paradox 9 Power Programming by Mike Prestwood.

#### KB100206

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.

### Using Crosstabs

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

Crosstabs and Charts

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.

One-Dimensional Summary Crosstabs

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.

Creating Crosstabs

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

Using Quick Crosstab

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

1. Open the Vendors.db table located in Paradox’s Samples folder.
2. With the table open, open the Define Crosstab dialog box by selecting Tools | Quick Design | Quick Crosstab (see below).
3. Illustration 1

4. Set the Categories option to the State/Prov field and the Summaries option to Vendor No (see the previous illustration). Note that we skipped the Column option. If you want to change this crosstab from a 1-D horizontal crosstab to a 1-D vertical crosstab, then instead of setting the Categories option to State/Prov, set the Column option to State/Prov.
5. Choose OK. Paradox calculates and generates the crosstab in a new form. From this form, you can modify the crosstab further in the Form Design window. Press F8 to move into Design mode.

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.

Two-Dimensional Summary Crosstabs

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.

Creating a Two-Dimensional Crosstab Using the Crosstab Expert

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.

Step by Step

1. Change your working directory to Paradox’s Samples directory and create a blank form with ORDERS.DB in the data model.
2. In the Form Design window, use the Crosstab tool to place a blank crosstab on the form. The Crosstab Expert starts (see the following illustration). Select the Orders table and select Next.
3. Illustration 2

If the Crosstab Expert doesn’t start, then you need to check the "Run experts when creating objects on documents" option on the Experts tab of the Preferences dialog box (see next).

Illustration 3

4. With the Month field selected, click the Add as Column Heading button. This adds the Month field to the crosstab’s columns.
5. With the Payment Method field selected, click the Add as Row button. This adds the Payment Method field to the crosstab’s rows.
6. Click Next and move the Order No to the Fields to Calculate on column. From the drop-down Calculate field, choose Count (see next). Select Next and then Finish to generate the crosstab on your form.
7. Illustration 4

8. Switch the form to View Data mode (shown next). The summary information (count of orders) appears sorted in columns by Month (alphabetical order, not numerical order) and in rows by Payment Method. This is a convenient way of analyzing the payment habits of customers over a period of time.

Illustration 5

Using Multiple Fields in a Row Heading

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.

Step by Step

1. Change your working directory to Paradox’s Samples directory and create a blank form with ORDERS.DB in the data model.
2. In the Form Design window, use the Crosstab tool to place a blank crosstab on the form. The Crosstab Expert starts. Select the Orders table and select Next.
3. Add the Payment Method field to the column heading and add both Customer No and Month to the row heading (see next).
4. Illustration 6

5. Click Next and move the Order No to the Fields to Calculate on column. From the drop-down Calculate field, choose Count (see next). Select Next and then Finish to generate the crosstab on your form.
6. Illustration 7

7. Switch the form to View Data mode (see next).

Illustration 8

Using Multiple Summary Fields

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.

Example of Using Multiple Summary Fields

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.

Step by Step

1. Change your working directory to Paradox’s Samples directory and create a blank form with ORDERS.DB in the data model.
2. In the Form Design window, use the Crosstab tool to place a blank crosstab on the form. The Crosstab Expert starts. Select the Orders table and then select Next.
4. Illustration 9

5. Click Next and move the Order No and Total Invoice fields to the Fields to Calculate on column. Change both to Sum using the Calculate field option (see below). Select Next and then Finish to generate the crosstab on your form.
6. Illustration 10

7. Switch the form to View Data mode (see below).

Illustration 11

Saving the Data of a Crosstab

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

action(DataSaveCrosstab)

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.

Step by Step

1. Open the form you created in the previous example in Design mode.
2. Name the crosstab ctOrders.
3. Add a button to the form and modify the pushButton event as follows:

method pushButton(var eventInfo Event)
const
CROSSTAB = ":priv:crosstab.db"
endConst
var
tvCrosstab TableView
endVar

ctOrders.action(DataSaveCrosstab)
tvCrosstab.open(CROSSTAB)
endMethod

1. Switch the form to View Data mode and press the button.

Crosstab Errors

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.

Multitable Crosstabs

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.

Note: You can use fields from multiple linked tables only if the link is single-valued. You can’t crosstab information from fields of multiple tables linked in multiple-valued one-to-many relationships. In a one-to-many relationship, you can crosstab information from the master or child only. You can not use fields from both.

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

Crosstabs of Detail Tables

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.

Step by Step

1. Change your working directory to Paradox’s Samples directory and create a blank form with the Customer and Orders table in the data model (link them in a one-to-many relationship from the Customer table to the Orders table). The following illustration shows what your form should look like.
2. Illustration 12

3. In the Form Design window, use the Crosstab tool to place a blank crosstab on the form. When the Crosstab Expert starts, select Cancel.
4. Right-click in the upper right of the crosstab and select Define Crosstab (see below).
5. Illustration 13

6. Add the orders.Payment Method field to the Column field, the orders.Month field to the Categories field, and summarize the orders.Total Invoice field (see below).

Illustration 14

1. Save the form as PaymentMethod.fsl (you’ll use this form in the next section). Switch the form to View Data mode (see below).

Illustration 15

Using Charts

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.

The Define Chart Dialog Box

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.

Adding a Chart to a Form

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

1. Open the PaymentMethod.fsl form from the previous example in Design Form mode.
2. Add a chart to the form (you may need to resize the page of the form). When the Chart Expert starts, select Cancel (you may want to explore the Chart Expert on your own, it makes creating charts very easy).
3. Right-click in the upper-right corner of the chart and select Define Chart (see Figure 13-2).
4. Figure 2: Opening the Define Crosstab dialog box

5. Select 1-D Summary in the Data type panel, add the orders.Payment Method to the X- Axis, and sum the orders.Total Invoice in the Y- Value (see below).

Illustration 16

1. Switch the form to View Data mode (see next).

Illustration 17

Choosing the Chart’s Data Type

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.

Specifying X-Axis Values

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.

Specifying the Y-Axis Values

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.

Note: You can’t choose the same field for X and Y values. If you’ve already chosen a field from this table to supply the X-axis values—or additional grouping values if you’re creating a two-dimensional summary chart—that field will be dimmed in the menu.

Tabular, One-Dimensional, and Two-Dimensional Charts

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.

Tabular Charts

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).

Step by Step

1. Change your working directory to Paradox’s Samples directory and create a blank form with the Customer and Orders tables in the data model (link them in a one-to-many relationship from the Customer table to the Orders table). Your form should look like the one shown here.

Illustration 18

1. In the Form Design window, add a blank chart on the form. When the Chart Expert starts, select Cancel.
2. Right-click in the upper right of the chart and select Define Chart.
3. In the Data type panel, select Tabular. Add the orders.Customer No field to the X-Axis area. Next add orders.Total Invoice, orders.Amount Paid, and orders.Balance Due to the Y-Value area (see next).
4. Illustration 19

5. Switch the form to View Data mode (see next).

Illustration 20

One-Dimensional Summary Charts

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

SPECIFYING THE X-AXIS IN A 1-D SUMMARY CHART

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

SPECIFYING THE Y-VALUE IN A 1-D SUMMARY CHART

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.

Two-Dimensional Summary Charts

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.

SPECIFYING THE X-AXIS 2-D SUMMARY CHART

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

SPECIFYING THE GROUP BY IN A 2-D SUMMARY CHART

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.

### SPECIFYING THE Y-VALUE IN A 2-D SUMMARY CHART

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.

### Summary

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.

Share a thought or comment...
 Anonymous Comment 1 of 1 I have a 3D summary chart with 8 groups.  The default markers are too large, but in the design form mode only 4 series are displayed as examples.  I can change the markers there, but how do I get the other four series to change?---Lynn Brucker Posted 9 years ago