Apache OpenOffice (AOO) Bugzilla – Issue 23658
Need ability to change datapilot source range
Last modified: 2013-08-07 15:14:30 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
Created attachment 12005 [details] Example of datapilot problem where adding row to source data
Hi Bettina, 1 4 u. Frank
Created attachment 19467 [details] Proposed Patch
patch created is against OOO113
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.
Created attachment 22030 [details] Updated patch with respect to m65.
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.
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").
Created attachment 22367 [details] Fix for remote database
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.
added nn to cc list for observation of this task
*** Issue 48586 has been marked as a duplicate of this issue. ***
Is this supposed to be included in Beta 2? If yes, I can't find it. If no, is there a workaround?
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 ;-)
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?
Oops, I intended to reassign to Níklas. Sorry, "n".
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.
Jody this is yours to track.
Changing target (last integration for 2.0.3 is over).
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 ?
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".
Without a working implementation, we'll have to change this back to ENHANCEMENT from PATCH.
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] ?
Created attachment 39875 [details] picture
Lho, please answer michael meeks questions. so we can have better OOo 2.X Thanks
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.
Well, this issue is fixed or not?
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.
I supose, this issue won't be fixed for 2.3? greetings, Claudia
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
Target 3.0
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..
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
*** Issue 84821 has been marked as a duplicate of this issue. ***
Created attachment 51504 [details] revised patch
Created attachment 51505 [details] screenshot showing the revised location of the data source selection box
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.
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.
@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.
Tab order is defined by construction order (thus, the order in the hxx).
Created attachment 51516 [details] revised patch
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.
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.
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?
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.
Created attachment 51520 [details] revised patch
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?
>The above two issues I mentioned still remain valid. Well, actually my latest change fixed them. Hmm... :-/
Why now FillOldParam(..., true)? This leads to wrong fields again.
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.
Created attachment 51533 [details] new patch without calling FillOldParam(...)
Created attachment 51537 [details] even more revised patch (no need to call FillLabelData(...) upon closing)
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.
ah ok. I didn't realize it was a local copy. Anyway I'm glad to see this issue finally resolved!
reassigning to QA for verification
(adding myself to cc) Hi Oliver, do we make it for 3.0? Please !?! :-D
Target 3.1 because of UI freeze
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.
setting target to 3.2 and reassign issue to kohei for resyncing the cws
re-assigning to oc for QA.
verified in internal build cws_datapilotrange
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
Sorry this issue was wrongly closed. This issue will be reopened automatically. And will be set after that back to fixed/verified.
Set to state 'fixed'.
Set back to state 'verified/fixed'. Again. Sorry for the mass of mails.
Verified in DEV300m56.
Closing.
As a follow-up to this issue, may I drag your attention on issue #104633. Thanks! :-)
May I ask why the new source field is NOT available for data sources? :-(