by 
Overview
At design time, we don’t always know how many rows and columns a table will have, nor what their labels will be. Pivot Tables place a grid on a BI Publisher report that is data dependent.
Take for example a table showing an employee’s taxes. The number of taxes paid by each employee will vary by state and tax locality. One employee may have only one or two taxes, where others may have many more. Creating one table for a report that will accommodate both these data sets is difficult at design time: the number of columns and rows vary per each employee.
One method might be to create a table large enough for the maximum number of columns in this tax report. For most employees, columns will be blank or empty. The real data easily disappears into the noise.
Pivot tables allow us to create a table on the report that is correct to that employee’s dataset each time. The exact number of columns and rows are created for each report but will vary depending on the employee’s data. One employee’s report many have two columns where other reports may have many more.
Documentation
BI Publisher Pivot tables are not well documented in PeopleBooks. However, they well documented on the BI Publisher general documentation.
Here are some links to Oracle’s more detailed documentation on BI Publisher and Pivot Tables
Demonstration Example
It’s easier to explain the Pivot Table given a specific example. In this Example, we have a report on employee annual payroll taxes.
Our structured data has a parent child relationship between Employee Data and Taxes.
| Each employee row has one or more tax lines | A simple report using this data might look like this |
|---|---|
 |  |
We can see that this employee has paid a lot of taxes over time. However, there is a lot of detail, much of it iterates over multiple paychecks.
Simple Pivot Table
We can replace the tax detail table with a Pivot Table to summarize and better visualize the employees data.
- Place your cursor in the RTF template where you want the new Pivot table and click on the Pivot Table Control on Word’s BI Publisher menu
2. The Pivot Table control box appears on your page.
- Drag the fields from the data section onto the Pivot Table control in both the X and Y Axis.
- In the below example, each column of the table will be a separate tax class or type while the rows will be the states.
- The Tax_Cur field, or tax amount, is added to the body of the table
3. Click the Preview button on the control to generate an example of what the pivot table will look like in your report
4. Adjust the aggregation and number format in the Properties section of the Pivot Table dialog box.
5. Select the proper column and row totals in the Properties section of the Pivot Table dialog box.
6. Click Preview again and update the sample report in the preview window.
7. Once the table is correctly configured, Click “OK”
8. BI Publisher inserts a completely configured table into your RTF template which will generate the Pivot Table at runtime.
9. Run the report using your sample XML file to see the new Pivot Table
Inserting into a Repeating Group
A problem occurs when your report has a repeating group. The Pivot table searches and aggregates all data in the XML file into each row of the repeating group instead of just the data for that row.
What you get is a file summation for every row.
In our example, my file now has five employees with their tax data. The pivot grid appears on each page of the report. However, each table is identical in that it summarized the entire file instead of just that employee.
Correcting the Group Issue
The way to fix this depends on if you created your data in a hierarchical structure as is my example or threw a “Gob” of a huge, flat XML file at your report (Yes, I’m judging). In a hierarchical structure, your data is already grouped.
With both methods, we need to edit the XSL of the Pivot table limit the table to the group.
On your template, find the “C” character in the first cell of the Pivot table. Highlight it and then double click on the field.
Structured data
Try this one first. This is the easiest fix if your data is already grouped.
Insert a period (.) before the “//row…” text in the properties box. This will force BI Publisher to only search down that XPath to populate data in the pivot grid
<?crosstab:c148;
".//row_X_PT3_TAX_DTL_V";
"fld_STATE_DESCR{,o=a,t=t}";
"fld_DESCR{,o=a,t=t}";
"fld_TAX_CUR";"sum"?>Unstructured data
Replace the “//ROW” designation for the source data with “current-group()”.
<?crosstab:c148;
"current-group()"; "fld_STATE_DESCR{,o=a,t=t}";
"fld_DESCR{,o=a,t=t}";
"fld_TAX_CUR";"sum"?>Click OK and preview your report
Where to go from here…
Bi Publisher’s Pivot Table tools has more options to explore
- Subcategories in the X-Axis
- Subcategories in the Y-Axis
- Sorting on rows and columns
- Count Vs Summation aggregation
- Choice of totals and subtotals on both X and Y Axis
BI publisher’s Pivot Table has the capability to deliver the data visualization your users need.
