Version 3.4 brings named ranges into the DataPilot data source. Fine! Version 3.4 also brings named ranges per sheet, that is, the same name in different sheets are possible with no conflicts. However the DataPilot does not accept named ranges defined per sheet. Only named ranges defined globally. Feature or bug? No reference to this behaviour in the Help.
[This is an automated message.] This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it started right out as NEW without ever being explicitly confirmed. The bug is changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases. Details on how to test the 3.5.0 beta1 can be found at: http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1 more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Dear bug submitter! Due to the fact, that there are a lot of NEEDINFO bugs with no answer within the last six months, we close all of these bugs. To keep this message short, more infos are available @ https://wiki.documentfoundation.org/QA/NeedinfoClosure#Statement Thanks for understanding and hopefully updating your bug, so that everything is prepared for developers to fix your problem. Yours! Florian
reproduced in 3.6.1 on Fedora 64 bit Used Insert->Name->Define for defining name then Data->Pivot table->Create for DataPilot, but "Named range" is grey after 3.3.4 Insert->Name->Define was reworked, as I can see
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (4.3.5 or later): https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) Thank you for your help! -- The LibreOffice QA Team
Pivot tables using Named ranges work perfectly in branch 4.3. This is an outdated problem that is already solved. Closing as Fixed.
Apologies for not reading the Bug report properly. I can confirm that it is NOT possible to create a Pivot Table using a range named on the same sheet (only a Global name). This seems clearly to be a missing feature. So I can confirm the bug as NEW.
so correct status is NEW. anyway, Pedro, would you please retest with LibO 5.0.1 and tell if the issue is still there?
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.1.5 or 5.2.1 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20160920
Problem still there as per duplicate bug 107311
*** Bug 107311 has been marked as a duplicate of this bug. ***
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
I just verified the bug is still there, in the same form, in LO 6.0.3.2 on MacOS 10.12. I defined two named ranges via Sheet -> Named Ranges and Expressions. First one just for this sheet (sheet 1), then one global for the doc. I then did Data -> Pivot Table -> Create. After I just made the sheet-1 name, the "Named range" item in the Select Source dialog is greyed out and can't be clicked. After adding the global named range, the "Named range" in Select Source was not greyed out. The only name in the pulldown menu was the global one. So the sheet-specific one isn't registering, or "visible" to the Pivot Table -> Create dialog. More info in LO help dialog, as requested in recent email about old bugs: Version: 6.0.3.2 Build ID: 8f48d515416608e3a835360314dac7e47fd0b821 CPU threads: 8; OS: Mac OS X 10.12.6; UI render: default; Locale: en-US (en_US.UTF-8); Calc: group
code pointer: https://opengrok.libreoffice.org/xref/core/sc/source/ui/view/cellsh1.cxx#2754 // Populate named ranges (line 2796)
Let's give it a try with https://gerrit.libreoffice.org/#/c/58070/
I give up my patch, too complicate for me.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=73af06a6a7fcb2d92d36a45bbe54395bcba8dfb0 tdf#37268: use also sheet local range in Pivot It will be available in 6.2.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.
Caveat: the named range is written to and loaded from .xls (not .xlsx (yet?)) Excel might stumble over the sheet scope, and if it is only because of the '.' dot sheet name separator that is currently written instead of '!' exclamation mark. I don't have Excel at hand to test this. Does someone? Or could create a small document in Excel using a sheet-local named range and save that to .xls and .xlsx and attach here? Thanks.
Created attachment 144633 [details] 3 files with local range name The zip contains 3 files with local range name generated from MsOffice 2013: - xlsx - xls (from 2003) - xls (Excel 5.0/Excel 95)
On pc Debian x86-64 with master sources updated today, I could give a try to the 3 files from Excel. The pb is I should have put more data in them to test them completely. Eike: any thoughts?
If my previous is patch is wrong, don't hesitate to revert it. Unassign myself.
Created attachment 161209 [details] Document with local named range Open attached document. Press Ctrl+F3 to open 'Manage Names' dialog. Make sure, that MyNamedRangeLocal has scope Sheet1 in the list at the top. Cancel or repair if necessary. (Causion: 'global' is preselected in the Scope field at the bottom.) Insert Pivot Table by: Data > Pivot Table > Insert or Edit In the dialog take option 'Named range' and select the only entry. It is 'Sheet1'.MyNamedRangeLocal. OK In the dialog drag 'Person' to 'Row Fields' and 'Value' to 'Data Fields'. OK. Save. Inspect file You find in content.xml table:name="MyNamedRangeLocal" as attribute in <table:named-range> in <table:named-expresseions> in first <table:table>. table:name="'Sheet1'.MyNamedRangeLocal" in <table:source-cell-range> in <table:data-pilot-table> in <table:data-pilot-tables>. Both table:name attributes must have the same values, see section 19.677.12 in part 3, ODF 1.3. Reopen the file, click into the pivot table and use 'Properties...' from its context menu. Open + from 'Source and Destination'. Notice option 'Named range' has empty value and is disabled. The current solution for local named ranges in pilot tables does not work.
(In reply to Regina Henschel from comment #24) > ... > The current solution for local named ranges in pilot tables does not work. Revert patch is waiting for review here: https://gerrit.libreoffice.org/c/core/+/94748
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/818af4717bd7d4fbb6f622128c99259efb0714c9 Revert "tdf#37268: use also sheet local range in Pivot" It will be available in 7.0.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Reverted in master branch now. If someone wants to revert in 6.4 branch, don't hesitate to do it!
If reloading such document leads to the original query not being accessible then reverting the feature in this case might indeed be best. However, I consider ODF 1.3 part 3 section 19.677.12 to be insufficiently defined, it (likely, didn't check now) was already defined such way in ODF 1.2 before sheet-local names were even implemented.
Whether a name is local or not is determined by the position of the <table:name-expressions> Element in file (9.4.11 in ODF 1.2 and ODF 1.3). From that definition, a local named range as source cannot work with a pivot table, which is located on a different sheet. So the UI should only list local named ranges as source, which belong to the target of the pivot table. The current default target for a pivot-table is a new sheet, so in this case no local named ranges should be listed as source. If I make a document with local named range, put the pivot table on the same sheet as the local named range and manually change the file so, that the names are identical, then it still doesn't work. For me the problem is not in the implementation of local named ranges, but the way the pivot table interprets <table:source-cell-range>. (In reply to Eike Rathke from comment #28) > However, I consider ODF 1.3 part 3 section 19.677.12 to be insufficiently > defined, it (likely, didn't check now) was already defined such way in ODF > 1.2 before sheet-local names were even implemented. It might be necessary to add a note, that for getting the cell range from the table:name attribute the local named ranges of that sheet, which is given by the table:target-range-address attribute, has to be considered too. Sheet-local names already exist in ODF 1.2, new in ODF 1.3 is the possibility to use a named range as source for a pivot table.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-6-4": https://git.libreoffice.org/core/commit/6f1ca3db3a228fa0513536a82079637e6e025a15 Revert "tdf#37268: use also sheet local range in Pivot" It will be available in 6.4.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Sorry if I misunderstood the patch objective, but, in my version of LO (7.0.4.2-Linux), I still cannot use "Named range" as source for Pivot Tables. It is the same if I try to create a new Pivot table, or simply change Source in a existing one : the "Named range" option remains greyed... Of course, "Named Ranges" exist in my worksheet. When I use menu "Data / Select Range...", I can see them all. And they work fine : they are dynamically expanded when I add new rows/columns, as per my LO options choice. But they are not proposed in the PT creation popup, nor in the PT properties. Am I doing something wrong, or is the bug still there ? Thanks
@Thierry: I was having the same issue as you did: I also tried creating a named range using Data -> Define Range. This did not work, as you stated. What did work for me was to create a named range using the field on the left next to the formula field. As this is hard to explain, here is a video with the way that worked for me: https://youtu.be/q5Gqq_OpOos?t=219
I confirm this bug. As the previous comment, creating a named range using the field on the left next to the formula field worked for me too.
@frontlemon: please don't fiddle with bug fields, Version is the first version the behaviour was observed, Hardware and OS were already generally set. Thanks.