The Cross Tabs Dialog

The cross tab management dialog box presents you with the list of available cross tabs that you may select to work on or the option to delete or create a new cross tab.

To edit an existing cross tab select it from the list and press the Edit button. To remove a cross tab, select it from the list and press the Delete button.

To create a new cross tab to begin work on, press the New button. It will appear on the cross tab list as an Empty entry. Press Edit to define its contents.

Cross Tab Properties

The cross tab properties sheet consists of four main sections:

• The Field List.

• The Cross Tab Column box.

• The Cross Tab Row box.

• The Cross Tab Summarised Values Box.

Type (Field List)

Selects the type of field list. Two choices are provided: Database or Dictionary. The Database list shows the available fields according to the database files selected on the Links tab. The field list will be empty until one or more databases have been linked to the cross tab.

The Dictionary field list relates to any pre-defined dictionary attached to the current form. For information on attaching a dictionary to a form see: Form Preferences.

Sort

Controls the sort order of the cross tab. Rows and columns may be sorted in ascending or descending sequence. For example, if you are creating a cross tab for sales by state, and the states are the columns, sorting in ascending sequence would create this sequence:

ACT NSW NT QLD TAS VIC

Sorting in descending sequence would create columns with this ordering:

VIC TAS QLD NT NSW ACT

Columns

The field that will be used to make up the columns of the report. For example, the field:

Invoice->State

would produce a series of columns based on the contents of the customer invoice State field.

To add a column to the cross tab select the field from the field list, click on the Columns list box and press the button next to it. To remove a field from the list, select the field you wish to remove in the Columns box and press the button.

Due to the limits of the physical width of a printed page, you should select columns fields that have a limited range of values. The field Invoice->State is a good example since in Australia it should only contain one of 6 different possible values. Other examples might include quarterly results (only 4 per year), monthly results (only 12 per year), stock locations (assuming a limited number of warehouses in use) and so on. See: Cross Tab Concepts for more information on designing successful cross tabs.

You may add more than one column field to the Columns list. The first entry will take priority when sorting. For example, if your first column is a date (grouped by quarterly periods perhaps) and the second field is a stock group, then the column arrangement on the cross tab would be:

Rows

The field that will be used to make up the rows of the report. For example, the field Stock->Name would produce a series of rows based on the contents of the stock Name (product code) field.

To add a row to the cross tab select the field from the field list, click on the Rows list box and press the button next to it.

To remove a field from the list, select the field you wish to remove in the Rows box and press the button.

You may add more than one row field to the Rows list. The first entry will take priority when sorting. For example, if your first row is a state and the second field is a product code, then the row arrangement on the cross tab would be:

Summarised Values

The field that will be used to make up the summarised values of the report. This should be a numeric field. For example, the field Invoice->Invtot would produce total values for a range of transactions based on the contents of the customer invoice Invtot (invoice total) field.

To add a summarised value to the cross tab select the field from the field list, click on the Summarised Values list box and press the button next to it. To remove a field from the list, select the field you wish to remove in the Summarised Values box and press the button.

You may add more than one summarised field to the Summarised Values list. A separate row will be created for each summarised value.

For example, on a cross tab for a stock report, the first user field might be the stock quantity multiplied by the stock cost price. The total being the value of the stock. The second summarised field value might be the stock quantity. On a cross tab report a pair of rows would be created. The first listing stock value and the one below it the stock quantity.

Field...

Allows you to define the characteristics of a column, row or summarised value. This is also useful when no specific field from the field list expresses the required calculation. First select the field you wish to edit and press the Field... button to define the type of calculation and exact fields involved in the calculation. For more information on this topic see: Cross Tab Fields.

Cross Tab Rules

Cross tab rules are just like normal body rules. They are used to filter out unwanted information. For example, if you wanted to produce a cross tab based on the monthly performance of a range of products over the course of a year, it would be important to specify the start and end dates (the 1 year period) of the range to include. This would be done by defining an appropriate rule in the rules dialog.

For more information on defining rules see: Body Rules.

Cross Tab Links

Use the Cross Tab Link dialog to define the databases required by the cross tab. The databases linked to the cross tab determine what field items appear on the cross tab field list. For more information on linking see: Links.

Using the Completed Cross Tab

A cross tab is generated just before printing or print previewing of the form takes place. It first checks to see if any of the database tables linked to the cross tab have been changed and are later than the date of the last time the cross tab database was built. If the cross tab is out of date, it is regenerated. If no data has changed in any of the linked tables, the existing cross tab, if any, is used.

1. You must print-preview your report at least once, before you begin arranging cross-tab fields.

Once a cross tab is built it can be attached to a form like any other form body. The cross tab utility creates two databases: the first holds the column information of the cross tab. The second holds the actual cross tab data.

2. Begin by creating two new bodies. Select Edit|Body Management from the menu and click on New twice.

To place the cross tab column list on a form, edit the new bodies you have attached to the form and on the Link Tab Main field enter:

"CTAB=1"

On the second new body, on its Link Tab Main field enter:

"XTAB=1"

It is very important that you don't forget to put the quote (") marks around the cross tab reference codes.

Make sure that the body style of the second body is Fixed Width/Height - Form Style. Remember: a cross tab is a type of report.

3. Then begin to arrange the fields onto the form just as you would a normal body. Click on the button to begin inserting fields.

Be sure to change the Body to the first of the new bodies you have created.

4. When positioning the bodies, place the column cross tab body above the cross tab data body. The column body acts as the column headings of the cross tab data body.

Multiple Cross-Tabs

If you will be creating more than one cross tab and displaying it on the same form, create further bodies and increment the cross tab numbers. For example, the next cross tab column list would be:

"CTAB=2"

and the next data cross tab would be:

"XTAB=2"

CAPITAL Visual Builder comes with numerous example cross tab forms that should be studied before you begin creating your own cross tab databases.