Pivot Table Parameters
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)
End Sub
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!
oz:
Hello Dick,
about the "Pivot Table Parameters" code.
i tried your method exctly like in the example but its not working, im getting the messege "Application defined error"
can you pls help ?
im using office 2000 on win98.
best regards,
11 May 2005, 4:00 amoz.
Dick Kusleika:
oz: Well, I may have left out something important, but I don't know what that is. I can email you the workbook I used (or recreate it if I didn't save it) or you can feel free to email your workbook to me. Be sure to be specific about where the error is.
11 May 2005, 1:29 pmoz:
Hi again Dick,
15 May 2005, 2:49 amif u can send me the Workbook i'll be greatfull.
my mail is: oz.saar@deltagalil.com
and also what version of Excel u r using ?
many thanks,
oz.
Jack N:
Hello Dick,
I came up with the same error that Oz got.
Can you please e-mail me the workbook with the correct solution.
With appreication in advanced.
Thanks,
Jack
6 June 2005, 12:09 pmTamas Karacsony:
Hi!
I am just trying to rewrite the CommandText property, so as to make the administration of pivottables easier (for example when the place or name the underlying file changes). But unfortunately VBA always send me '424 - Object required' error message when I try to give _any_ value to the CommandText property. Either nullstring, either valid SQL string, either someting else, even if its actual value... I always get these message.
Haven't you meet this problem at all?
Thx.
9 June 2005, 1:30 amTamas
Dan:
Please send me a copy of your spreadsheet also - I can't seem to get it to work.
Thanks,
Dan
9 June 2005, 5:30 pmRob:
Dick,
My understanding is that .commandtext is a read-only variable, at least up to Excel 2000. Maybe that is why people are having difficulty?
I would like to see the workbook as well.
What version of Excel are you using?
Rob
12 June 2005, 9:18 amDick Kusleika:
I know it seems like I've been ignoring you guys (because I have), but I swear I will be posting that workbook soon. Or maybe when I get around to looking at I'll see where I screwed up. Stay tuned.
14 June 2005, 1:17 pmFelipe:
Hi Dick,
How can I use your idea with 2 parameters.
Let's say a date range.
Thanks in advance.
Felipe.
4 July 2005, 10:39 pmMike Ford:
You can build any query you want using the CommandText property. Just build up the "Where" clause and set it directly as in
pc.CommandText = "SELECT Blah FROM dbo.Blah WHERE Data = 'Blah'".
This gives you the flexibility of adding any "Where" clause you want from within your Macro.
I was able to set the CommandText property in Excel 2003 without any problem although it didn't come up with a prompt when I set it to "?" as shown above.
15 July 2005, 6:01 amsaikumar:
Good After noon how are you
i am getting trouble in creating parameter table
for a spresd sheet for my assignment
can you guide me what exactly the parameter table and how to create it.
thank you
with kind regards
satya
Prepare budget figures for the years 2014 – 2016 based on the estimates below. Set up a parameter table for the budget figures and name the sheet to identify it.
10 August 2005, 9:24 pmJarrett Atkinson:
Very cool parameter option for pivot table. Never knew it could be done. How do you view the pivotcache object? Do you also know if there is a way to redirect where the data file is when using external data. For example, the database got moved to a different server, and I don't want to recreate the pivot tables since it took a while, just like to say look on this drive folder instead of the original one.
Jarrett
20 December 2005, 5:22 pmDM Unseen:
Dick,
this trick has been known to me for several years, but there is one *BIG* caveat (at leats in 97 and 2000, i have not tested/used this afterwards).
If you instead of entering a value, select a cell, and check 'use this value/reference for furter...'
you will be in trouble. After you saved the file it will not reopen, but crash excel instead. There might be other issues as well, but this was the reason for me not to use it at all.
(sorry to spoil the fun;)
Dm Unseen
21 December 2005, 3:12 amSanjeev:
HI
I am not able to use parameters in cube through Micro, can you please advise me how to use it?
Thanks
1 March 2006, 6:57 amJeremy:
I am trying to set up a table for data entry.
the user enters an order number into the first column of each row. this cell is the parameter for a query in the second cell which looks up a piece of data based on that parameter.
The next line is identical and so on with the user entering a different order number in each row for 20 rows.
The problem is i am getting an error message when ANY field in the spreadsheet is updated although only the first column is defined as the parameter for each query. The error message is
"Bad paramter type. MS Excel is expecting a different kind of value than what was provided"
Then i get
"The following data range failed to refresh:
....name of range...
Continue to refresh all?"
Trouble is i didn't enter any data in any cell that this particular range is linked to.
Any ideas.
9 March 2006, 11:28 amMeeta:
Hi
21 March 2006, 8:58 amI am trying to display the actual data, and not the summary or total in the data area of the pivot table. MS Access lets me do this, but not MS Excel. Also with MS Access, the system slows down a lot. Any suggestions?
Stan:
I have been trying to pull something like this off for months now. Glad I found this page!!
I have gotten it to work with a number criteria (just like the zip code)
However, I need to use it for a date range, and I can't seem to get the macro to replace the string without the "application or object defined error" right after I set my cell reference using the criteria box.
I am using
pc.CommandText = Replace(pc.CommandText, "{ts '2005-12-01 00:00:00'}", "?", , 1)
in the replace macro to replace the string in the pivotcache commandtext below.
(qCurrent8hrBldSheet.Date>={ts '2005-12-01 00:00:00'})
any advice on how to get this to work?
21 July 2006, 1:47 pmZ Zhang:
Hi, Dick,
thank you very much! It seems to me that you were the only one in the world who knew the solution. I could not find it anywhere else.
Thanks again!
-Zhang
2 May 2007, 11:59 amMaxim Ivashkov:
Excel does not allow to use varaiables' names (parameters) in the pivot table query. Access would issue a pop up input dialog. The work around this restriction is to use a function that will compute the value (or prompt the user to input it).
Enjoy!
24 May 2007, 2:38 amkelvin:
hi there
Can I have a copy of your work book? I need to set parameters as well. Thanks
28 June 2007, 2:25 amSteveM:
Dick - This is Great! I got it to work as per your instructions & also with a cell ref which is perfect for what I have been looking for
12 February 2008, 10:11 amThanks
SteveM:
Oh well....Nearly! - I see that DM Unseen was correct with his comment on using "select a cell...etc" that after saving the file it will not reopen! - I was using Excel 2003 by the way.
12 February 2008, 10:26 amI'll still find it useful even withouth the select a cell feature...thanks
DM Unseen:
Has somebody tested this parameter reference bug in Excel 2007 yet?(does it still crash excel?)
BTW the workaround I use for now for this is a lot of code and a querytable and pivottable combination. The query definition and parameters will come from the querytable and in the before_refresh event of the querytable I'll prevent the querytable from refreshing, but refresh the pivottable instead, with the query from the querytable, but with the parameters replaced by values. This works as expected, but it needs some code.
I also made it so I can switch between this refresh and the more traditional way of first loading the data through a querytable and then refreshing the pivottable, all through code of course.
With this last situation I have made an option to execute an auto filter on the querytable range with the drill-down function of the pivottable, i.e. a drill-down on the pivottable will not open a new sheet, but will put an auto filter on the source range of the pivottable thus showing only the relevant rows. Works like a charm, as long as all data fits on a sheet.
13 February 2008, 4:01 am(For completeness sake, I've also made this function work with pivottables that use a SQL query directly)