Bug 37268 - Data Pilot and sheet local defined named range insert menu entry inactive
Summary: Data Pilot and sheet local defined named range insert menu entry inactive
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.0 Beta5
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: target:6.4.5
Keywords:
: 107311 (view as bug list)
Depends on:
Blocks: Pivot-Table
  Show dependency treegraph
 
Reported: 2011-05-16 12:21 UTC by Olivier Hallot
Modified: 2020-05-25 18:28 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
3 files with local range name (14.63 KB, application/zip)
2018-09-03 15:08 UTC, Julien Nabet
Details
Document with local named range (10.21 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-23 23:21 UTC, Regina Henschel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Olivier Hallot 2011-05-16 12:21:29 UTC
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.
Comment 1 Björn Michaelsen 2011-12-23 12:07:24 UTC Comment hidden (obsolete)
Comment 2 Florian Reisinger 2012-08-14 14:00:34 UTC Comment hidden (obsolete)
Comment 3 Florian Reisinger 2012-08-14 14:01:42 UTC Comment hidden (obsolete)
Comment 4 Florian Reisinger 2012-08-14 14:06:24 UTC Comment hidden (obsolete)
Comment 5 Florian Reisinger 2012-08-14 14:08:26 UTC Comment hidden (obsolete)
Comment 6 sasha.libreoffice 2012-09-05 07:56:29 UTC
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
Comment 7 QA Administrators 2015-01-05 17:52:30 UTC Comment hidden (obsolete)
Comment 8 Pedro 2015-01-21 10:40:09 UTC
Pivot tables using Named ranges work perfectly in branch 4.3.
This is an outdated problem that is already solved.
Closing as Fixed.
Comment 9 Pedro 2015-01-21 10:45:10 UTC
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.
Comment 10 tommy27 2015-09-17 05:13:16 UTC Comment hidden (obsolete)
Comment 11 QA Administrators 2016-09-20 10:32:26 UTC Comment hidden (obsolete)
Comment 12 Alex Thurgood 2017-05-10 14:18:07 UTC
Problem still there as per duplicate bug 107311
Comment 13 Alex Thurgood 2017-05-10 14:18:36 UTC
*** Bug 107311 has been marked as a duplicate of this bug. ***
Comment 14 QA Administrators 2018-05-11 02:32:42 UTC Comment hidden (obsolete)
Comment 15 David Strozzi 2018-05-15 05:51:39 UTC
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
Comment 16 Julien Nabet 2018-07-25 11:28:59 UTC
code pointer:
https://opengrok.libreoffice.org/xref/core/sc/source/ui/view/cellsh1.cxx#2754
// Populate named ranges (line 2796)
Comment 17 Julien Nabet 2018-07-26 10:03:59 UTC
Let's give it a try with https://gerrit.libreoffice.org/#/c/58070/
Comment 18 Julien Nabet 2018-08-20 20:01:18 UTC
I give up my patch, too complicate for me.
Comment 19 Commit Notification 2018-09-03 12:40:24 UTC
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.
Comment 20 Eike Rathke 2018-09-03 12:45:21 UTC
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.
Comment 21 Julien Nabet 2018-09-03 15:08:43 UTC
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)
Comment 22 Julien Nabet 2018-09-04 20:10:28 UTC
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?
Comment 23 Julien Nabet 2018-12-03 13:21:17 UTC
If my previous is patch is wrong, don't hesitate to revert it.
Unassign myself.
Comment 24 Regina Henschel 2020-05-23 23:21:36 UTC
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.
Comment 25 Julien Nabet 2020-05-24 08:49:19 UTC
(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
Comment 26 Commit Notification 2020-05-24 10:05:46 UTC
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.
Comment 27 Julien Nabet 2020-05-24 10:06:38 UTC
Reverted in master branch now.
If someone wants to revert in 6.4 branch, don't hesitate to do it!
Comment 28 Eike Rathke 2020-05-25 15:22:20 UTC
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.
Comment 29 Regina Henschel 2020-05-25 17:16:47 UTC
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.
Comment 30 Commit Notification 2020-05-25 18:28:48 UTC
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.