41 thoughts on “Pivot Table Parameters”

1. 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.

2. 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.

3. 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.

4. 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

5. 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

6. Please send me a copy of your spreadsheet also – I can’t seem to get it to work.

   Thanks,

   Dan

7. 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

8. 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.

9. Hi Dick,

   How can I use your idea with 2 parameters.
   Let’s say a date range.

   Thanks in advance.

   Felipe.

10. 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.

11. 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.

12. 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

13. 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

14. HI

    I am not able to use parameters in cube through Micro, can you please advise me how to use it?

    Thanks

15. 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.

16. 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?

17. 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?

18. 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

19. 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!

20. hi there

    Can I have a copy of your work book? I need to set parameters as well. Thanks

21. 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

22. 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

23. 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)

24. 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.

25. 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.

26. Hi Dick,
    Can you please send me the worksheet?

27. 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

28. 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.

29. 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

    sheet1.PivotTables(1).pivotcache.commandtext = replace(sheet1.PivotTables(1).pivotcache.commandtext,”‘05022′”,”?”,,1)

    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

30. 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).

31. 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

32. 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

33. 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

34. 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!

35. 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 !!

36. 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

37. @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.

38. 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.

39. 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

40. 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

41. 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

Posting code? Use <pre> tags for VBA and <code> tags for inline.