Cross-tab reports group data in two dimensions (down and across). They are useful for summarizing large amounts of data in a format that cross-references information.
To create cross-tab reports, you will typically start with a GROUP BY query to summarize the data into rows, and then use a transformation (pivot) service to create the grouped columns. The transformation service can be provided by the database server itself, it can be a custom program, or you can use C1Report's built-in domain aggregates.
In all cases, the most important element in the cross-tab report is the original summarized view of the data. For example, a typical summarized view would look like this:
|
Year |
Quarter |
Amount |
|
1990 |
1 |
1.1 |
|
1990 |
2 |
1.2 |
|
1990 |
3 |
1.3 |
|
1990 |
4 |
1.4 |
|
1991 |
1 |
2.1 |
|
1991 |
2 |
2.2 |
|
1991 |
3 |
2.3 |
|
1991 |
4 |
2.4 |
This data would then be transformed by adding columns for each quarter and consolidating the values into the new columns:
|
Year |
Total |
Q1 |
Q2 |
Q3 |
Q4 |
|
1990 |
5 |
1.1 |
1.2 |
1.3 |
1.4 |
|
1991 |
9 |
2.1 |
2.2 |
2.3 |
2.4 |
You can do this using C1Report aggregate functions. The report would be grouped by year. The Detail section would be invisible, and the group header would contain the following aggregates:
|
Year |
Total |
Q1 |
Q2 |
Q3 |
Q4 |
|
[Year] |
Sum(Amount) |
Sum(Amount, Quarter=1) |
Sum(Amount, Quarter=2) |
Sum(Amount, Quarter=3) |
Sum(Amount, Quarter=4) |
The first aggregate would calculate the total amount sold in the current year. The quarter-specific aggregates specify a domain to restrict the aggregate to the specified quarter.
Sample Report
Available
For the complete report, see report "20: Cross-tab Reports" in the CommonTasks.xml report definition file, which is available for download from the CommonTasks sample on the ComponentOne HelpCentral Sample page.
|
