Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Need ability to change datapilot source range|
|Status:||CLOSED FIXED||QA Contact:||issues@sc <issues>|
|Priority:||P4||CC:||andre.schnabel, cmoulin, eva-email, gerhard.oettl.ml, issues, jody, kamataki, kyoshida, marc.galmard, marius.andreiana, mdelorme, mmeeks, niklas.nebel, pagalmes.lists, pescetti, sragavan, stx123, thomas, tora3, utomo.prawiro, y-catch|
|Version:||OOo 1.1||Keywords:||oooqa, usability|
|Issue Type:||PATCH||Latest Confirmation in:||---|
|Issue Depends on:||86791|
|Issue Blocks:||15522, 47145|
Description antonbijl 2003-12-18 07:49:29 UTC
There is currently no way of changing the source range of an existing datapilot table where the source data is on a sheet in the same workbook. This feature is definitely available on MS Excel and makes working with pivot tables much easier when new data rows are constantly being added to the data source. Example: (See attached file) - Sheet1 contains 5 rows of data in two colums. Column A contains a list of names and Column B a list of values. - Select the data (A1:B6) - Create a datapilot table using Data => Datapilot => Start menu command - In the select source dialog pick 'Current Selection' - Drag 'Name' to the Column area and 'Value' to the Data area, so that the sum of values per unique name will appear in the datapilot table - Click on the 'More' button and select the option for creating the datapilot table on a new sheet and finally click OK to generate the datapilot table - Now go back to sheet1 and enter data in row 7 (e.g. Albert, 2) In the above example in order to get the datapilot to recognize the new row that has been added it (the datapilot table) must be deleted altogether and recreated from scratch using the same steps as above but selecting A1:B7 instead of A1:B6. The ideal solution would be if there could be an option on the datapilot table to right-click and select 'change source' or something similar and simply change the source. Or (similar to Excel) have an option to just go through the datapilot setup dialogs (wizard) again (this time defaulting to all the options that have already been chosen). A few workarounds are available but these are less than ideal. Cheers Anton
Comment 1 antonbijl 2003-12-18 07:55:11 UTC
Created attachment 12005 [details] Example of datapilot problem where adding row to source data
Comment 2 frank 2004-01-07 12:14:06 UTC
Hi Bettina, 1 4 u. Frank
Comment 4 sragavan 2004-11-18 11:22:35 UTC
patch created is against OOO113
Comment 5 niklas.nebel 2005-01-12 09:38:58 UTC
I agree this would be a useful enhancement, but there's more to it than the patch in its current state handles: - The dialog layout has to be changed to avoid cramming the additional controls so close to the bottom. - Other source types have to be handled (the edit field disabled, probably - as is, the dialog would crash if called with database data). - It has to be defined how the field settings are updated when the source range is changed.
Comment 6 sragavan 2005-01-31 11:06:55 UTC
Created attachment 22030 [details] Updated patch with respect to m65.
Comment 7 sragavan 2005-01-31 11:07:54 UTC
nn: I have updated few things and it works perfectly for me. Can you be bit ellaborate about the database stuff. Ill try and test it out.
Comment 8 niklas.nebel 2005-01-31 14:11:57 UTC
Database: Select DataPilot/Start from the menu, then "Data source registered in OpenOffice.org" in the "Select Source" dialog, select any database table ("biblio" from the default Bibliography database is fine). The ScDPLayoutDlg is then called with an ScDPObject that has no sheet source descriptor (GetSheetDesc returns null). The code to select which edit is updated on cell range selection also doesn't work. See ScPrintAreasDlg (areasdlg.cxx) for the right way to handle dialogs with several reference input fields. Finally, note that it's too late now for UI changes for OOo 2.0. This will have to wait for a later version (target milestone is already "later").
Comment 9 sragavan 2005-02-09 11:26:56 UTC
Created attachment 22367 [details] Fix for remote database
Comment 10 sragavan 2005-02-09 11:33:45 UTC
nn: I have fixed the remote database crash issue i think so(?) :-). This patch in combi with the prev patch wrt to m65 would solve the issue. The edit field update works but may not be the right way. Ill try to put it the right way and submit again. Please correct me of any issues.
Comment 11 frank 2005-06-07 11:18:53 UTC
added nn to cc list for observation of this task
Comment 12 frank 2005-06-07 11:19:31 UTC
*** Issue 48586 has been marked as a duplicate of this issue. ***
Comment 13 tboerkel 2005-09-24 21:57:37 UTC
Is this supposed to be included in Beta 2? If yes, I can't find it. If no, is there a workaround?
Comment 14 amerj 2005-10-06 08:07:31 UTC
The only workaround I know of is to insert new data - say - 100 rows from bottom of data range, then resort and the data is "recognised". If rows are added at / near bottom then they are NOT included. Shame really because OOo is generally quite nice to use ..... After seeing news yesterday, I wonder if Google can help ;-)
Comment 15 stx123 2006-02-24 14:55:41 UTC
2.0 has been released some time ago. Michael, Niklas, now might be a good point in time to revisit the patch. Btw has the dependent issue 47145 been fixed?
Comment 16 stx123 2006-03-15 16:52:05 UTC
Oops, I intended to reassign to Níklas. Sorry, "n".
Comment 17 niklas.nebel 2006-03-17 16:07:29 UTC
The mentioned problem with selecting the Edit (GetRefButton etc in ScDPLayoutDlg::SetReference) is still there. The dialog layout isn't quite right either (the "More" button isn't at the bottom, an when using it, the "Selection from" edit is cut off). Generally, the dialog layout also has to be agreed upon with User Experience.
Comment 18 mmeeks 2006-03-29 13:53:48 UTC
Jody this is yours to track.
Comment 19 niklas.nebel 2006-05-11 15:47:23 UTC
Changing target (last integration for 2.0.3 is over).
Comment 20 mmeeks 2006-05-30 15:47:40 UTC
fixes in http://www.openoffice.org/nonav/issues/showattachment.cgi/24918/sc-pv-crasher.diff are necessary. No idea why this is now an ENHANCEMENT, and not a PATCH, changing type back. nn: all that remains is cleaning up some GUI warts right ?
Comment 21 niklas.nebel 2006-05-31 14:31:14 UTC
At least three things are still open: 1. Dialog layout, including agreement from User Experience. 2. Reference input handling. The member pRefBtn in ScAnyRefDlg is meant for internal use. It is assigned only in RefInputStart, but with the patch it's used to get the arguments to the RefInputStart call. If you click into the sheet without using the "Shrink" button, this can't work and even crashes. As mentioned above, see ScPrintAreasDlg (where pRefInputEdit is updated in the focus handler) for a working solution to this. 3. When the source range is changed, your EdInModifyHdl immediately modifies the output table in the sheet (just without repaint), and it remains changed even if the dialog is closed with "Cancel".
Comment 22 niklas.nebel 2006-10-18 18:28:53 UTC
Without a working implementation, we'll have to change this back to ENHANCEMENT from PATCH.
Comment 23 mmeeks 2006-10-19 10:31:17 UTC
ok - 2 and 3 look trivial to fix, but there is no point in doing anything without the UI team's approval. Lutz - without this patch the common case of extending the data (eg. by adding a row) visualised in the data pilot is simply not possible in the UI. Instead it is necessary to destroy and re-create the Data Pilot - which may mean painfully replicating a lot of settings; and of course is ergonomically hopeless. Competing office suites allow the range to be changed fairly painlessly post construction. I attach a screenshot of the updated dialog [ with red gradient overlayed to highlight the new widget ]. Surely there is a spec. already for this, that can be trivially updated ? can you approve this change [ in principle ] [yes/no] ?
Comment 25 utomo99 2006-11-19 09:05:35 UTC
Lho, please answer michael meeks questions. so we can have better OOo 2.X Thanks
Comment 26 lutz.hoeger 2006-12-14 08:34:25 UTC
Sorry, I completely missed this one! And even worse, I won't be able to take care of this issue for the OOo 2.2 release (the one that has UI freeze on Dec 28), as pretty much everyone will be on their holiday break, soon. I guess that's yet another one of these issues for which UX can be blamed for blocking... and it would be right to do so... sigh. I am all in favor of improving the Data Pilot feature, as I experience it's limitations often enough in my day-to-day work. But as Niklas said, and as I could see from Michael's screen shot (which was a brilliant idea! as UX people can't just press F5 and build the whole thing to see the changes), it requires some thoughts about where and how to give the user control over the data source area. I promise I will get on this in January, as soon as I am back. Again, my apologies for not having paid sufficient attention to this one.
Comment 27 oooforum (fr) 2007-04-04 14:03:17 UTC
Well, this issue is fixed or not?
Comment 28 lutz.hoeger 2007-06-11 15:18:17 UTC
Apparently this request is still valid - at least in the vanilla OOo build, you cannot select a different source range. I just discussed this with Niklas. We both agree that the source range selection should be offered in the "More" section of the dialog box - equivalent to the result range selection. Otherwise the UI of the original dialog becomes too cluttered, as the help text ("Drag the fields....") looses it's prominent position. At the moment, there are no other comments from User Experience regarding this dialog design.
Comment 29 claudiadzm 2007-08-29 09:06:15 UTC
I supose, this issue won't be fixed for 2.3? greetings, Claudia
Comment 30 snmartin 2007-11-13 19:54:05 UTC
This feature could means a great enhancement for oo.org, it has been discused since 2003 and still there's no fix available. Would be nice if this can be fixed soon, oo.org is a real good tool for almost everything :-) thanks in advance
Comment 31 niklas.nebel 2007-12-04 18:25:37 UTC
Comment 32 digvijayd 2007-12-21 04:48:54 UTC
Hi Everyone, I need help on the same, I am currently doing some work in Star Basic need the help on changing or editing Source range in Data Pilot, but couldn't find a way...Please help in this regard..
Comment 33 digvijayd 2007-12-21 04:58:53 UTC
Hi Anton Can you tell a way to change the source of a Data Pilot..You have mentioned about few workarounds that are available..Please help Thanks in advance Digvijay
Comment 34 kyoshida 2007-12-21 17:28:45 UTC
*** Issue 84821 has been marked as a duplicate of this issue. ***
Comment 36 kyoshida 2008-02-14 00:42:26 UTC
Created attachment 51505 [details] screenshot showing the revised location of the data source selection box
Comment 37 kyoshida 2008-02-14 00:46:01 UTC
I've revised the previous patch to match the latest milestone, and also to move the position of the data source selection box as lho suggested in 'desc29'. There were quite a few issues with the previous patch, but I've cleaned up all that I could find. So I consider this patch to be in pretty good shape.
Comment 38 niklas.nebel 2008-02-14 14:16:15 UTC
What's the "xDlgDPObject->Output()" call supposed to achieve? It modifies the output cells, even if the dialog is canceled, breaks undo and causes unnecessary "destination range not empty" warnings. The GrabFocus in EdInModifyHdl breaks tab-key travelling (and the tab order is wrong to begin with). The "Results to" list box loses its selection, and leaks the String objects for EntryData. If we fix these problems quickly, it's still uncertain if the change will make it through QA before 3.0 feature freeze. But we can try.
Comment 39 kyoshida 2008-02-14 15:01:28 UTC
@nn: How do we set the tab order of the controls? I've looked at the .src file and the dialog impl. code, but I can't seem to find anything that looks relevant. Any pointer would be great. Thanks.
Comment 40 niklas.nebel 2008-02-14 15:11:07 UTC
Tab order is defined by construction order (thus, the order in the hxx).
Comment 42 kyoshida 2008-02-14 19:46:32 UTC
This patch addresses the tab order issue, and to commit the new source range when OK is pressed, not when the source range is modified, in order to allow canceling. Two issues still remain: 1) When the table size grows as a result of the data source change, the "destination range not empty do you want to overwrite data" message appears when it should not. 2) When the destination position changes, the original table fails to refresh when the dialog closes. Refreshing the original table brings it back to its correct state.
Comment 43 niklas.nebel 2008-02-14 20:07:52 UTC
Calling FillOldParam/FillLabelData with a changed ScDPObject immediately was done to update the dialog's field list if the new range contains different column headers. I was just wondering why the Output() call is there at all.
Comment 44 kyoshida 2008-02-14 20:22:24 UTC
No idea about Output() since the original patch's author is no longer around. Looks like it was redundant since it works just fine without it. So, do you prefer we update the field list immediately when the data source range changes, instead of handling it when the OK button is pressed?
Comment 45 niklas.nebel 2008-02-14 20:48:33 UTC
If the field list isn't updated, the user can make changes to fields that are no longer there. I think it's better to update it immediately, even if this loses the changes made in the dialog so far.
Comment 47 kyoshida 2008-02-14 22:27:14 UTC
This patch updates the data source immediately after the source range gets modified (original functionality) & restores the original source range on cancel. The above two issues I mentioned still remain valid. One thing I noticed, the bAlive flag inside ScDPObject doesn't seem to do much of anything anymore. Will it be okay to remove it?
Comment 48 kyoshida 2008-02-14 22:50:34 UTC
>The above two issues I mentioned still remain valid. Well, actually my latest change fixed them. Hmm... :-/
Comment 49 niklas.nebel 2008-02-15 16:59:20 UTC
Why now FillOldParam(..., true)? This leads to wrong fields again.
Comment 50 kyoshida 2008-02-15 17:19:02 UTC
It's one of those things carried over from the original patch. I take that we are better off not calling that method, correct? That method fills the pivot parameter from the existing output data, so calling it doesn't seem appropriate indeed.
Comment 51 kyoshida 2008-02-15 17:25:18 UTC
Created attachment 51533 [details] new patch without calling FillOldParam(...)
Comment 52 kyoshida 2008-02-15 19:48:02 UTC
Created attachment 51537 [details] even more revised patch (no need to call FillLabelData(...) upon closing)
Comment 53 niklas.nebel 2008-02-18 19:05:40 UTC
In the first "revised patch", the only problem with xDlgDPObject in EdInModifyHdl seemed to be the Output call. xDlgDPObject is a local copy for the dialog, so modifying it isn't really a problem. FillOldParam was called with (..., FALSE) in that version. It's needed so thePivotData contains valid column indexes for the new source range. I added FillOldParam(..., FALSE) to the latest patch again, plus some minor changes, and committed it to CWS "datapilotrange", to finally resolve this issue.
Comment 54 kyoshida 2008-02-18 22:59:31 UTC
ah ok. I didn't realize it was a local copy. Anyway I'm glad to see this issue finally resolved!
Comment 55 niklas.nebel 2008-02-19 08:59:32 UTC
reassigning to QA for verification
Comment 56 niederbayern 2008-05-28 15:06:54 UTC
(adding myself to cc) Hi Oliver, do we make it for 3.0? Please !?! :-D
Comment 57 oc 2008-07-08 17:06:40 UTC
Target 3.1 because of UI freeze
Comment 58 andreschnabel 2009-01-03 13:57:18 UTC
hi - why is this still blocked? The cws was ready and could have been tested months ago. But instead another issue was added to the cws that never got attention and stupitly blocks the cws. Suggestion: remove blocking issue from cws, resync cws and hand over to QA. I can take care about QA, if needed.
Comment 59 oc 2009-01-05 15:09:32 UTC
setting target to 3.2 and reassign issue to kohei for resyncing the cws
Comment 60 kyoshida 2009-04-14 14:33:14 UTC
re-assigning to oc for QA.
Comment 61 oc 2009-05-14 14:56:06 UTC
verified in internal build cws_datapilotrange
Comment 62 thorsten.ziehm 2009-07-20 14:40:38 UTC
This issue is closed automatically and wasn't rechecked in a current version of OOo. This fixed issue should be integrated in OOo since more than half a year. If you think this issue isn't fixed in a current version (OOo 3.1), please reopen it and change the field 'Target Milestone' accordingly. If you want to download a current version of OOo => http://download.openoffice.org/index.html If you want to know more about the handling of fixed/verified issues => http://wiki.services.openoffice.org/wiki/Handle_fixed_verified_issues
Comment 63 thorsten.ziehm 2009-07-20 15:34:57 UTC
Sorry this issue was wrongly closed. This issue will be reopened automatically. And will be set after that back to fixed/verified.
Comment 64 thorsten.ziehm 2009-07-20 15:39:32 UTC
Set to state 'fixed'.
Comment 65 thorsten.ziehm 2009-07-20 15:43:53 UTC
Set back to state 'verified/fixed'. Again. Sorry for the mass of mails.
Comment 66 Stefan Weigel 2009-08-30 17:19:49 UTC
Verified in DEV300m56.
Comment 67 Stefan Weigel 2009-08-30 17:20:43 UTC
Comment 68 Stefan Weigel 2009-08-30 18:30:00 UTC
As a follow-up to this issue, may I drag your attention on issue #104633. Thanks! :-)
Comment 69 aoo-bugger 2010-02-05 13:18:21 UTC
May I ask why the new source field is NOT available for data sources? :-(