InetSoft Product How-To: Constructing Crosstab Tables

Crosstab tables are a useful way to summarize information in a tabular format. A crosstab table is constructed by using the values of two table columns as row and column headers. A third column is used to obtain the crosstab table cell values. The cell values are a summarization of the rows that match both the row and column header values. For example, suppose we have the following table containing the original data:

The ‘Customer’ column is used as the row header, and the ‘Product’ column is used as the column header. The summarization is done on the ‘Quantity’ column. The resulting crosstab table is the following:

The same information can obviously be generated by grouping on both the‘Customer’ and ‘Product’ columns, and by summarizing the ‘Quantity’ column. However, information is often more readable when presented in the tabular form, as in a crosstab table.

View a five-minute Flash demo to get an overview of what InetSoft's business intelligence software, Style Intelligence, can do and how easy it is to use.

 

 

 

 

 

Walkthrough: Converting a Table into a Crosstab Table

The next example changes the report table to a crosstab table. It uses the ‘Company’ and ‘Product’ columns as headers, and the ‘Quantity’ column as the details.

1. Open the Designer and click the ‘New’ button. In the ‘Create Report’ dialog box, select ‘Blank Tabular Report’ and click ‘OK’

2. Click the ‘Table’ button to add a table to the report.

3. Bind the table to the ‘Order Model’ data model.

4. On the Columns tab, add the following columns: ‘Customer.Company’ ‘Product.Total’ ‘Product.Name’

5. Select the Grouping & Summary tab of the ‘Data Binding’ dialog box and select the ‘Crosstab’ radio button. The ‘Crosstab’ panes will appear.

6. You can add multiple columns as column headers, row headers, or summary fields by selecting the column name and the appropriate ‘Add’ button. The ‘Percentage’ option allows a column to be calculated as a percentage of some other row.

6. Select ‘Product.Name’ on the column list and drag it into the ‘Column Header’ pane.

7. Select ‘Customer.Company’ on the column list and drag it to the ‘Row Header’ pane.

8. Select ‘Product.Total’ on the column list and drag it into the ‘Summary’ pane. Select ‘Sum’ as the formula, and select ‘GrandTotal’ in the ‘Percentage’ menu to display the ‘Sum(Total)’ as a percentage of the Grand Total.

Crosstab Table Options

Additional attributes can be specified on the Options tab. These include foreground and background colors as well as font. A label for the column and row totals may be specified. By clicking the appropriate border in the ‘Crosstab Grid’ area you can define crosstab table border options. This allows you to provide attractive formatting for the crosstab table grid cells.

Both rows and columns can be sorted. Sorting is always done based on the header values. Optionally, the row header columns’ original column header can be retained. In this case, the original headers are used as the column header of the row header columns. The ‘Show Summary Headers’ option can be selected in order to see the column header names for the summary columns. If the ‘Summary Cells Side by Side’ option is selected, the summary header row appears just above the data cells; if the ‘Summary Cells Side by Side’ is not selected, a summary header column appears on the left. The text in the summary column header field can be modified by applying text formatting to those cells.

Previous: Embedding Static Data

Next: Constructing Freehand Tables

More Resources:

		Reporting Software Flash Demo
		Download a Free Trial
		Ask a Question
		Enterprise Business Intelligence