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.
Select the New Item icon
at the top of the window > select 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.
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.