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. The Pivot table source is an Excel list or an Active XL Report list range.

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 the Pivot option is used to describe the pivot table fields and their formats. Data will be transferred into the list range and Active 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? form: frmPivot1; template: Pivot1.xls

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

Let's create the template taking the following picture as an example. Put the Pivot\Name=Orders1 option in the leftmost cell of the bottom row of the range. This way we tell Active XL Report to build a pivot table, and name it Orders1. Then put the Row option in the option cells of Payment method, OrderNo, Ship date and Tax rate columns. This way we tell Active 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. Apply appropriate formats to cells. Format the option cells too, 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 see 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.

Constructing a pivot table

Where? form: frmPivot2; template: Pivot2.xls

As you see, building a pivot table means using the Pivot option in a list range. Then the 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]

Where:

Name=PivotTableName - the Excel name for the pivot table (see Appendix B).

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

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

RowGrand - allows inserting grand totals for rows in the pivot table.

ColumnGrand - allows inserting 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 the xlrDemo projects).

Some examples of the Pivot option use:

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

"Pivot\Name=Pivot25\DataToRows" - create the 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 - Excel doesn't like field names containing a carriage return.

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 upper left cell of the pivot table - Dst=Sheet2!R8C2:R8C2 (you can also use Dst=Sheet2!B8). To insert 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 put the Row option for the following fields: Company (and indicated that we wanted to get a subtotal on it - the Sum option) OrderNo, Ship date, and Tax rate. We added the Payment method field to the column area (the Column option), 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 different values leads to more readable and understandable reports.

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

Formatting a pivot table

Where? form: frmPivot3; template: Pivot3.xls

To format a pivot table in Active XL Report you have to format a source list range. Then Active 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. Active 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. Note the cell coloring. See the result in xlrDemo project.

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? form: frmPivot5; template: Pivot5.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 the 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 Active 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.
• Active XL Report uses the field names of the corresponding list range to name pivot table fields.
• Active 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.
• Use the NoPreserveFormatting parameter for speeding up pivot table creation.