Understanding Cross Tables in Report Designer

The articles in this section of the Knowledge Base involve working with Cross Tables for display in reports. To access the Report Designer, select Toolbox Setup > Design Reports > Design (Report Type) Report. Select New Report to design a report from scratch or New Report from Existing. Select Designer to open Report Designer.

The Cross Table component is a special component that is used to process, group, and summarize data from the Data Source. The result is presented as a table. The Cross Table component can be placed directly on a page or in a Data band. When a table is created that does not fit on one page, it can be printed on multiple pages. The component has many properties and settings.

Fig. 01 – Cross-Tab Component

Data Source Property

For a Cross Table to function, it needs to be assigned a Data Source. The purpose of a Cross Table is to compare data from the Data Source. Before the Data Source is selected, create a Relation between two tables from the Data Source so that they can be compared.

Create a Relation

Select the Dictionary Tab at the bottom of the Properties Window.

Fig. 02 – Dictionary Tab

Select the New Item icon at the top of the window > select New Relation.

Fig. 03 – New Relation…

In the New Relation Window, enter the Name in Source and the Name > select the Primary Key in the Parent table and the coinciding Foreign Key in the Child table.

Fig. 04 – Create a New Relation

Active Relation sets the priority Relation to bind two tables. It is used in Data Transformation and Dashboards. Only one Relation in each Data Source can be active.

The new relation is added to the Child table.

Fig. 05 – Subassemblies & Products Relation

Add Data Source

Now that a relation has been created, the Data Source can be added to the Cross Table component.

Double-click the Cross Table component > in the Data Source tab, select the table that contains the Relation.

Fig. 06 – Data Source

Create a Basic Cross Table

The relation established above contains the Parent table. Drag the desired Column(s) from this Relation/Parent table to the Columns section of the Cross-Tab Tab.

Fig. 07 – Parent Column

Drag the desired Row(s) from the Child table.

Fig. 08 – Child Row

Drag the desired Summary item from the Child table.

Fig. 09 – Summary Item

The Summary item is where the Columns and the Rows meet. In this example, the Columns are the Products in the Work Order that provides the data for the report. The Rows are the Subassemblies in the Work Order. The Summary item is the Quantity of the Subassemblies in the Work Order. When this Cross Table is rendered, it resembles Figure 10.

Fig. 10 – Example Cross Table

This Cross Table tells at a glance the number of subassemblies and which subassemblies are in each product, the number of times a subassembly appears in the Work Order, and the total number of subassemblies.