+ Reply to Thread
Results 1 to 30 of 30

Changing OLAP Pivot Table with GetPivotData function

  1. #1
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Exclamation Changing OLAP Pivot Table with GetPivotData function

    I am trying to find out a way to change the date on an OLAP pivot table by having it reference another cell on another worksheet. Can a GetPivotData function do that? I've been trying to get it to work by putting in the cell that I want it to reference but it keeps coming up with #REF.

    =GETPIVOTDATA("[Measures].[Net]",$E$7,"[Reporting Date].[Fiscal Week]","[Reporting Date].[Fiscal Week].&[20145209]")

    Here is my GetPivotData formula. I tried putting the ["&A1&"] in different places in the formula but it won't work.

    Any help would be much appreciated.

    Thank you!

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    GetPivotData is a READ function - it reads data from the pivot table, based on the arguments specified. It can't change the pivot table itself.

    You say you are trying to change the date on the pivot table.... Do you mean you want to change a pivot table filter to a date stored in a cell? That's simple enough, with a bit of VBA code...

    It will be easier to help if you attach a sample workbook, showing how your data / pivot table is structured, and what your required outcome is.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    I've been trying to use VBA but I've never used it before. I've spent so many hours this week trying to figure out this problem.

    http://www.excelforum.com/excel-prog...ml#post3786916

    I tried to figure out the VBA from this thread I created yesterday. I was able to get the VBA to work for a regular pivot table, but it won't work for a OLAP query. I can't figure out how to get it to work with OLAP.

    Thank you,
    Mark
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    So from that file. I want to be able to have a cell in another sheet where I can type in the week and it will automatically update all the pivot tables to that specified week.

  5. #5
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    Okay - try this:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    I can't get it to work. I tried putting it into the file I sent you with macros enabled and nothing happens when I put the week into cell A1.

    I wasn't sure what this line was doing. If Not pi.Caption = "Week" & iWeek Then pi.Visible = False

  7. #7
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    It's not set to auto trigger when A1 changes - put a number in A1, then run the macro...

    If that works okay for you, then call the macro based on the worksheet_change event.... But try it manually to start with, make sure it works, first. Tested fine for me.

    The code loops through every item in the "Week" field, and this line:
    Please Login or Register  to view this content.
    hides records if they don't match "Week X"

  8. #8
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    When I run it I get a "type mismatch".

  9. #9
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    Aw! ok. I got it to work. I was typing Week 2. Just need to type 2 ok. If I want to be able to type the whole thing instead of just the number do I delete the "Week " and just leave ""? And will this work with OLAP?

  10. #10
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    if you want to type "Week 2" instead of just "2", then the code will need changed to accept a string value, instead of an integer... the variable iWeek will need changed to a string, not an integer, and the line:
    Please Login or Register  to view this content.
    will also need changed.

    Shout if you need help.

    Should work as well with an OLAP sourced pivot table as any other - OLAP is just the data source, the pivot object in Excel works exactly the same way.

  11. #11
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    Ok, I am now trying it on my project now. I am getting a, "Application-defined or object-defined error." Typing in the integer actually makes my template I'm making easier for the end users to use. I have a large OLAP of data and they want to be able to see data from just one week. I wanted to be able make a template for them where they just have to type in the week they want and all the information comes up automatically.

  12. #12
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    On error, hit debug - where's the error happening?

    Edit: did you change the references to the sheet name / pivot table name, to suit the workbook you are using the code in?

  13. #13
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    I changed the references. When I click the debug selection it highlights

    Sub RefilterPivot()

    iWeek = Sheet1.Range("A1").Value 'change as necessary

    On Error GoTo FilterError

    With Sheet1.PivotTables("PivotTable8").PivotFields("FiscalWeek")

    For Each pi In Sheet1.PivotTables("PivotTable8").PivotFields("FiscalWeek")

  14. #14
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    It won't highlight all 5 of those rows... which one is it failing on?!

  15. #15
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    The first line. If I push F8 it'll highlight the first line, then if I push it again it'll keep going down the code to each of those lines each time I push F8.

  16. #16
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    Can you attach your actual workbook? I can't see anything that should be failing...

  17. #17
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    This is what I have now after I changed the names to reference the new file.

    Sub RefilterPivot()
    Dim pi As PivotItem, iWeek As Integer
    iWeek = Sheet1.Range("A1").Value 'change as necessary
    On Error GoTo FilterError
    With Sheet1.PivotTables("PivotTable8").PivotFields("FiscalWeek")
    For Each pi In Sheet1.PivotTables("PivotTable8").PivotFields("FiscalWeek").PivotItems
    pi.Visible = True
    If Not pi.Caption = "2014 WK " & iWeek Then pi.Visible = False
    Next pi
    End With
    Exit Sub
    FilterError:
    For Each pi In Sheet1.PivotTables("PivotTable8").PivotFields("FiscalWeek")
    pi.Visible = True
    Next pi
    MsgBox "There was an error with the filter value. Please check and try again"
    End Sub

  18. #18
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    I really need to see your workbook. Can't see anything in the code which should be failing.

  19. #19
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    OK - received a workbook via email, where the week field was formatted [yyyy "WK" ww], and used as a Report Filter field, not a Column field.

    Popped the following code into the worksheet module, to refilter based on a year entered in B1, and a week number in B2:
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    Thank you so much. I got it to work in the file you sent. It still doesn't work in the file I'm creating. I messed around with the file you sent and it stopped working if I put in a value not in the data range. And now when I redownload the file you sent and try to use it again, it doesn't work anymore. I don't understand why it worked when I first downloaded it but when I redownload the file and try again it won't work. :/

  21. #21
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    When you say "won't work" - what is actually happening?

  22. #22
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    It doesn't do anything at all. An error box does not pop up. It just doesn't do anything.

  23. #23
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    Close and restart Excel, then try again...

    Oh, and change the code to this:
    Please Login or Register  to view this content.

    Sounds like it's hit an uncaught error, and left events disabled. This modified code will inform you of an error, and reset events - then we can debug what is actually causing the error
    Last edited by Olly; 08-01-2014 at 01:33 PM.

  24. #24
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    Ok great. That fixed that. Now I am trying to get it to work with the OLAP pivot table in my report and it comes up with an error for this line. I made sure the Pivot Table name was right and the field name was right. Is there an error here because it's an OLAP pivot table?

    With PivotTables("PivotTable8").PivotFields("Week")

  25. #25
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    I wish you'd sent me the actual file you wanted to work with!

    What is the error displayed?

  26. #26
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    The error is "Unexpected Error: 1004 - Unable to get the PivotField property of the PivotTable Class." I didn't think the OLAP Pivot Table would work for you since you aren't able to connect to our database.

  27. #27
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    That error tells us the PivotField name is not right.

    Try sending me the file.

  28. #28
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    The PivotField name is "[Reporting Date].[Weekly Calendar].[Date].[Fiscal Week]", not "Fiscal Week"

    So change this line:
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    If the pivot table exists on a separate sheet, simply qualify the pivot table reference - for example:
    Please Login or Register  to view this content.

    Better?

  29. #29
    Registered User
    Join Date
    07-30-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    21

    Re: Changing OLAP Pivot Table with GetPivotData function

    Ok that makes sense. I put that in and it keeps crashing my excel.

  30. #30
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Changing OLAP Pivot Table with GetPivotData function

    Okay. Looks like OLAP pivot tables ARE handled differently. I've reached my limit of experience, here - will ask others for help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Adding a series of cells in a Pivot Table using GetPivotData function
    By Pratik Bhatia in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-15-2013, 10:00 AM
  2. Replies: 1
    Last Post: 01-09-2013, 01:28 PM
  3. Extracting data from pivot table using getPivotData function
    By shantanuk in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 12-24-2012, 08:20 AM
  4. Pivot table - Changing OLAP Source
    By mut22 in forum Excel General
    Replies: 2
    Last Post: 03-06-2007, 07:25 AM
  5. Replies: 3
    Last Post: 11-10-2005, 08:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1