Excel Pivot Play PLUS Add-In

Free Excel pivot table add-in, PivotPlay, helps you edit connection strings for pivot data source and queries

Introduction

If your Pivot or Query Table is based on an External Data Query, Pivot Play PLUS was built to help you easily edit the Connection String to the data source and the Query itself. It also contains a handful of other features to help you manage your pivot and query tables in Excel 2002 and Excel 2003.

NOTE: The add-in can be installed in later versions of Excel, and it will appear on the Ribbon's Add-In tab. Not all features will work in later versions.

General Usage

The Main Page (see illustration below) opens with a list of all Pivot and Query Tables that are on the active worksheet (along with key information about each of those tables). If a cell within one of those tables is selected when Pivot Play PLUS is engaged, that table will be the default item selected on the list.

Whenever a listed table is selected, additional information about that pivot table displays in the lower section of the Main Page. If the table data is based on an External Data Source, the Connection and Query information will be displayed and the Edit Settings button will be visible. Otherwise, a message that the source is not based on external data displays.

To change the Connection Info or Query Info, click the Edit Conn/Qry Settings button. to open the Edit Settings For page.  

Processing Options

When a table from the list is selected, one or more option buttons, depending on whether the selected item is a Pivot Table or a Query Table. Each button's text indicates its functionality:

• Refresh Data: Retrieves the latest data into the Pivot or Query Table.
• Rename: allows you to give the selected Table a more descriptive name.
• Clear Invalid Field Names: removes dropdown list items that have no representation in the source data. (Pivot Tables ONLY).

Data Source Settings

Clicking on the Edit Settings button opens a window displaying the Connection and Query details. Use this page to:

• Edit the Connection Settings to the data source.
  The upper section of the page displays the current Connection settings. This is where you change the location of the data source and any other Connection String settings.
• Edit the Query Settings that extract data into the pivot cache.
  The lower section of the page displays the current query settings. Edit the SQL in this section.

  

An additional feature in this section is the Read Query From a Cell button, which allows you to select a single cell in the workbook that contains the SQL to be used by the Pivot or Query Table. That reference can be a cell reference or a named range. In either case, the contents of the cell are read and written into the Table settings; the Table itself is NOT linked to the cell

Finally, the Connection Settings and Query Settings sections have buttons to restore their respective settings to the original values.

NOTE: If the edited settings prove to be invalid when saving, you may continue editing the them or click the Restore button see the original values.

Global Settings

The Turn GetPivotData On/Off button toggles Excel's automatic GetPivotData function generation on/off.

The Reset Pivot Play PLUS Startup Settings button returns the Pivot Play StartUp options to their defaults. This includes displaying the Pivot Play splash screen upon opening.

Installation Instructions

NOTE: The add-in is designed for Excel 2002/2003. It can be installed in later versions of Excel, and it will appear on the Ribbon's Add-In tab. Not all features will work in later versions.

To install the Add-in:

1. Download the PivotPlayPLUS.zip file
2. Unzip the file
3. Save the PivotPlayPLUS.xla file in your Microsoft\Addins folder.
   For example, in Windows XP, this might be:
   C:\Documents and Settings\Contextures\Application  Data\Microsoft\AddIns
   (If you have previously installed the PivotPlay.xla add-in..Delete that file)
4. Open Excel
5. Choose Tools | Add-Ins
6. Click the Browse button
7. Find and select the PivotPlayPLUS.xla add-in
8. Click OK, click OK
9. A Pivot Play PLUS command will appear on the Data menu.

Download Pivot Play PLUS

To download the add-in file click this link: PivotPlayPLUS.zip 
(for Excel 2002 and Excel 2003 only)

About the Developer

When he's not sailing, Ron Coderre is a Business Systems Analyst and Application Developer in Boston, where he works within the Finance organization to provide extensible, automated, process re-engineering solutions targeted at increasing the productivity of financial analysts and reducing analytical task duration. Inherent in his finished products are intuitive user interfaces, accurate data, the ability to easily adjust data scenarios, and publication-ready reports. Ron is also an Essbase expert and provides technical training.

Ron Coderre

NOTE: No Help is available for the add-in. If you have comments or suggestions, please contact ddalgleish@contextures.com

More Pivot Table Resources

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table