Configuring a Pivot Table Report

May 13, 2014 , by
Pivot tables let you group the information contained in a list by row and column. Once configured, a pivot template can be used in conjunction with any query performed on the list. You can create pivots on the fly, or save them so that they can be accessed at any time.

Configuring a pivot table

To create a pivot table, use any list displayed in the Pistachio web part. Choose Pivot Table from the Actions drop-down menu on the lower left side of the list to display the pivot table dialogue box.

The screen image below shows the setup section of the dialog box. To configure the pivot, drag fields from the list on the left and drop them into the labeled areas.
  1. The left column shows fields that you can use to group on or summarize. Simply take a field and drag it over to one of the areas on the right.
  2. A field dropped into this area will group field values and display them one per row.
  3. A field dropped into this area on the top will group field values and display them one per column. It's good to use a field that doesn't have too many unique values across the top so that the columns don't extend too far out to the right.
  4. Drop a field into this area that you want summed or averaged. The sum or average will be calculated for all records were the field values for the row and column are the same. If you only want to display account, you can leave this box blank.
  5. Indicate whether you want records to be counted, summed or averaged, and whether you want to display the total numbers or relative percentages. For sum or average you will have to choose a numeric field to perform the calculation on.
Once you've configured rows and columns, click the Pivot button to display your pivot table.  You can configure pivot tables that only group by row, or by column. Remember that the best fields to group on are those that don't have too many different values.

Navigating pivot table reports

After pressing the Pivot button, the pivot table is displayed.   It will use the underlying list and group the corresponding row and column fields.  The image below shows a sample pivot that summed loan amounts by status and region.  You can drill down by row, column or cell to see the corresponding records. 
  1. Clicking on a row on the left will list all the records that match the row's value.  For example, clicking on Active above, will list all records in which the status is "Active" and for all regions.
  2. Clicking on a column on the top will list all records that match that column's value. For example, clicking on Africa will list all records flagged as being in Africa (and ignoring the status).
  3. Clicking on an individual cell will list all the records that match the cell's status and region. So, clicking on the cell with the value of $962,000 will list all records with a status of "Pipeline" and located in "Europe and Central Asia".
To edit the pivot table, choose Modify Pivot Table from the Actions drop-down menu on the lower left side of the table.

Saving pivot table templates

When configuring pivot tables, you can save the templates for later use.  Use the Saved Pivot Tables panel on the left to save a new template or retrieve other ones.

  1. To save a pivot table template, give it a name and click on the save icon.
  2. To retrieve a save template, click on the name and its configuration will be displayed on the right panel.  You can rename a template or delete it as well using the small icons on the right side of the name.