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)
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.
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.
I will try take a look at this
after talking to kohei it seems more involved ( and I go on vacation day after tomorrow ) - putting back to the list for the moment
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
Thanks Noel. I've managed to squeeze some time, so I'll take a look at this.
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.
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.
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.
Pending on review in gerrit: https://gerrit.libreoffice.org/#/c/4853/
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.
Thanks so much Kohei, when available to test I'll try to do it and report the results.
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.
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.
> It will be available already in LibreOffice 4.1.0. Great news - THANKS!
Really great news. I can not test for now, because there are several days without builds in windows.
Verified fixed in Win7x64Ultimate Version: 4.2.0.0.alpha0+ Build ID: 22fa63a4fac7e86bc199092c48f0d2954435bf46 I can not find builds with 4.1 to verify.
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.
Veryfied fixed in 4.1.0.3 Mac / x86
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
Created attachment 86046 [details] DataPilotConstraints.ods
Created attachment 86047 [details] DataPilotConstraints Ok with OOo
Created attachment 86048 [details] DataPilotConstraints KO with LibO
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 !
(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