Pivot tables

Excel PivotTable gives us a really convenient tool to analyze our data. The possibility of quickly changing the data layout and analyzing it without programming, using only mouse (and brains) is a gift for some customers (and a cool tool for programmers, we add). PivotTable is a tool to organize and reorganize our data in order to summarize quickly large amounts of information. It adds multi-dimension features to a relational table. You can rotate its rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details of areas of interest. A pivot table consists of the row area, column area, data area, and page area. Fields of the page area add other dimensions to a 2-D table.

Here is an example of a pivot table that is used to analyze orders.

To create a pivot table you have to create a list range in the template where PivotTable options are used to describe the pivot table fields and their formats. Data will be transferred into the list range and XL Report will create a pivot table. When designing the template, you can designate fields for all the areas of a pivot table, choose summary function for the fields and define their appearance.

Your first pivot table

Where? QDemo.dpr form: frmPivot1; unit: fPivot1; template: tPivot1.xls

We would like to remind you that the Excel PivotTable works with 2-D sources - a list on a sheet, so you have to supply it with a single dataset.

There are two tables in DBDEMOS - Customer.Db and Orders.Db. We joined these tables on the CustNo field and got orders for every customer. Here is the SQL statement:

Look at the form. We placed the dataset (TQuery) on the form and filled the SQL property with the previous SQL statement. We chose the name of the component - qryOrders. Then we added the TDataSource instance, the grid, and the navigator. Then it was the turn of a TxlReport component. We placed it onto the form, named it xlReport, added one item in its DataSources collection and bound the item with qryOrders. We also added the Report and Template buttons and loaded their Click events with calls to Report and Template methods accordingly. The final step in our preparations - xlReport.XLSTemplate = "tPivot1.xls".

Let's create the template taking the next picture as an example. Put the Pivot\Name=OrdersPivot\Dst=Pivot!R4C2\ColumnGrand option in the leftmost cell of the bottom row. This way we tell XL Report to build a pivot table, name it Orders1, and place it on the Pivot worksheet starting from the R4C2 cell. The ColumnGrand parameter allows totals for columns of the pivot table. Then put the Row option in the option cells of Payment method, OrderNo, Ship date and Tax rate columns. This way we tell XL Report to use these fields in the Row Area. To get subtotals on payment method add the Sum option to the existing Row option of the Payment method field. Use the Data option for the Amount paid and Items total fields (pivot table data area fields). Add the Page option to the options of Company field, thus referring it to the Page Area. Don't forget to apply appropriate formats to cells. Format the option cells, to make the results of calculations in the pivot table show with this format applied. Apply any special color to the Payment method field. That's the template. You saw the report at the beginning of the chapter.

To make the Pivot worksheet active after report generation, indicate its name in the TxlReport.ActiveSheet option.

Effective cross-tables

Where? QDemo.dpr form: frmPivot2; unit: fPivot2; template: tPivot2.xls

As you see, building a pivot table means using the Pivot option in a list range. Then the list range becomes a data source for the pivot table. The Pivot option differs from other options in that it can have several arguments separated by a backslash. The syntax is:

Pivot\Name=PivotTableName [\Dst=Destination] [\DataToRows] [\RowGrand] [\ColumnGrand] [\NoPreserveFormatting]

Where:

Name=PivotTableName - the Excel name for the pivot table.

Dst=Destination - cell in the upper left corner of the pivot table. If you omit the Destination then the pivot table will be placed on a new sheet called PivotTableName starting from the A1 cell.

DataToRows - allows placing data fields in the pivot table Row Area. By default, data fields are placed in the Column Area.

RowGrand - lets you see grand totals for rows in the pivot table.

ColumnGrand - lets you see grand totals for the columns in the pivot table.

NoPreserveFormatting - allows the pivot table building without preserving the source range's formatting (see "Speeding up the Pivot table generation" in QDemo).

Some examples of the Pivot option use:

"Pivot\Name=Pivot1\Dst=Totals!A1" - generate Pivot1 pivot table on the Totals worksheet beginning from the A1 cell.

"Pivot\Name=Pivot25\DataToRows" - generate a Pivot125 pivot table on the current worksheet and place data fields in rows.

"Pivot\Name=Pivot25\Dst=Totals!R1C1:R1C1\RowGrand" - add grand totals for the rows of the pivot table.

"Pivot\Name=Pivot25\ColumnGrand" - add grand totals for the columns of the pivot table.

You probably noticed that fields were added to all Pivot table areas in left-to-right order. So you have to place the fields related to each area in the same order you need to display them in the final pivot table.

Note
Field names of a pivot table are taken out of the list range head. Be careful with the field names in pivots - neither Excel 97 nor Excel 2000 (2002) like field names containing a carriage return. The situation has not changed with arrival of Excel 2002.

For the example in this chapter, we took the previous one and changed its template. First, we added a new worksheet - Sheet2. Second, we indicated the cell in the left upper corner of the pivot table - Dst=Sheet2!R8C2:R8C2 (you can also use Dst=Sheet2!B8). To get grand totals for rows we added the RowGrand option. To do the same thing for columns you can add the ColumnGrand option.

As a result, we got: Pivot\Name=OrdersPivot\Dst=Sheet2!R8C2:R8C2\RowGrand. Then we added: Company field (and indicated that we wanted to get a subtotal on it - the Sum option) as well as the OrderNo, Ship date and Tax rate fields to the row area (the Row option). We added the Payment method field to the column area, and we left the Items total and Amount paid fields in the data area. This report differs from the previous one. Take a look at the Payment method field in the generated report - the use of fields with few values leads to the most readable and understandable reports.

We used the Sum option here. You can use any appropriate summary option - minimum and maximum values, deviations, and so on. You can also get several subtotals by specifying the options needed.

Formatting a Pivot table

Where? QDemo.dpr form: frmPivot3; unit: fPivot3; template: tPivot3.xls

To format a pivot table in XL Report you have to format a source range. Then XL Report applies the template cell formats to the corresponding cells of a pivot table. The field totals get the formats of corresponding option cells. We used the color in the option cell to print Company field totals in yellow. XL Report will transfer only the background color, font, and number format. It is just another Excel restriction.

Now for a more advanced example. The goal of a future report is to show minimum and maximum values of the order sum per customer, and subtotals on payment method. The solution is shown in the figure below. Note the cell coloring. See the result in Quick Demo XL Report.

You should be aware of Excel pivot table limitations. Some information can be found in Excel Help. More detailed review of the limitations can be found in MSDN:

• XL2000: Limits of PivotTables in Microsoft Excel 2000 (ID: Q211517)
• XL97: Limits of PivotTables in Microsoft Excel 97 (ID: Q157486)

Static Pivot tables

Where? QDemo.dpr form: frmPivot5; unit: fPivot5; template: tPivot5.xls

Starting from build 115 XL Report offers a more advanced technique of Pivot table creation. Now you can place one or more Pivot tables in a report template using comfortable PivotTable Wizard. We used the first Pivot table sample as a base. Its template contains the SourceRange range at the Sheet1 worksheet. For building the pivot table, we needed to select a source area. This area doesn't coincide with the SourceRange. See below how we selected the source area.

Then we placed the pivot table on the PivotSheet1 worksheet and assigned its fields to the row, column, and page areas of the pivot table. We formatted the fields as well as headers. Because the source area contains only XL Report field formulas, the pivot table contains the fields with the #NAME error.

Finishing our work, we named the pivot table PivotTable1 (choosing Table options in the pop-up menu) and specified the range option in the SourceRange - "PIVOT\Refresh=PivotSheet!PivotTable1". The Refresh parameter allows specifying the list of pivot tables whose data will be refreshed thus allowing you to build several pivot tables based on the same source range. You specify the pivot tables in the list including their sheet names and separating them with semicolon. Just for a show we added another pivot table to the template. We placed it at the PivotSheet2 worksheet and called it Second PivotTable. The Pivot option reflects this change - "PIVOT\Refresh=PivotSheet!PivotTable1,Pivot Sheet1!Second PivotTable". Please, pay attention to spaces in both worksheet and pivot table names, and absence of single quotation marks as oppose to Excel rules.

Excel 2000 and Excel XP users can also make use of Pivot charts in a template. Both Pivot tables and Pivot charts will be refreshed after data transfer.

Time to look back - pivot tables

You know that:

• You can automatically build a pivot table based on a list range using appropriate options.
• The Pivot option has several arguments allowing you to name a Pivot table, to define its place in a report, to use grand totals, and others.
• If the Destination parameter is missing then XL Report sets up a pivot table on a new sheet.
• The Row option designates the field in the row area of a pivot table, Page - the page area, Column - the column area, Data - the data area. Along with these options, you can use any summary options.
• XL Report uses the names from the header of the corresponding list range to name pivot table fields.
• XL Report assigns the background color, font, and number format of the list range cells to the appropriate cells of the target pivot table. Option cell formats are transferred into subtotal cells of the pivot table.
• The ActiveSheet property allows you to specify the name of the worksheet that will become active after report generation.
• Use the NoPreserveFormatting parameter for speeding up pivot table creation.