Pivot tables that are based on external data are somewhat limited compared to normal external data queries. One limitation is that you can’t (read: it’s difficult) to create parameters. At least there’s no way to do it in the user interface that I’ve seen. This post will show you how to modify the CommandText property of the PivotCache object to create a parameter.
Note that you can simply create a normal external data table and base your pivot table off that. That you should give you all the flexibility you need, but it’s not as neat and clean. Also, I believe you have to perform two refreshes in that circumstance: one for the external data and one for the pivot table.
When you create the external data pivot table, include criteria to limit one of the fields. This builds the WHERE clause of the SQL statement and makes it far easier to convert to a parameter query. For this example, I’m using the Invoices table from Northwind.mdb and pivoting the ExtendedPrice on the PostalCode.
This produces a pivot table that shows how much we’ve invoiced for this particular zip code. I picked a zip code that was in the database, but it’s just a placeholder for now.
The SQL statement behind this pivot table is stored as a property of the PivotCache object. Here’s what that property looks like via the Immediate Window:
That property is just a string, so if you know what a parameter looks like, you can manipulate that string. Here’s how I might do it in a macro:
Dim pc As PivotCache
Set pc = Sheet2.PivotTables(1).PivotCache
pc.CommandText = Replace(pc.CommandText, “‘05022′”, “?”, , 1)
I’m replacing my placeholder zip code, 05022, with a question mark. The question mark is interpreted by MSQuery as a parameter. Once I run this sub, the pivot table tries to refresh, and I get this
Now that the question mark is the SQL statement, every time you refresh the pivot table, it will ask you for a value. What I can’t seem to do is manipulate the parameter to, say, change the prompt or base it on a cell. I know all those features are built in to the class, MS just didn’t expose them in this situation (i.e. they didn’t create a Parameters property of the PivotCache object like they did with QueryTable object). Consarnit!