Created attachment 90672 [details] Spreadsheets used for testing Versions used for the testing: Version: 4.2.0.0.beta2+ Build ID: 02180aed7dc0b8c5f9cc23b319adc2386a9aab69 Version 4.0.6.2 (Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24) MS Excel 2003 Inconsistent results from GETPIVOTDATA function between LibreOffice 4.0.6.2 and 4.2.0.0.beta2+ (and Excel which acts as 4.0.6.2) 2 Spreadsheets attached: PivotTableExcel.xls, when opened with LibreOffice 4.2.x GETPIVOTDATA cannot access the pivot table data unless the string "Sum of " is inserted into the Data Field argument. Works OK with 4.0.6.2 PivotTableLibreOffice.xls, PivotTable created with LibreOffice, shows inconsitencies beween 4.0.6.2 and 4.2.x handling strings with upper/lowercase and spaces. Works OK with 4.0.6.2
Created attachment 90673 [details] PivotTableExcel.xls
Created attachment 90674 [details] PivotTableLibreOffice.ods
Hi Ceri, thanks for reporting. In relation with the need to define the all restrictions you can see developer comment in: https://bugs.freedesktop.org/show_bug.cgi?id=68666#c9 the other issue is about case sensitivity, I don't know if this change is intended or not. Please Kohei, can you inform what is right about case issue.
Well taking a look into Open Document Format v1.2 approved as a OASIS Standard on 29 September 2011 http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2.odt. As I understand. For the first syntax: "Field and member names are case-insensitive." For the second it is not clear: "Each of the other entries specifies a constraint in the form Field[Member] (with literal characters [ and ])" "The possible function names are the same as in the table:function attribute of the <table:data-pilot-subtotal> element, case-insensitive" But the function seems case-insensitive with the first syntax B5 in the attached file PivotTableLibreOffice.ods and not with the second B4. I think it is a bug about case-insensitive.
Hi, thanks for the reply. Sorry I started looking at an issue with GETPIVOTDATA importing Pivot tables from Excel and found some other issues along the way, so I listed them all. LibreOffice 4.0.6 handles all of this OK, 4.2.x does not. I waited for the fix for bug 69518 before retesting and reporting. Here they are broken down: PivotTableLibreOffice.ods: 1. Cell A2, Different results returned by 4.0.6 and 4.2. You may be right with Bug 68666, not sure. 2. Cells A4 and A5 demonstrate that the second format of GETPIVOTDATA seems to be case sensitive. 3. Cell A10, Different handling of Target Value format with spaces at the end of the field item string. 4.2.x behaves differently to 4.0.6 PivotTableExcel.ods This is the bug I was looking for, which I think is with using GETPIVOTDATA with a Pivot Table created in Excel. Again it works OK in 4.0.6. When this spreadsheet is opened with 4.2.x none of the valid test GETPIVOTDATA examples work (A1 to A7,A10,A12,A17). Only cell A15 works and this is where I have inserted the text "Sum of " into the TargetField string.
This bug report consists of multiple bugs, actually. We'll need to handle them one at a time.
I'll take it.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8728f8e8705cfb6875a315aef85ec6004604e702 fdo#72645: Allow GETPIVOTDATA to get result from leaf node of result tree. 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.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=17b00767948f7add229ec589c06cd8c898032ffa fdo#72645: Case-insensitive string comparison in GETPIVOTDATA. 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.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e5dcc06cf898109a1dd8218cf5eeecb83fb01492&h=libreoffice-4-2 fdo#72645: Allow GETPIVOTDATA to get result from leaf node of result tree. It will be available in LibreOffice 4.2. 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.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=fd58eb3e92fc82d45130e06f89f9fcee29d08f1e&h=libreoffice-4-2 fdo#72645: Case-insensitive string comparison in GETPIVOTDATA. It will be available in LibreOffice 4.2. 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.
Backport request for 4.1 is underway on gerrit: https://gerrit.libreoffice.org/#/c/7284/ https://gerrit.libreoffice.org/#/c/7285/ Meanwhile I'll mark this fixed.
Just for the sake of documenting it, there are two ways you can legally get a result value via GETPIVOTDATA. One is to specify full path to the subtotal value i.e. in this specific example you specify the Type being either M or F and the Name. Alternative way is to specify only one field name / member pair whose support was dropped during the rework (by accident). The 2nd way works if and only if that pair occurs only once within the pivot table output *and* it has a corresponding subtotal value displayed in the table. And this should work both the old syntax and the new syntax.
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=83eb0b64980b405bc94ed19f3bcb60860f86e7d4&h=libreoffice-4-1 fdo#72645: Allow GETPIVOTDATA to get result from leaf node of result tree. It will be available in LibreOffice 4.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.
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=7c4a58833df3931c168c57be3239e904e3a3baba&h=libreoffice-4-1 fdo#72645: Case-insensitive string comparison in GETPIVOTDATA. It will be available in LibreOffice 4.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.
Confirmed working in: Version: 4.2.1.0.0+ Build ID: 8308f6e5f50bcdd5cd4e5511a8a3bd5c64c93e2b TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-01-08_09:46:01 Many thanks
Sorry for the delay in check. Seems solved for: Win7x64 Version: 4.2.0.2 Build ID: cd65d6220c5694ee7012d7863bcde3455c9e3c30 Version: 4.2.1.0.0+ Build ID: d72321b665f54946cf603e6f30740f31151c898f TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-01-16_02:01:06 Version: 4.3.0.0.alpha0+ Build ID: 42f551d524a1df46f6a311d5897ac30bd8fc1aaf TinderBox: Win-x86@39, Branch:master, Time: 2014-01-15_22:44:37 A little difference: In A21 now (from 4.2) results in a value 21 while in LibreOffice 4.0.6 and in Excel-(as reported) gives a #REF!