Show or hide menuTRichView.com

  Cross Tabulation (Pivot Tables) in Report Workshop for Delphi and C++Builder

Overview

A cross-tab report allows displaying data as a grid, with rows representing one group of data (row fields), columns representing another group of data (column fields), and intersection of rows and columns containing the summarized information (value fields).

Cross-tab columns may include data columns and summary columns.

Report Workshop offers the following features for cross-tab tables:

  • any number of cross-tab levels (i.e. column fields)
  • flexible structure of cross-tab rows and columns:
    • any number of columns corresponding to each column field (usually, reporting tools provide only a single column per a level)
    • any number of rows corresponding to each row field (usually, reporting tools provide only a single row per a field)
    • consequently, the intersection is not necessary a single cell, but a rectangle of M×N cells
    • any number of summary columns for each level, any number of summary rows
  • several methods for producing columns:
    • automatic: values of column fields are taken from results of the same data query as row and value fields; for multilevel cross-tab reports, columns may be generated either independently or in groups
    • using separate data queries; for multilevel cross-tab reports, queries may be either independent or cascading
    • using ranges of numeric, date-time or Boolean values
  • multiple cross-tab reports in a single table (using different sets of row and value fields, but the same column fields)
  • aggregate functions in summary cells: min, max, sum, average, count, variance, standard deviation, median (can be extended by implementing plug-ins)
  • values in cells may be visualized by displaying diagrams or changing cell colors
  • each cell may contain not a single value, but any formatted text and images; cells may even contain sub-reports

A typical cross-tab report: payment method  ×  shipping method = amount

Cross-tab repoort showing a subordinate/manager relation on the same table

Pivot table for sakila database (MySQL) with subreports in cells

This flexibility is achieved by using a unique approach for building cross tabulation tables. In most of the reporting tools, users start designing a cross-tab report from a model, where they select row fields, column fields, value fields, specify summary columns and rows. In Report Workshop, users start designing a cross-tab report from a table template. Then users define columns and position of the cross-tab header. Finally, users define row fields. A layout of cross-tab table gives a lot of information by itself, for example count and location of data and summary columns for each level. Content of each cell is edited by the user just like in any other part of a report template.

Examples

How to create a simple pivot table, how to add summary columns and rows, how to visualize values:

How to make two data columns, how to add the second level, how to add summary columns for each level:

How to generate columns by separate data queries, how to use caption fields, how to use cross-tab tables as a detail in a master/detail report:

The resulting report of the last video can be viewed as HTML.