Contextures

PPP Help - Filters and Cache

This page shows how to use the main features of the Contextures Pivot Power Premium add-in. If you have a question that is not answered here, send an email to
Debra Dalgleish -- ddalgleish @ contextures.com

 

Filters and Cache

Use the commands in this group to clear filters, change filter settings, change a cache, and list cache information

filter commands

Filters and Cache Video

Watch this video for a quick tour of the Filters and Cache commands on the PPP tab in Excel. It shows how to:

  • Clear all pivot table filters
  • Sort items in Report Filters
  • Allow Multiple Filters per Field
  • List all Pivot Caches in Workbook
  • Change Pivot Caches (normal pivot tables only)

Clear Filters

Clears all filters (row, column and report) in the pivot table(s). A quick way to reset the reports.

NOTE:

  • If a pivot table cell is selected, only that pivot table will be affected by the selected command.
  • If the active cell is not in a pivot table, all pivot tables on the sheet will be affected.

Filter

Use the commands in the Filter drop down to change filter settings for pivot table(s).

Clear Report Filters         

Quickly reset all the report filters, in all pivot tables on the active sheet, to (All).

It doesn't matter which cell on the worksheet is currently selected -- all Report filters will be cleared.

Sort Report Filters

Use this command to quickly sort

  • all items in each field in the report filters
  • in all pivot tables on the active sheet

There's no built-in way to manually sort a field in the Report filter area, so this command makes the task easy for you.. Without this command, you would need to temporarily move the fields to the row area, sort them, then move back to the report filter area.

Allow Multiple Filters – On / Off         

There are 3 types of field filters -- Label, Value and Manual. By default, you can only apply one type of filter at a time.

Use this command to turn the multiple filters setting on or off, for all pivot fields in a pivot table.

  • On – all three types of filters (Label, Value, and Manual), can be on at once.
  • Off – only one filter at a time can be applied.

NOTE:

  • If a pivot table cell is selected, only that pivot table will be affected.
  • If the active cell is not in a pivot table, all pivot tables on the sheet will be affected.

Cache

See the notes below, for details on what is affected by each command.

WARNING: Always make a backup copy of your file before making any changes to the pivot cache.

Cache List

This command adds a new worksheet to the active workbook, with a detailed list of all pivot caches in the active workbook.

cache list report

Columns in the Cache List report:

  • Cache Index - Index number assigned by Excel
  • PTs - Number of pivot tables using that pivot cache
  • Records - Number of rows (records) in the source data
  • Source Type - xlDatabase (Excel worksheet table or list), or other types of data, such as External or OLAP
  • Miss Items - The setting for missing data (Retain items deleted from the data source) - Default, Max or None. (Note: To change to None, so old items don't appear in the drop downs, use the Clear Old Items command in the Pivot Fields and Items group)
  • Refresh Date - Date the pivot cache was last refreshed
  • Refresh Name - User name in Excel, when pivot cache was last refreshed
  • Refresh on Open - Current setting in PivotTable Options>Data tab, for Refresh data when opening the file
  • Command Text - If pivot table is based on an external connection, the command text will appear here
  • Command Type - For an external connection, this shows the number code for the command type, such as 1 for an OLAP cube, and 3 for an OLE DB source. There is a list of command types on the Microsoft site.
  • Connection - The connection string, if available. If a pivot table is based on an external source, you can also see the connection string if you click Properties, in the Connections group on the Data tab, and go to the Definition tab.

Cache Count

Displays a message box with the count of pivot caches in the active workbook.

cache count

Change Cache for Selected Pivot Table

Displays a form with the selected pivot table's cache ID, and a list of pivot caches in the active workbook. Select a cache from the drop down list, and the selected pivot table will change to that cache, if possible.

This is helpful if you have multiple pivot tables based on the same source data, but using different pivot caches. You can change the cache for one or more of those pivot tables, to reduce the number of caches.

NOTE: This feature is only available for pivot tables based on Excel lists, and some types of external data -- it does not work for Data Model or Power Pivot Tables (OLAP).

cache change

Change Cache for All Pivot Tables

Use this command with extreme caution! It is a quick way to get rid of duplicate caches, and reduce the workbook size, but cannot be undone.

  • A confirmation message will appear, asking if you want to change all pivot tables to the same cache as the selected pivot table.
  • If you click Yes, all the pivot tables in the workbook will change to the same cache as the selected pivot table, if possible.

This feature is available for pivot tables based on Excel lists, and some types of external data.

cache change all

Create New Cache

This command creates a new pivot cache for the selected pivot table. There is no confirmation message.

This is useful in some situations, such as:

  • there are two or more pivot tables based on the same data
  • you want grouping in one pivot table, but not the other.

 

 

 

 

Last updated: January 15, 2019 7:03 PM