Bug Hunting Session
Bug 66655 - EDITING: GETPIVOTDATA() gives #REF!
Summary: EDITING: GETPIVOTDATA() gives #REF!
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.0.1 rc
Hardware: Other Windows (All)
: medium critical
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.2.0 target:4.1.0
Keywords: regression
Depends on:
Blocks: mab4.1
  Show dependency treegraph
 
Reported: 2013-07-06 23:25 UTC by m.a.riosv
Modified: 2013-11-20 16:14 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample GETPIVOTDATA calculation error (14.95 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-07-06 23:25 UTC, m.a.riosv
Details
DataPilotConstraints.ods (11.23 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-09-18 08:26 UTC, pierre-yves samyn
Details
DataPilotConstraints Ok with OOo (17.38 KB, image/png)
2013-09-18 08:28 UTC, pierre-yves samyn
Details
DataPilotConstraints KO with LibO (15.96 KB, image/png)
2013-09-18 08:29 UTC, pierre-yves samyn
Details

Note You need to log in before you can comment on or make changes to this bug.
Description m.a.riosv 2013-07-06 23:25:03 UTC
Created attachment 82129 [details]
Sample GETPIVOTDATA calculation error

GETPIVOTDATA() always gives #REF! as result. Attached file created with 4.0.4.2
With:
Win7x64Ultimate
Version: 4.1.0.1 Build ID: 1b3956717a60d6ac35b133d7b0a0f5eb55e9155
Version: 4.2.0.0.alpha0+ Build ID: 40026a2fcd88851e18f701a10c3cddc87b549476

The attached file produce normal result in:
Version 4.0.4.2 (Build ID: 9e9821abd0ffdbc09cd8c52eaa574fa09eb08f2)
Comment 1 GerardF 2013-07-07 20:52:43 UTC
Reproducible with 4.2.0.0alpha0+
I can also confirm correct results with 4.0.4

Tried with various options like "ignore empty rows" without success.
Comment 2 cpohle 2013-07-09 15:32:20 UTC
I can confirm this bug.

Having opened quite a complex spreadsheet with lots of "GETPIVOTDATA" calculations created in 4.0 in the 4.1 pre-release, all calculations where gone.

Re-Opening the file with the 4.0 version did not bring the data back.

This should be considered as highly important for the 4.1 release, as it renders LO useless for many serious business tasks.
Comment 3 Noel Power 2013-07-10 10:26:29 UTC
I will try take a look at this
Comment 4 Noel Power 2013-07-10 16:52:59 UTC
after talking to kohei it seems more involved ( and I go on vacation day after tomorrow ) - putting back to the list for the moment
Comment 5 Noel Power 2013-07-11 07:41:28 UTC
just for future info, some summary of the problem...

it occurs in 
ScDPObject::GetPivotData(...)

where there is a failure to match DataPilotFieldOrientation_DATA type ScDPSaveDimension objects for the given fieldname e.g. in this case the field name

"Average - value" relates to the field named 'value'

there have been changes to the code (since 4.1) to use results stored within the result tree stored inside the pivot object instead of the table output (which is what was previously used) Part of the problem here is the "Average - " part of the name "Average - value" is created from the function associated with the data field and that name is not stored (by design) in the result tree. Further more the other fields ( from different functions on the same 'value' field e.g Count - value etc. ) have just the same field name stored ( actually adjusted by the code for uniqueness, "value, value*, value**" etc.

cc'ing Kohei here because after some discussion on IRC he will handle this when he has the chance
Comment 6 Kohei Yoshida 2013-07-11 18:57:38 UTC
Thanks Noel.

I've managed to squeeze some time, so I'll take a look at this.
Comment 7 Kohei Yoshida 2013-07-11 21:55:19 UTC
Actually, the data field name mis-match is only part of the problem. There are also issues with finding the right node in the result tree as well. This one needs multiple fixes in several different places.
Comment 8 Kohei Yoshida 2013-07-11 23:51:51 UTC
Ok. I did fix the name matching problem on the result tree side. Now I need to just fix the other problem Noel mentioned and all will be good.
Comment 9 Commit Notification 2013-07-12 02:11:05 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=324d87a130d929fe59379ed8c15e33f7c74e38dd

fdo#66655: Get GETPIVOTDATA to work again.



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 10 Kohei Yoshida 2013-07-12 02:12:51 UTC
Pending on review in gerrit: https://gerrit.libreoffice.org/#/c/4853/
Comment 11 Commit Notification 2013-07-12 10:57:28 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b4f35c69bfe56c1de0507e151dd8895563983723&h=libreoffice-4-1

fdo#66655: Get GETPIVOTDATA to work again.


It will be available in LibreOffice 4.1.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 12 m.a.riosv 2013-07-12 11:35:54 UTC
Thanks so much Kohei, when available to test I'll try to do it and report the results.
Comment 13 Kohei Yoshida 2013-07-12 14:27:31 UTC
I'll mark this fixed.  I'm not sure if we will squeeze this fix into 4.1.0 final (since we are a bit late for that target), but I've left a suggestion in gerrit for possible 4.1.0 inclusion.
Comment 14 Commit Notification 2013-07-15 11:27:39 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=556519f0a1dcc92eab5078dcbaaf2cec4dbaa179&h=libreoffice-4-1-0

fdo#66655: Get GETPIVOTDATA to work again.


It will be available already in LibreOffice 4.1.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 15 cpohle 2013-07-15 13:20:14 UTC
> It will be available already in LibreOffice 4.1.0.

Great news - THANKS!
Comment 16 m.a.riosv 2013-07-15 20:28:42 UTC
Really great news.

I can not test for now, because there are several days without builds in windows.
Comment 17 m.a.riosv 2013-07-17 00:21:32 UTC
Verified fixed in 
Win7x64Ultimate
Version: 4.2.0.0.alpha0+ Build ID: 22fa63a4fac7e86bc199092c48f0d2954435bf46
I can not find builds with 4.1 to verify.
Comment 18 m.a.riosv 2013-07-17 21:02:12 UTC
Verified fixed in
Versión: 4.1.1.0.0+
Id. de compilación: c1f0f890f5065f88164add33707228f8c6d5755
TinderBox: Win-x86@6-debug, Branch:libreoffice-4-1, Time: 2013-07-17_16:17:47

Daily windows builds have changed their path, I have found it today.
Comment 19 cpohle 2013-07-18 12:22:08 UTC
Veryfied fixed in 4.1.0.3 Mac / x86
Comment 20 pierre-yves samyn 2013-09-18 08:25:34 UTC
Hello

Sorry to reopen, but the fix does not solve the problem in all cases, especially with this syntax:
GETPIVOTDATA (pivot table, Constraints)

The attached spreadsheet DataPilotConstraints.ods includes examples of syntax that worked in previous versions and does not work (see screenshots).

Regards
Pierre-Yves
Comment 21 pierre-yves samyn 2013-09-18 08:26:44 UTC
Created attachment 86046 [details]
DataPilotConstraints.ods
Comment 22 pierre-yves samyn 2013-09-18 08:28:24 UTC
Created attachment 86047 [details]
DataPilotConstraints Ok with OOo
Comment 23 pierre-yves samyn 2013-09-18 08:29:05 UTC
Created attachment 86048 [details]
DataPilotConstraints KO with LibO
Comment 24 Michael Meeks 2013-09-18 09:06:32 UTC
pierre-yves - please can you open a new bug for this newly discovered regression :-) feel free to make it a mab4.1 & regression.

We really don't want every issue with getpivotdata in the future to involve a re-opening of this same bug :-) - it makes it impossible to track the issues. It'd be good to have a 'See also' of your new bug of course too.

It'd be great to re-close this bug when that is split out; thanks !
Comment 25 pierre-yves samyn 2013-09-18 10:03:16 UTC
(In reply to comment #24)
> pierre-yves - please can you open a new bug for this newly discovered
> regression :-) feel free to make it a mab4.1 & regression.


Sorry for the wrong move ... I opened a new bug 69518

Regards
Pierre-Yves