Query Definition: Producing a Pivot Result Set 
A report can be created that contains a two-dimensional, variable-width table and an optional chart. The number of columns (the width of the horizontal dimension) depends on the data being selected. Two query definitions typically cooperate to produce the data for the chart and for a result set that is pivoted, in which the data read in successive rows is moved to sets of repeating columns across the width of the table.
To create a report that produces a pivot result set:
Create a query definition with a chart that identifies the numeric measure and the two dimensions in the result set. This first query typically writes data to a work file that is also read by the second query. It identifies the set of labels that will be used as super column headers in the second query.
Select the data label Column ID in the chart definition, which will supply the strings to be displayed in the vertical dimension of the table in the second query.
Select the series label Column ID in the chart definition, which will supply the super column headers of the table in the second query. Do not select the Other checkbox.
Group the result set primarily by the series label column and secondarily by the data label column. These label values must be unique.
Suppress the query results table for this query. It is the chart data which is important.
Suppress the chart display for this query if the only purpose of the chart definition is to identify the series and data labels.
Create a query definition to produce the pivoted result set. This second query typically reads the data from a work table that was populated by the first query. It also receives series and data label information from the first query.
The result set created by this query should contain the following columns:
Repeating Columns that will be pivoted in the final result set, each with one of the chart series labels. The Repeating Column indicator should be checked for each of these contiguous columns.
Data Label Column that contains unique row labels for the pivot table. All rows of the base result set should be grouped by this column. You can use other columns for ordering, but you must be sure that all rows which will be pivoted into one are contiguous. The column ID for this data label column must match the corresponding Column ID from the chart query.
Series Label Column that contains values that will become headers for the groups of repeating columns. The order of the Series Labels doesn't matter; in the pivot table, all Series Labels will be displayed in the order given by the chart query. The column ID for this series label column must match the corresponding Column ID from the chart query.
If a workbook template is specified for a report that contains a query that pivots a result set, then that template should have only one worksheet. The second and third worksheet (and any others) should be deleted. If the purpose of the template is to provide format styles, then the styles on the first sheet will have to suffice for all generated sheets.