Sign Up for Training |
Jet Global Company Site
Community
Downloads
Training
Submit a Request
Become a Jet Insider
Give Feedback

Create a Pivot Table from Jet Reports


Related Articles...

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...

    pivot01.png

    ... 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.

    pivot02.png

    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:

    pivot03.png

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

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

    pivot05.png

     

    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:

    pivot06.png

     

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:

    pivot07.png

    Important

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

    pivot08.png

     

  2. Run the Report

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

    pivot09.png
  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.

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

    pivot12.png

    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

    pivot11.png

  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.

    pivot09.png
  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.

    pivot10.png

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

    pivot12.png

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments