Inserting Crosstab Objects

A crosstab object can be inserted in the tool window "Report structure"

or select Objects > Insert > Crosstab (depends of application).

This object is used to analyze and display diferent kinds of data in several dimensions. You can use it, for example, to investigate the changes in turnover per year and region and analyze sales by unit and customer, then show the totals by quarter and year. There are numerous layout options available to you.

When you are creating a new crosstab, a wizard is available to you to guide you through the three tabs.

Example: Customer sales per sales period and employee

4.16.1 Properties

See chapter "4.13.3 Characteristics of Elements" in the report container.

4.16.2 Axis definition (Grouping)

In the "Axis Definition" tab, you can set the groupings for the rows and columns. For example, to create a statistics about the turnover by year and country, select "Country" as the group for the rows and "Year" as the group

for the columns. For the contents of the result cells, select the sum over the turnover.

▪ With the "New" button, you can add a group for rows or columns.

▪ With the "Properties" button you can edit the selected group, with the button "Delete" you can delete the selected group.

▪ You can add as many groups (layers) as you need, e.g. a group "Year"

and another group "Quarter". With the arrow buttons, the order can be changed. The lowest row or column is the innermost group.

▪ In the field "Value of the result cells" the cell contents are defined. Here you can select with the button "Edit group result formula" in the dialog

"Cell contents" an aggregate function for the content, e.g. sum or quantity. This is the formula that is set into the "value" property of each content cell. In the lower part of the dialog "Cell contents", the selected result function is displayed.

4.16.3 Cell definition (Contents)

In the "Cell Definition" tab, the properties of the diferent cells can be edited.

You can select the cells directly in the sketch in the top part of the dialog and can then edit their properties. To select more than one cell, hold the

CTRL key and selector drag a frame around the cells with the cursor.

See also Chapter "Common Object Properties".

Property Description Value Description

Value Formula for the cell value. This is evaluated by the Crosstab.Cells...

functions.

Formula Formula dialog

Displayed contents

Text to be displayed in the cell, so it can be diferent from the property "Value".

Formula Formula dialog Link Link that is opened when clicked (only in

preview, PDF and HTML export). Link Link Formula dialog Rotation Rotates the object anti-clockwise. With

this function you can, for example, turn column titles by 90°.

0

alignment Vertical alignment of the contents in the

available space. 0

(with Text) The text alignment. Decimal means numbers are aligned by their decimal points.

position Position of the decimal point (only applicable in decimal alignment, negative means

width Sets the minimum width of the cell. Numbe r

Formula Formula dialog Minimum

height

Sets the minimum height of the cell. Numbe r

Formula Formula dialog

4.16.4 Layout Options and Page Break Behavior

See also Chapter "Common Object Properties".

Property Description Value Description

PDF index

text Text for the PDF index. Formula Formula

dialog Link Link that is opened when clicked (only in

preview, PDF and HTML export).

Link Formula dialog

Minimum

size Sets by how much the crosstab can be shrunk in order to avoid a horizontal page break. 50=that it can be shrunk up to 50% to avoid a page break; 100=retain original size.

Numbe

r Formula

dialog

Minimum

height Sets how much height should be available to an object. If less space is available, a page break is triggered.

Numbe

r Formula

dialog

Columns Controls the column properties at a page

break. Formula Formula

dialog Columns Repeat

Labels Sets whether the column headers are to be repeated at a page break.

Level Sets the optimal page break level. "0" represents the innermost group (the bottom row in the

"Columns" field of the "Axis definition" tab).

Numbe r

Formula Formula dialog

Force Forces page break after

every corresponding group. True

True: If the crosstab contents exceed the available space, the remaining contents will be printed to shadow pages.

Shadow pages are additional, automatically inserted pages, which are not counted as pages (receives no page number).

False: The remaining contents will be printed below the table. (see example above).

Distance

Before Distance to the preceding table (void if table starts on page top).

Numbe r

Formula Formula dialog

Rows Controls the row properties

at a page break. Formula Formula dialog Repeat

Labels Sets whether the row headings are repeated after a page break.

Level Sets the optimal page break level. "0" represents the innermost group (the bottom row in the "Rows"

field of the "Axis definition"

tab).

Force Forces page break after every corresponding group.

4.16.5 Crosstab Functions

There are several crosstab functions to access to the values of each data record.

▪ Crosstab.Cells.Avg() returns the average of the cell contents. Only available in crosstab objects.

▪ Crosstab.Cells.Max() returns the largest value of the cell contents. Only available in crosstab objects.

▪ Crosstab.Cells.Min() returns the smallest value of the cell contents.

Only available in crosstab objects.

▪ Crosstab.Col$() returns the column header for the cell currently being output. Only available in crosstab objects.

▪ Crosstab.Col() returns the column index for the cell currently being output. Only available in crosstab objects.

▪ Crosstab.Row$() returns the row header for the cell currently being output. Only available in crosstab objects.

▪ Crosstab.Row() returns the row index for the cell currently being output. Only available in crosstab objects.

▪ Total() can be used for computations over all cells away. Otherwise calculations always run over all values, which concern the respective cell

Additional information about this function can be found in Chapter "List of Available Functions".