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!
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,
oz.
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.
Hi again Dick,
if 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.
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
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.
Tamas
Please send me a copy of your spreadsheet also – I can’t seem to get it to work.
Thanks,
Dan
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
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.
Hi Dick,
How can I use your idea with 2 parameters.
Let’s say a date range.
Thanks in advance.
Felipe.
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.
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.
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
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
HI
I am not able to use parameters in cube through Micro, can you please advise me how to use it?
Thanks
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.
Hi
I 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?
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?
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
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!
hi there
Can I have a copy of your work book? I need to set parameters as well. Thanks
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
Thanks
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.
I’ll still find it useful even withouth the select a cell feature…thanks
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.
(For completeness sake, I’ve also made this function work with pivottables that use a SQL query directly)
Thanks for your article, it has broken a major stumbling block for me.
In Excel 2007, you can now do this directly without using any code.
Setup your pivottable using external data as above, including the dummy filter criteria. Finish.
1. Select any cell in your pivot table
2. Go to Data -> Properties (In the Connections area)
3. Click on the “Definition” tab
4. Change your dummy filter criteria after the WHERE statement to a “?” without the quotation marks.
You can change as many criteria as you like, as long as you remember which order they show up. They will be named “Parameter 1?, “Parameter 2?, etc
5. Refresh your pivot table
I apologize if some of my terminology is incorrect.
Correction to the above:
You can set the Parameter dialog box to ask what you are querying for.
After you change the dummy filter critera to ?’s, click on the “Parameter” box at the bottom.
Hi Dick,
Can you please send me the worksheet?
I have a spreadsheet with cust# & stuff that loads a big file through pivot table from access. What I want is that, user enters the customer# & the pivot table will refresh the data accordingly.
Note I tried to put the parameter in Access query, i get error “too few parameter”. Also it takes a while to refresh.
can any1 help me please
Dick,
I hate to jump on the bandwagon, but would you mind sending me the workbook as well. It seems to be exactly the type of thing we are looking for at the institution.
Thanks.
Here are the steps I followed:
1. New workbook
2. Data > PivotTables
3. External Data Source
4. Get Data
5. Create query in MS Query as described above
6. In Immediate Window, type
and I get the parameter dialog box. I can share the file with you that I just re-created, but it’s pointing to C:Nwind.mdb so it likely won’t work for you.
http://www.dailydoseofexcel.com/excel/PivotTableParams.xls.zip
I was able to successfully do this using this free Excel addin: http://www.contextures.com/xlPivotPlayPLUS01.html
My strategy after installing this addin was the following… I created a non-pivot table reference to an external database and then used microsoft query to specify the parameters that I needed (filtering out a unnecessary assembly line and a date range in this case). I then opened the sql editor with the addin and copied the “where” line. I then created the same external database reference but using a pivot table (and only one criterion to have a placeholder in the sql code)… After I created the pivot table, I opened the addin again and then pasted over the “where” line with the other code… Presto, it asked me for the cell locations one after another and I told it to always use those cells (which seems to be working).
This is altogether a great device, and the Pivot Play PLUS program is pretty cool and saves messing about with the VBA code. Pity i still can’t rename the parameter (in Excel 2003)
However i have found that in certain cases i am unable to insert the ? into the SQL query and get it to parse successfully. Using the VBA route i get an error (code 400) and the replacetext macro just won’t run. Using the Pivot Play PLUS method on the same pivot table it actually tells me that the sql syntax has an error (which it didnt). I have discovered that this seems to occur when my SQL query extends to more than 1 inner join. When i subsequenty replaced 2 of the tables with a single view to reduce the complexity of the SQL statement (down to just 1 or 2 tables) the macro worked fine and the parameter was accepted into the query. Also i was able to use the Pivot Play PLUS program to effect the same change with no “syntax error”
Anyone else found this. Could be the reason why some if you are getting errors when you run the macro. Try simplifying the query
J
Hi DM Unseen and Bonson
i’m trying to follow up DM Unseen’s comment on parameters in Excel 2007 (13 feb o8) and Bonson (28 may)
I have recetly upgraded form 2003 and was delighted to update all my pivot tables (about 35 in one particular spreadsheet) so that they use parameters that are cell references. and then set up all the cell ref’s.
Sure enough when i refresh the pivot tables the s/sheet crashes. and when i go into the definition of the pivot table and look at the paramters they still show “Use a cell reference” but the cell ref field is now blank. Hence the crashing.
Does anyone know how to get around this.
DM i read your comments about doing this with a combination of querytable and pivottable but i have so many pivot tables in this one workbook i cant face doing that (and i’m not a whizz at the code by any means). plus the tables all feed graphs of which there are about 52 graphs currently.
Help, i need somebody , help…
Thanks
Hello friends,
it seems like the same thing i’ve been working on since last month.
I tried to implement parameters in pivot directly, but stuck at one place . It asks me to enter parameter values every time i open the excel sheet otherwise it works fine once i attach excel cell ref to parameters it works till it is kept open.
I asked the same question at another forum site too, but no answers, hoping to get some tips here.
http://www.mrexcel.com/forum/showthread.php?p=1864251#post1864251
I still do not understand why MS made paramertize pivot tables so difficult. As a mater of fact, MS has a pop up messages saying that “Microsoft does not support parameters in pivot tables”. Fortunately, my friend communicated that to me AFTER I actually got parameters to work in MS Excel 2003. It seems that 2007 , or at least converted to 2007 excel files allow parameters, but for some reason, the paramters do not ‘stick’, meaning you have to specify the excel location every time the file is open, or excel bombs. Fortunately, unconverted 2003 excel files manage to ‘keep’ thier excel references, which, until the time being, all parameter-based pivot tables will be created in 2003 and used in 2007; to take advantage of 2007’s vast memory improvements (retrieved 13.7 million records into a 2003 pivot table opened in 2007). Glad to see kindred spirits using parameterize pivot tables!
Hi – Seems to be a common problem I think. I created a query in MS Access then, created a pivot table in Excel via the ext database query. Looks great! However, a user entered in something in one of the cells (accidently), I refreshed the data and woolaa ! The data still contains the stuff they typed in. I.e., the refresh didn’t overwrite the accidental data. Does anyone have a suggestion on why this happens? I thought Refresh data would delete then over write the stuff with the data that is in the database but it doesn’t? Thank you !!
I have managed to create a workaround for Excel 2003.
I found that it was neccessary to remember the last used value, so I assigned that to a cell. Then an on change event trigger to refresh the pivot, and of course, the query could be refeshed as well.
I will post samples on my website in due course – http://www.mfs-int.net/excel.htm
In the meantime, I can email samples on request.
The code follows,
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$D$2? Then
Call Pt1
End If
End Sub
Private Sub Pt1()
OLDVAL = Application.ActiveSheet.Range(“D1?).Value
NEWVAL = Application.ActiveSheet.Range(“D2?).Value
Sheet1.PivotTables(1).PivotCache.CommandText = Replace(Sheet1.PivotTables(1).PivotCache.CommandText, OLDVAL, NEWVAL, , 1)
Application.ActiveSheet.Range(“D1?).Value = NEWVAL
ActiveSheet.PivotTables(“PivotTable1?).PivotCache.Refresh
Application.ActiveSheet.Range(“D2?).Select
End Sub
@P Verco
Great workaround.
I searched a couple of days for a solution to this problem.
The solution of Dick works, but the problem is that you can not keep the reference to a field when closing and reopening the file the reference is gone.
The solution of P Verco solves this!
Thank you very much.
This post thread led me into an in-depth look at how the CommandText Property works, and I thought I since there are so many people still looking at this problem, I should post my own solution, very similar to P Verco’s:
I was looking into how I could use specific cells as the parameters to refresh the pivot table. Instead of replacing key pieces of the PivotCache.CommandText value, you can declare variables that assign themselves the values of the the cells you want to reference, then actually place those variables in the CommandText SQL code as follows:
Dim CustNo as String
Dim ProductNo as String
Dim pc as PivotCache
Set pc = Sheet2.PivotTables(1).PivotCache
CustNo = Sheet2.Range(“A1?).Value
ProductNo = Sheet2.Range(“A2?).Value
pc.CommandText = “Select CUSTOMERS, PRODUCTS From V_VIEW _
Where (V_VIEW.CUSTOMERS Like ‘” & CustNo & “‘) AND (V_VIEW.PRODUCTS Like ‘” & ProductNo “‘)”
This eliminates the need to remember the last value used as a parameter, and avoids the possiblity of accidentally replacing criteria that may have the same string in it.
Hi,
I just found a funny solution to this, which allows to avoid coding, and remains sufficiently clean if the reporting is stable and not too complicated.
1. Create a XL sheet with your parameters (why not in the same workbook)
1bis. If you want to enter data in the main dashboard sheet, put a formula into the parameter sheet that points to the dashboard sheet
2. Go to access and make this sheet a linked table, with external data feature
3. Then you can use this table in the parameter fields or formulas with a subquery.
Works very nicely.
Regards,
Stevan
I’ve been frustrated with this so much as have been using Excel 2007 with embedded data sources in a big way on a recent project.
Here is my diagnosis of the issue and the workarounds I use to recover my corrupt sheets:
a) I have an Excel 2007 file with 2 embedded data connections both which use ? to specify parameters in the Command Text
b) One excel sheet is a table, the other a pivot – both connections take their parameter values from a further worksheet eg =Parameters!$B$2
c) Excel file works OK when you add the connections and refresh them in the initial session.
d) When you Save and reopen the file, and try to do a Refresh All, Excel crashes – your file appears corrupt, Excel tries to recover and can’t because it is “corrupt”!
e) Examining the last saved file you can see that the parameter references for the Pivot have been lost – if you reenter them again you can recover your file and refresh your sheets – however the parameters for the Pivot connection will be lost again on Save! – THIS MUST BE A BUG!
f) Workaround that I use is
i) Create all my data connections that need to use parameters as just a table (ie don’t use a pivot with an underlying data connection with parameters)
2) create a pivot on the table of results – this works because parameter values do not appear to be lost for table with underlying external connections
I hope this helps others as this was driving me mad.
Trev
Hi Trev,
Unfortunately your workaround is fine if using Excel 2007 but for 2003 will only work if there are less than 65536 records from the query. The beauty of using the pivotcache rather than a straight table is in Excel 2003 I can query more than 65K records on which the pivotcache is based.
Currently I have an Excel 2003 Pivot table based on a qry in Access that needs a date parameter to be passed on a monthly basis, and the query returns about 160K records.
Michael