A crosstab is simply a table with categories across the top and down the left, with numbers in the middle and totals across the bottom and down the right. The word crosstab is short for cross tabulation, referring to the criss-cross way that totals are tabulated both across and down. Probably the most common example of a crosstab is a budget, with months or years across the top, and spending categories down the left.
Before Panorama became available, crosstabs were usually created using a spreadsheet. Spreadsheets are perfect for totalling the rows and columns in the crosstab table. Unfortunately, spreadsheets cannot help with the really tedious part of creating a crosstab table—taking the raw data, categorizing it, and converting it into the crosstab table format. With a spreadsheet, this tedious number crunching must be done by hand.
Panorama automates the entire crosstab process from start to finish. Starting with raw data (a checkbook database, for example), Panorama divides the data into categories and automatically creates and calculates the entire crosstab table. When the raw data changes, the entire process can be repeated with a single mouse click. A simple dialog sets up the whole process.
Panorama can also work a crosstab backwards, allowing you to locate the raw data associated with any crosstab value. For example, if the crosstab table shows that July’s advertising expenditures seem a bit high, simply click on that value and press the Select Original Data tool. The individual data records for July advertising will appear.
Creating and Setting Up a New Crosstab
New crosstab views are created using the View menu. Choose New Crosstab from the menu. Then you must
give the new crosstab view a name (up to 25 characters) and press Ok to create the new crosstab.
When a new crosstab view is created, the Crosstab Design dialog box automatically appears. This dialog allows you to specify the category and tabulation fields (see previous section) and to specify what type of calculation (total or count) to use.
To use the Crosstab Design dialog you copy fields from the list on the left over to the list on the right. The left side lists all the fields in the database. The right side lists the fields in the crosstab. The first two fields copied to the right become the two category fields. The third field becomes the tabulation field. As you build the crosstab, a miniature schematic diagram of the crosstab appears in the lower right hand corner of the dialog.
Let’s walk through the creation of a crosstab like the one shown at the top of this page. First, click on the Date field and copy it to the right. (Hint: To copy the field either press the »Copy» button or double click on the field name.)
As you can see, the Date field now appears in the list of Crosstab fields. It also appears in the preview of the final crosstab, showing that the Date will appear across the top of the crosstab.
Since we want the date to be grouped by month, select Month from the Date Period pop-up menu. (If you forget to do this now you can always go back and change it later.)
Next, copy the Category field into the crosstab. It will appear in the list and on the left side of the mini-diagram, showing that the category will appear on the left side of the crosstab.
Now copy the Debit field into the crosstab. You’ll also need to click on the Total radio button, since we want to calculate sums of the checks, not counts of the checks.
Once you’ve specified the category and tabulation fields, press Ok to actually calculate the crosstab. Depending on the complexity of the database, there may be a delay of seconds or even minutes as the crosstab is calculated. When the calculations are finished, the new crosstab table will appear. It will look something like this.
Re-Calculating a Crosstab
Crosstabs do not automatically update when the main database changes. This is because of the time it takes to recalculate the crosstab. If you change the main database (or re-download from Rails) and want to re-calculate a crosstab, press the Calculate Crosstab tool.
Adjusting Crosstab Column Widths
When a new crosstab is created, Panorama tries to assign an appropriate width for each column. You can adjust these column widths the same way you would adjust the column widths in the data sheet. Move the cursor over the column titles and drag left or right to adjust the width.
Whenever the crosstab is recalculated, Panorama automatically resets the width of every column except for the first two. The third, fourth, fifth and all additional columns are all set to the same width as the second column.
Selecting Original Data
Using the Select Original Data tool, Panorama can locate the raw data behind any value in the crosstab table. To do this you must have a regular database window open in addition to the crosstab window, usually the data sheet window. To select the original data, first click on the crosstab value you are interested in. Then click on the Select Original Data tool, The original data is selected and will appear in the data sheet or other database window. For example, you could click on the Apr 08 Office Supplies cell, then choose Select Original Data.
In the data sheet the raw data backing up this crosstab cell will be selected - in this case the seven checks written for Office Supplies in April of 2008.
You can select the original data for any value in the crosstab. If you click on a cell in the first or last column of the crosstab, the Select Original Data tool will select all the data associated with the entire row. For example, you could click on the Fixed Assets cell to see all of the fixed assets expenditures in the entire database, for all time periods.
When you choose the Select Original Data tool, the data sheet will show all of the checks written for Fixed
Assets in every month.
If you click on a cell in the bottom row of the crosstab, the Select Original Data tool will select all of the data
for the entire column.
Learn More About Crosstabs
To learn more about crosstabs see Chapter 11 of Panorama Essentials or the Panorama Handbook.