+ Reply to Thread
Results 1 to 3 of 3

GetPivotData with OLAP(?) Data Connection

  1. #1
    Registered User
    Join Date
    04-11-2021
    Location
    Michigan
    MS-Off Ver
    2016
    Posts
    2

    GetPivotData with OLAP(?) Data Connection

    Hi,
    New here. To increase performance and reduce document size, I rebuilt my sales forecast pivot with Get-And-Transform - which allowed me to load my source data through a query instead of a tab in the document.

    For the most part it worked great - the document is about 1/10th original size, loads/saves much faster, and the new pivot still works. However, I was using GETPIVOTDATA to show a grand total in a cell above my pivot and am now getting #REF error there. My syntax is the same as that of previous documents (=GETPIVOTDATA("Grand Total",T37 ) - but I suspect that this no longer works with a pivot based on an query for a data source. (I couldn't determine if mine would be classified as an OLAP query).

    Is there a GETPIVOTDATA syntax which will work with a table using a "query" data source rather than conventionally loaded data in another tab?

    Any help greatly appreciated!

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,890

    Re: GetPivotData with OLAP(?) Data Connection

    Have you tried a simple = and then select the total cell? By default Excel will build the GETPIVOTDATA formula for you (which will work with an OLAP pivot too).
    Rory
    I drink, and I know things

  3. #3
    Registered User
    Join Date
    04-11-2021
    Location
    Michigan
    MS-Off Ver
    2016
    Posts
    2

    Re: GetPivotData with OLAP(?) Data Connection

    Hi rorya,
    That worked - thank you. I actually tried this method before posting, but it wasn't working at the time - which made me question whether it worked with OLAP pivots. It turns out that I had broken my pivot connection which is why GETPIVOTDATA didn't work.

    For sake of convenience for others searching, GETPIVOTDATA syntax with OLAP does differ a bit in that it appears to use an in-built "Measures" class and an object link to the table:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    My pivot has a "Grand Total" row at bottom, but the reference to "[Measures]" is apparently invoking a method of the table object. Now I'm curious what other methods might be available to GETPIVOTDATA in this manner.

+ 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. Excel Connection to OLAP Cube
    By Guy007 in forum Excel General
    Replies: 0
    Last Post: 03-10-2016, 10:50 AM
  2. Changing OLAP Pivot Table with GetPivotData function
    By Mark89 in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 08-01-2014, 02:21 PM
  3. Execute code after OLAP connection has refreshed
    By Polis15 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-11-2014, 09:27 AM
  4. Replies: 1
    Last Post: 01-09-2013, 01:28 PM
  5. [SOLVED] GETPIVOTDATA from OLAP cube
    By jaroscak@iol.cz in forum Excel General
    Replies: 3
    Last Post: 08-15-2006, 03:25 PM
  6. Excel OLAP Connection Settings
    By christophe.leroquais@gmail.com in forum Excel General
    Replies: 1
    Last Post: 05-09-2006, 02:35 PM
  7. Replies: 0
    Last Post: 05-19-2005, 11:06 AM

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