Using Pivot Tables With XLSX Templates

The WebFOCUS XLSX format can generate a workbook based on a template that contains predefined pivot tables. These pivot tables can be built based on data fed from a report and/or exist independently of the WebFOCUS data on other worksheets.

TABLE FILE GGSALES
PRINT
UNITS/D12C DOLLARS/D12CM 
BUDUNITS/D12C BUDDOLLARS/D12CM
BY LOWEST REGION
BY LOWEST ST
BY HIGHEST CATEGORY 
BY LOWEST PRODUCT
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD AS PIVOTWITHCHART FORMAT XLSX TEMPLATE wf2pivot.xltx SHEETNUMBER 2
ON TABLE SET STYLE *
TYPE=DATA,IN-RANGES='DATAwithHEADERS',$
TYPE=TITLE,IN-RANGES='DATAwithHEADERS',$
ENDSTYLE
END

The wf2pivot.xltx template file must be in the Reporting Server path. The following images show the default of the first and second worksheets in the wf2pivot.xltx template, before executing the sample procedure.

The first worksheet, PivotTablewithChart, contains an empty pivot table and pivot chart. It also contains an empty PivotTableFieldList. The first worksheet is shown in the following image.

The second worksheet, Source Data, contains one column called FieldsToBeAdded, for which there is initially no data. The second worksheet is shown in the following image.

When you run the sample procedure, a pivotwithchart.xlsx workbook is generated, with the WebFOCUS report data stored in the second worksheet.

The following images show the first and second worksheets in the pivotwithchart.xlsx workbook after you run the sample procedure.

First Worksheet

Second Worksheet

In the PivotTablewithChart worksheet, note that the PivotTableFieldList is populated with the fields from the WebFOCUS report. There is one check box for each field in the report procedure. All the check boxes are not selected, by default.

The data used to populate the check boxes is obtained from the Source Data worksheet, where the data from the WebFOCUS report was saved upon executing the sample procedure.

To start building a pivot report and pivot chart, you can select the check boxes for the desired fields in the PivotTableFieldList. For example, selecting the check boxes for Product, Unit Sales, and Budget Units will automatically feed the data from the Source Data worksheet into the pivot table and pivot chart in the PivotTablewithChart worksheet. The resulting pivot table and pivot chart are shown in the following image.

The wf2pivot.xltx template is provided, by default, with the WebFOCUS Reporting Server installation as part of the Legacy Samples. For information on how to download the Reporting Server Legacy Samples, see the Server Administration manual.

You can use the WebFOCUS XLSX template as is, or you can customize it to meet the your business requirements.

Note: The wf2pivot.xltx template includes a pivot table and pivot chart on the same worksheet, but you can use a macro-enabled template to generate a pivot table and a pivot chart on separate worksheets, which are linked to a common data source.