The Crosstab Workshop wizard is a tool for two dimensional summarizing, analyzing and charting large amounts of data. The workshop displays a two dimensional grid summarizing the information in a database by multiple categories, for example spending by vendor vs. quarter, as shown here:

Opening the Crosstab Workshop

To open the Crosstab Workshop, click on the Summary tool and choose Crosstab Table & Chart from the pop-up menu.

You can also open the Crosstab Workshop from the menu bar by choosing Field>Analyze>Crosstab Table & Chart.

Note: You can open more than one Crosstab Workshop window at the same time. Each * Crosstab Workshop* window can display a separate summary, making it easy to put different summaries side-by-side for comparisons. To open another * Crosstab Workshop* window either click on the Summary tool again, or choose New Window from the Window menu.

Crosstab Groups

Use the Group by (Side) and Group by (Top) pop-up menus to specify how the database should be divided into groups. This menu lists all the non-numeric fields in the database. If a field contains dates, you are given the option to group by different calendar periods (month, quarter, year, etc.)

Note: If you do want to group by a numeric field, open the Preferences window and enable the Allow grouping on numeric fields option.

When you change either of these pop-ups, the crosstab summary updates immediately.

To swap the side and top categories, press the double arrow icon.

Crosstab Computations

Use the Value pop-up menu to choose whether the crosstab body contains counts or totals. If totals, you will need to choose which field will be included in the total.

Revealing the Underlying Data

To reveal the underlying raw data behind any crosstab cell, simply right click on the cell and choose Select Original Data.

Refreshing the Crosstab when Data Changes

To refresh the crosstab with the latest changes to the database, click on the refresh tool.

Analyzing a Data Subset

The Crosstab Workshop normally analyzes all the records in the entire database, whether they are visible or not. Sometimes, however, that’s not what you want. This sample checkbook database, for example, contains items other than checks, which you probably don’t want to include in the analysis.

One solution is to use the Find/Select Dialog to select only the checks, and then to choose Selected Records from the Data pop-up menu.

A more permanent solution is to use the Find/Select Dialog to set up a favorite search. See Favorite Searches to learn how to set these up. I’ve set up several favorite searches in this checkbook database, including Debit, which selects all records with a debit greater than zero (in other words, all checks, but no deposits).

To use any of these favorite searches in the Crosstab Workshop, simply select it from the Data popup menu.

From now on, the Crosstab Workshop will always analyze based on the data in this favorite search, no matter what data is currently selected in the database itself. The workshop will also take this search into account when revealing underlying data. Only records that match the favorite will be revealed.

Saving a Favorite Crosstab

Once you’ve set up a crosstab just the way you want it, you can save it for re-use later. To save a favorite, click on the star and choose Add to Favorites. Give the crosstab configuration a name and press RETURN or ENTER to have that name replace the default name NEW_FAVORITE in the listing, then press Done. (Note: To permanently save this favorite, you must save the underlying database.)

To instantly pull up a previously saved favorite, just click on the star and select it from the pop-up menu.

To delete a favorite crosstab, click on the star and choose Edit Favorites. Click on the minus button to delete any favorite.

To rename a favorite crosstab, click on the star and choose Edit Favorites. Click on the favorite you want to change, then type in the new name, then press the Return key.

You can’t change the criteria used by a favorite crosstab. If you need to make a change, simply delete the favorite and then set it up again.

Alternate Database

When the Crosstab Workshop is opened it is connected with the database that was open. You can use the Database menu to switch to any open database. Remember, you can open multiple Crosstab Workshop windows, and each one can be a different database if you wish.

Chart Display

In addition to tabular display, the Crosstab Workshop can also display various kinds of charts. Simply choose the type of chart you want to see from the Display popup menu.

The charts are interactive – when you hover over them a label appears showing the data value.

You can turn on spike lines to make it easier to see where each value falls on the axes.

You can zoom in and out, pan, and reset the zoom.

The other tools in the chart toolbar are not operational.

The Crosstab Menu

The Crosstab menu performs various utility functions, including refreshing the crosstab values and exporting the crosstab.

Refresh Crosstab Data

To refresh the crosstab with the latest changes to the database, choose this menu option or click on the refresh tool.

Reset Crosstab Specifications

This command resets all crosstab options to their defaults, making it easy to start over with a new crosstab analysis.

Make New Database with Crosstab Data

This command exports the crosstab analysis into a new, untitled database. You can then save this analysis data separately, export it to a text file, or do whatever you want with it.

Although the exported database is separate from the original database, the Crosstab Workshop creates two procedures in a special Crosstab menu that maintain a link to the original database. Using these items you can go back to view the original data that went into a crosstab, and you can also refresh this analysis database with any changes to the data in the original database.

Make New Crosstab Table Form

This command creates a new form in the original database, and creates a Text List Object in that form that displays the current crosstab analysis. In other words, it makes this analysis a permanent part of the database. From that point forward, you can view this analysis simply by opening that form, you don’t have to re-open the Crosstab Workshop. You can manipulate this form like any other form – change the object properties, add other objects, etc.

The crosstab( function is used to actually generate the summarized data. You can customize the formula Panorama generates to your specific needs.

To learn more about the options available, see the crosstab( function.

Linking Back to the Original Data

When creating the Text List Object, Panorama adds code to link the object back to the original data.

With this code in place, right clicking on a cell (in Data Mode) will trigger a pop-up menu that allows you to select the original data, or to refresh the crosstab display.

To learn more about linking back to the original data, see Linking the Crosstab to the Original Database in the crosstab( function.

Make New Crosstab Chart Form

If the Crosstab Workshop is currently displaying a chart, the Make New Crosstab Table Form command changes into the Make New Crosstab Chart Form command. This command creates a new form in the original database, and creates a Web Browser Object in that form with a chart( function, making this chart a permanent part of the database. From that point forward, you can view this chart simply by opening that form, you don’t have to re-open the Summary Workshop. You can manipulate this form like any other form – change the object properties, add other objects, etc.

The chart( and crosstab( functions are used to actually generate the summarized data. You can customize the formula Panorama generates to your specific needs.

You can change the the formula to modify the chart.

To learn more about the options available, see the chart( and crosstab( functions.

Embellishing the Crosstab Table/Chart Form

For simplicity, the Crosstab Workshop creates a new form with a single crosstab table or chart. But you should consider that just a starting point. You can modify the created object, add other objects, even copy the created object to another form. Here is an example with both a crosstab table and a chart combined onto a single form.

You can also put multiple tables on a single form, multiple charts, any combinations you want.

Crosstab Table Blueprint

This command displays a blueprint of the form object used to display the current crosstab table or chart. See Form Object Blueprint to learn more about these blueprints.


See Also


History

VersionStatusNotes
10.1NewNew in this version.