Create a Pivot Table from Jet Reports

Overview

PivotTables and PivotCharts can be extremely useful tools for analyzing data.

Pivot tables can be created from Jet replicating functions [i.e., NL("Rows"), NL("Columns")] or from an NL("Table") function.

Here are a few hints for using these useful Excel features with your Jet Reports.

Using Replicating Functions

1. Once we have created our report so that it returns the data we want...

   

   ... we are ready to define our PivotTable

2. While in Design mode , we've selected our header row, the row containing our Jet functions (this can be more than one row), and the blank row AFTER our Jet functions - this row is referred to as the Anchor row

   Including the Anchor row (or Anchor column, if our report is based on the NL(Columns) function) is essential .  This will allow our PivotTable to expand and contract as the data in the Jet report changes.

3. Now, it is simply a matter of selecting PivotTable from Excel's Insert ribbon:

   Note that the range specified in the Create PivotTable dialog is the same range we selected on our worksheet (including the Anchor row):

   

4. We can then define our PivotTable with whatever structure we want.

   Because we included the Anchor within the defined region of our PivotTable, when the underlying data in our Jet report changes, our PivotTable data will change, too:

Using Table Builder

The Jet Table Builder is ideal for use with Excel Pivot capabilities.  Table Builder reports can quickly return large amounts of data from our database, which can then be easily analyzed using PivotTables and Charts.

1. Here we see the same the same information as we saw in the first example, but this time it was created using the Table Builder and resides in an Excel table:

   Important

   Note that the Table Builder automatically gives this Excel table a name:

   

2. Run the Report

   While in Report mode , click anywhere in the Excel table.  Then click the PivotTable button on Excel's Insert ribbon:

3. Because the Excel table already has a name, the Create PivotTable dialog can use that name for defining the region on which the PivotTable will be based.

   

4. Click OK and Excel will insert the Pivot Table from where we will have access to all the data contained in our report.

   Whenever the data returned by the NL(Table) function changes, the data in the PivotTable will automatically be updated.

Using the NL("Table") Function

1. Create our NL(Table) function

   Using the Jet Function Wizard (or typing or function by hand), we need to be sure to give our table a name by using the special TableName= filter

   

2. Run our report

   It is important that our report be in Report (not Design) mode for the next step.

3. Insert the Pivot Table

   We'll select any cell within the Excel table that now contains our data, and then click the PivotTable button on Excel's Insert ribbon.

4. We'll ensure that the table listed in the Create PivotTable dialog matches the name we gave to our Excel table in step #1.

   

5. Click OK and Excel will insert the Pivot Table from where we will have access to all the data contained in our report.

Related Articles

• Table Builder and NL("Table")