Apache OpenOffice (AOO) Bugzilla – Issue 29848
copied sheet: chart-copy references original data instead of copied data
Last modified: 2017-05-20 11:41:46 UTC
I get a unexpected reaction... Steps to reproduce... 1. Enter some simple data (one cell is enough) 2. Draw a chart on the same sheet 3. Copy this sheet 4. What you get is not what you might expect You might like the new chart using the new sheet as source. But instead both chart use the same (old) source. The datasource reference should not be held absolute, but relative to its own sheet
Hi, wrong component, I corrected this to Chart, Frank
tk->bh: i think both should be possible.
Hi Bettina, one for you. tk
Reassigned to Ingrid.
->BM: Please take care of this Issue. I also think that the current behaviour is not a good default.
It looks like Calc is able to convert ranges from the source sheet to the new sheet (like $Sheet1 stays $Sheet1 while Sheet1 becomes Sheet4 - or whatever the new sheet is called), so it should be possible to change all affected ranges a chart uses. Currently (in the new chart) all ranges are written with $s, but we should change this anyway, because otherwise users would never get notice if the underlying range of a series is moved or not.
*** Issue 59775 has been marked as a duplicate of this issue. ***
*** Issue 61501 has been marked as a duplicate of this issue. ***
*** Issue 61499 has been marked as a duplicate of this issue. ***
Still see this in 2.3.1
*** Issue 85396 has been marked as a duplicate of this issue. ***
Issue 64604 seems like it may be similar? Still applies to 2.4.0.
Ahem, bm: You switched the status to STARTED in April 2005. Have you really started at that time? What is your state now? (I see that you currently have 66 issues of same or higher priority assigned - so this item probably is starving.) If somebody else wants to take a try on this, could you describe what has to be done and how much work you expect it to be?
->jondo: Well, we used STARTED as a synonym for ACCEPTED, which does not exist as a state in IssueZilla. So it just means we found it valid, but didn't really start with it. This is also the current state. (Meanwhile we would leave such isses on NEW). Well the solution is not so straight-forward here. If you have Sheet1 with a chart referring to cells on Sheet1 things are simple when you copy Sheet1. However you might have references to other sheets or to both, the same sheet and another sheet mixed. What do you want to do then? Automatisms like "when all references are on the same sheet, relink them to the new one" are not obvious to a user. So you would need some kind of user interaction. So, it is not so trivial to deal with this. If someone is interested in finding a solid solution this would be greatly appreciated.
Just an addition to the remark to hold the sheet references relative: A chart uses ranges from Sheet 1, Sheet 2 and Sheet 3. It resides on Sheet 2. You copy Sheet 2 and append it as Sheet 4. What happens? Is the new chart pointing to Sheet 1 (stays as before), Sheet 4 (was Sheet 2) and Sheet 3, or are all references relative, i.e. the new chart refers to ranges on Sheet 3 (was 1), Sheet 4 (was 2) and Sheet 5 (was 3)?
Well, this bug isn't to fix all the possible cases, just the most common ones. - Many times you have a graph from data of the same sheet, so treat this references relative. So on the copied sheet the graph should refer the copied data. - To respond on your more complex case. Many people would not expect to change any thing but the reference to the copied data. So the graph on sheet 4 should refer to the data of sheet 1, 3 and 4 - IMHO. Or more generally. Compare the action of copying a sheet to renaming a sheet. If you rename a sheet you get the graphs references to that very sheet changed. - If you copy it you might expect the equivalent behaviour for the graphs on the new sheet. Or does someone disagree with that?
Thomas, I fully agree. Btw., this is how Gnumeric behaves. I cannot test Excel at the moment, but it's certainly the same there.
change owner
reset to new
*** Issue 101581 has been marked as a duplicate of this issue. ***
This issue still occur in version 3.1. to jondo: Agree, Excel 2000 behaves same as Gnumeric. Hope this will be fixed soon.. Thanks.
Bug is still there in 3.1.1-beta. This is turning into one of the top annoyances of Calc-users at my office: 4 complaints in the last month (rising as the use of Calc is spreading beyond the initial test group). @iha: Is there anyone actively working on this bug?
I am not working on this issue. So if anyone would like to give it a try go-ahead!
*** Issue 92580 has been marked as a duplicate of this issue. ***
@weiz, please have a look at this one. Thanks!
shifting target due to limited resources
Created attachment 64875 [details] patch_20090921
@iha, the patch is submitted, please let me know your suggestions. Thank you!
@weiz, the patch does only work for charts where the cell ranges are arranged in standard way. More complex charts do break now while copying. I'll attach an example to show the problem.
Created attachment 64930 [details] example showing the problem
The methods used in the current patch do set a new rectangular range to the chart. That destroys the former structure. Look at method ScDocument::TransferDrawPage how the methods GetChartRanges() and SetChartRanges() are used there. This is the correct way that will preserve the structure. The method is called in case a sheet is moved to a different document. After changing the code please also make sure that the change notifications are send correctly. If the values in the new cells change, the chart should get updated. Something with the general update mechanism is broken in dev300m59 so you might need to update to m60 if you are on that version. Kind regards, Ingrid
Wouldn't this be a matter of getting the relative references to work properly. By removing the $sheet1 from the reference string in the chart and then copy the sheet. The expected behavior would be that the reference still are relative. How it works now is that the references are changed to absolute references when copying the sheet. Making this work would avoid all the possible conflicts with references to other sheets.
*** Issue 109897 has been marked as a duplicate of this issue. ***
I take back the issue to complete the fix.
Fixed in CWS chart43. For copied charts the references are adapted as follows: Data References to the source of the copy are changed to data references to the result of the copy. All other data ranges remain unchanged.
Thanks a lot, Ingrid! This greatly enhances the re-usability of charts in Calc. An important issue for a lot of OOo "power users" at my company. I will test it as soon as the first builds with this fix included hit the net. What build number will that be?
@stephan66, the exact build number is not known yet as the Childworkspace 'chart43' will go through a QA phase first before integration. But you can observe the status of CWS chart43 here: http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fchart43 If the CWS is integrated the field 'Milestone (integrated)' will show the version dev300mXX.
@Thomas, please verify in CWS chart43.
Seen ok in cws chart43 -> verified
*** Issue 23212 has been marked as a duplicate of this issue. ***
Although the declaration "Issue 23212 has been marked as a duplicate of this issue." is not really fair (I reported 23212 on Thu Dec 4 20:25:00 +0000 2003, i.e. is half a year earlier than 29848) - I am happy to hear that the issue will be resolved after more than 6 years! Thanks a lot, Ingrid!
I think a better way of phrasing this issue is that Chart components use ABSOLUTE data references by default, so if you copy a chart to a new sheet the data still pulls from the original place. I would like to see chart objects default to RELATIVE references, and have the option to toggle them to ABSOLUTE references using SHIFT+F4 as you can with individual cells
A regression was introduced: Issue 110849.
Confirming that this problem is solved in DEV300m80 downloaded from openoffice.org (openSuSE 11.0, x86_64, 4 GB memory) This solution is fine for now. I agree with rsking84 that being able to use both relative and absolute references in data ranges would make Calc's behavior more predictable. Calc now silently changes relative references to absolute references without warning. Defaulting to relative references would likely confuse and surprise a lot of inexperienced users, though. Default should be absolute references. It would make Calc stand out amongst spreadsheet programs (compared to Excel and Gnumeric)!
@rsking84 and stephan66, it would be nice if one of you could submit a separate issue for the relative<->absolute reference issue and describe there what is wanted. Thanks in advance!
*** Issue 113289 has been marked as a duplicate of this issue. ***
I have opened Issue #113664 as an enhancement to offer the ability to toggle between absolute and relative references in Charts. See http://www.openoffice.org/issues/show_bug.cgi?id=113664