Using crosstabs

A crosstab is a data analysis tool that summarizes (cross-tabulates) information according to one or more categories. A crosstab allows you to classify data by one or more categories, summarize and sort the data, and then display it in a spreadsheet-like format.

In this section, you'll learn about

  creating crosstabs
  
  defining crosstabs
  
  modifying crosstabs
  
  running and saving crosstabs
  
For more information about crosstabs, see "Reference: Using Crosstabs."

Creating crosstabs

You can create a crosstab on either a form or a report. 

For more information about crosstabs, see "Crosstab basics".

To create a crosstab on a form or report

  With a form or report open in the design window, click Insert  Crosstab.
  
An empty crosstab object appears with undefined fields in the row header, column header, and first summary area. You can size and shape the crosstab as desired. For more information on sizing crosstabs, see Sizing and shaping design objects.

Note

  If you want to see the data from your tables placed in the crosstab you have created, you will have to run the crosstab.
  
Tip

  You can also insert a crosstab by clicking the Crosstab tool.and dragging in the form or report.
  
Defining crosstabs

When you create a new crosstab object on a form or report, the first column field, first row field, and first summary field are undefined. You can define the fields used in a crosstab at any time. This involves specifying the fields to use for columns headings, row headings(categories), as well as the fields on which to perform summary operations such as calculating the average. You can later change or remove any of the fields used in a crosstab.

The only requirements when defining crosstabs are that the tables being examined must be in the data model, and that the categories being compared are either linked in the data model or are linked via referential integrity.

To define a crosstab

1	With a form or report open in the design window, right-click the upper left corner of the crosstab object, and click Define crosstab.

2	Enable the Column option. 

3	From the table list box, choose the fields you want to use as column headings across the top of the crosstab.

4	Enable the Categories option. 

5	From the table list box, choose the fields you want to use as row headings.

6	Enable the Summaries option.

7	 Choose the field from the table list box on which you want to perform a summary operation. 

Notes

  The total number of category fields plus the number of columns created for a crosstab cannot exceed 254.
  
To define the fields for a crosstab

1	With a form or report open in the design window, right-click a crosstab field, and click Define field.

2	Choose a field from a table list box.

To specify column headings for a crosstab

1	With a form or report open in the design window, click a crosstab column field, right-click the field, and click Define field.

2	Choose a field to be used as column headings from a table list box.

Note

  For one-dimensional crosstabs you can choose only one field to supply column-heading values. For more information about one-dimensional crosstabs, see "Crosstab basics"
  
To specify row headings (categories) for a crosstab

1	With a form or report open in the design window, click a crosstab row field, right-click the field, and click Define field.

2	Choose a field to be used as row headings from a table list box.

Notes

  For two-dimensional crosstabs, as long as you have at least one field specified for column headings, you can choose as many fields as are available and that are valid in the tables of the data model for the row categories. If you are creating a horizontal one-dimensional crosstab, do not choose a field for row categories.
  
  You cannot use the same field for column headings and row categories. If you have already chosen a field from a table to supply the column heading values, that field will be grayed.
  
Tip

  If you are creating a vertical one-dimensional crosstab, do not choose a field for column-heading values. For more information on one-dimensional crosstabs, see "Crosstab basics".
  
To specify summary fields for a crosstab

1	With a form or report open in the design window, click a crosstab field, right-click the field, and click Define field.

2	From the table list box, choose a field on which you want to perform a summary operation. 

3	Choose a summary operation from the Summary list box.

Notes

  You cannot choose the same field to summarize as you have chosen for column headings or for row categories. If you have already chosen fields from the table to supply column heading and row category values, those fields will be dimmed.
  
  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 the crosstab.
  
To remove a field from a crosstab

1	With a form or report open in the design window, right-click the upper-left corner of the crosstab object, and click Define crosstab.

2	Choose a field from one of the following boxes.

  Column
  
  Categories
  
  Summaries
  
3	Click Remove field.

Modifying crosstabs

You can change the size of  a crosstab as well as the appearance of the fields, columns, rows, and summary areas of a crosstab. You can also change the order in which category and summary fields display.

To change the appearance of a crosstab

1	With a form or report open in the design window, select a crosstab. 

2	Right-click the appropriate area of the crosstab, and click Properties.

3	Modify any of the settings.

Note.

  For more information about the properties available to crosstabs, see the "Object property reference"
  
To resize a crosstab

1	With a form or report open in the design window, select a crosstab.

2	Drag the crosstab's borders to the required size.

You can also	

Resize a crosstab's column area	Drag the grid lines surrounding the selected crosstab.

Resize a crosstab's row area	Drag the borders of the row area in the selected crosstab.

To rearrange category and summary fields in a crosstab

1	With a form or report open in the design window, right-click the upper-left corner of the crosstab object and click Define crosstab.

2	Use the Change order arrows at the bottom of the Field used in area.

Tip

  You can rearrange the categories or summaries in the Form Design and Report Design windows by dragging them to a different location.
  
Running and saving crosstabs

After you havefinished creating a crosstab, you can then view the data it creates by running your form or report. You can also save the crosstab data to its own table.

To run a crosstab

  With a form or report in the design window, click View, and click one of the following.
  
  Run form
  
  Run report
  
Note

  Paradox runs a query to calculate a crosstab's summary information. The process may fail if the resulting Answer table contains too many fields or if you have inadequate disk space for the query. When the crosstab fails, an empty grid displays in its place.
  
To save crosstab data to a table

1	Save a form containing a crosstab.

2	Click Edit  Save crosstab 

3	Type the name you want to give to the new table.

Reference: Using Crosstabs

This section contains reference information pertaining to crosstabs.

Crosstab basics

Crosstabs may be one dimensional or two dimensional.

One-dimensional crosstabs

When you create a one-dimensional crosstab, you can analyze one type of data in relation to another.

The Orders table has a Payment Method field. The crosstab counts the number of orders placed using each of the seven possible payment methods. In this case, Payment Method is the category of information, and the calculation Count (ORDER.Order No) provides the data for each category.

You can arrange the display of information horizontally or vertically. Paradox can usually calculate and generate a vertical one-dimensional crosstab faster than a horizontal one.

Two-dimensional crosstabs

A two-dimensional crosstab summarizes information by more than one category.

To create a two-dimensional crosstab, indicate two category fields (the Column and Categories boxes in the Define crosstab dialog box), and a field whose data you want to summarize (entered in the Summaries box of the Define Crosstab dialog box). In this case, using the Orders table, you could show the count of orders (the summary appearing in the cells of the crosstab object) placed for each payment method and each month (the categories appearing across the top and down the left side of the crosstab object). The data are two-dimensional because they reflect both the month in which the orders were placed and the method used to pay for the orders.

The summary information (count of Order No) displays in the crosstab cells sorted in rows by Month and in columns by Payment Method. To find the number of orders in a given month, you would find the intersection of the appropriate row and column. For example, the number 4 in the first column of cells indicates that four orders were placed in the month of December by customers who used an American Express charge card to pay for the orders. This is a convenient way to analyze the buying habits of customers over time.

You can also use charts to analyze data contained in two or more tables. For more information, see "Multi-table charts."

Multi-table crosstabs

To analyze (cross-tabulate) data contained in two or more tables, the tables must be linked. A crosstab can draw information from any number of tables that are linked in a single-value (oneone or manyone) relationship. Before you create a multi-table crosstab of a linked one-to-one relationship, you must define the relationship with a data model.

For example, 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 using any field from either table.

The following figure shows a multi-table crosstab that uses the Vendors Name field from the Vendors table and the Equipment Class field from the Stock table.

This figure displays:

  Summary values show how many pieces of each type of stock came from each vendor.
  
  Row titles show values from the Vendor Name field of the Vendors table.
  
  Column headings show values from the Equipment Class field of the Stock table.
  
You can combine fields from linked tables in the same crosstab only if the link is single-valued. You cannot cross-tabulate information from combined fields of tables linked in multi-value (onemany) relationships. You can cross-tabulate information from the detail table only in a onemany relationship.

Crosstabs of detail tables

Suppose you have a linked multi-valued (one-to-many) relationship and you want a summary crosstab of only those records in the detail table that apply to a specific record in the master table.

For example, you might want to define a crosstab in the detail table Orders that sums the Total Invoice field by Payment Method and by Month for each customer in the Customer table.

In the relationship between the Customer and Orders tables, each customer can have many orders. You can link the two tables and create a crosstab in the detail table, Orders. You can then place the Customer No or Name field (or both) from the master table, Customer, on the form. Paradox knows from the data model that the information in the crosstab applies only to the current record of the master table. In this example, the Name field at the top comes from the Customer table. As you scroll through Customer, the crosstab updates to show each customer's order information. 




