Bug Hunting Session
Bug 89186 - GETPIVOTDATA gives #REF! with "Field n / Item n" syntax.
Summary: GETPIVOTDATA gives #REF! with "Field n / Item n" syntax.
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.5.2 release
Hardware: Other All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.3.0 target:5.2.0.1 target:5.1.5
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2015-02-06 19:55 UTC by Stevenson Davies
Modified: 2016-10-25 18:54 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Brand new LO spreadsheet with illustrations. (44.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-02-06 19:55 UTC, Stevenson Davies
Details
Created new on Apache OO 4.1.0 equivalent with illustrations. (11.86 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-02-06 20:01 UTC, Stevenson Davies
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Stevenson Davies 2015-02-06 19:55:25 UTC
Created attachment 113189 [details]
Brand new LO spreadsheet with illustrations.

GETPIVOTDATA function gives bad #REF! result using "Field n / Item n" syntax.

Using LO Version: 4.3.5.2
Intel Core i5-4200U CPU x64-based processor
Windows 8.1

GETPIVOTDATA does not function as documented, according to LibreOffice Calc Help:
You should not get “#REF!” ("Error: Not a valid reference") as long as "the pivot table contains only a single result value that fulfills all of the constraints, or a subtotal result that summarizes all matching values".

I am taking "constraints" to include constraints applied using the "Field n / Item n" syntax.  Or if "constraints" is referring to the "constraints" syntax branch of the Help text, then I'm talking about the "Field n / Item n" syntax branch.

Works OK on Apache OpenOffice 4.1.0

Start with fresh Calc file.  Enter the following:
Col1	Col2	col3
bge	47	12
tpx	1.7	1.37
mrz	42	8
tpx	1.7	88.68
mrz	33	12.01

Start a Pivot Table on the above.  Specify Row Fields Col1 and Col2 in that order; Data Field of col3.  Make it produce the Pivot Table starting at A1 on a new sheet.

GETPIVOTDATA("Col3",$A$1,"Col1","bge") yields "#REF!" - should yield 12.
You should get the result 12 because there is only one "bge" row in the source data, and of course only one "bge" row in the Pivot Table.

GETPIVOTDATA("Col3",$A$1,"Col1","bge","Col2",47) yields 12 as it should.
But you should not have to specify the Col2 field, which is not required to pin down the qualifying row.

Same approach but using Col1="mrz" requires both Col1 and Col2 to work; no complaint about that.

Same approach but using Col1="tpx" with Col2 unspecified yields "#REF!" - should yield 90.05.
This is because, although there are more than 1 source rows, they all have the same Col2 value and thus summarize into a single Pivot Table row.
Comment 1 Stevenson Davies 2015-02-06 20:01:16 UTC
Created attachment 113190 [details]
Created new on Apache OO 4.1.0 equivalent with illustrations.

Open with Apache OO and compare with other attachment "113189: Brand new LO spreadsheet with illustrations." you have opened in LibreOffice.
Comment 2 Buovjaga 2015-02-08 13:14:51 UTC
Confirmed with attachment 113189 [details].

LibO 4.4 & 4.5 give:
bge	#REF!
mrz	#REF!
tpx	#REF!
mrz	#REF!
qws	#REF!
bge	#REF!


LibO 3.5.0 gives:
bge	12
mrz	#REF!
tpx	90.05
mrz	#REF!
qws	#REF!
bge	12

Win 7 Pro 64-bit, LibO Version: 4.4.0.3
Build ID: de093506bcdc5fafd9023ee680b8c60e3e0645d7
Locale: fi_FI

Version: 4.5.0.0.alpha0+
Build ID: 99c00b090533da9818444be2831b8da0e713e5f9
TinderBox: Win-x86@62-TDF, Branch:MASTER, Time: 2015-02-04_06:38:53
Locale: fi_FI

Ubuntu 14.10 64-bit
Version: 4.4.0.3
Build ID: 40m0(Build:3)
Locale: en_US

LibreOffice 3.5.0rc3 
Build ID: 7e68ba2-a744ebf-1f241b7-c506db1-7d53735
Comment 3 Matthew Francis 2015-02-22 04:12:08 UTC
Looks like this set of commits is where the GETPIVOTDATA formulae stopped working


commit ac569ed4cf5064248b9952f182f6572f20dc9bcb
Author: Kohei Yoshida <kohei.yoshida@gmail.com>
Date:   Mon Mar 11 17:16:54 2013 -0400

    fdo#60300: Work-in-progress change to rework pivot table core.
    
    The idea is to avoid parsing the pivot table sheet output in order
    to calculate GETPIVOTDATA.  The table outout is configurable, and it
    will only be more configurable in the future.  The gist of my rework
    is to calcualte the result of GETPIVOTDATA with the internl result
    tree alone.
    
    Also, the same result tree can be used for drill down too, which also
    currently parses the table output, therefore subject to the same
    limitation & fragility.
    
    Change-Id: Ib0147e2aa2b710dfd627df7f535a685301214a52

........

commit a210575baa5bc72dc0392eeb20b2da58b6cf14ae
Author: Kohei Yoshida <kohei.yoshida@gmail.com>
Date:   Thu Apr 18 23:10:49 2013 -0400

    Handle the old syntax for GETPIVOTDATA.
    
    The old syntax is Calc only (not Excel), and is specified in ODF 1.2.
    
    Change-Id: I9551d408ae136d3567375e905b1273103a8f616a
Comment 4 Robinson Tryon (qubit) 2015-12-13 11:12:16 UTC Comment hidden (obsolete)
Comment 5 Eike Rathke 2016-06-07 08:56:46 UTC
Investigating.
Comment 6 Commit Notification 2016-06-07 14:38:08 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=81f2bbc2fc42688d5f884d3dde58abe4c265694a

Resolves: tdf#89186 descend into dimension children to obtain leaf result

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2016-06-07 15:56:07 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f6b76c9d7fa989ac9475ff6d2ab45c257f96ad3e&h=libreoffice-5-2

Resolves: tdf#89186 descend into dimension children to obtain leaf result

It will be available in 5.2.0.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Eike Rathke 2016-06-07 16:05:46 UTC
Pending review  for 5-1
Comment 9 Eike Rathke 2016-06-07 16:10:10 UTC
Pending review https://gerrit.libreoffice.org/26028 for 5-1
Comment 10 Commit Notification 2016-06-09 11:11:56 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7a3be3a229d10665d724ffc215c9dca4ab8dd0bc&h=libreoffice-5-1

Resolves: tdf#89186 descend into dimension children to obtain leaf result

It will be available in 5.1.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.