Issue 23658 - Need ability to change datapilot source range
Summary: Need ability to change datapilot source range
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1
Hardware: All All
: P4 Trivial with 8 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: oooqa, usability
: 84821 (view as issue list)
Depends on: 86791
Blocks: 15522 47145
  Show dependency tree
Reported: 2003-12-18 07:49 UTC by antonbijl
Modified: 2013-08-07 15:14 UTC (History)
21 users (show)

See Also:
Issue Type: PATCH
Latest Confirmation in: ---
Developer Difficulty: ---

Example of datapilot problem where adding row to source data (5.99 KB, application/octet-stream)
2003-12-18 07:55 UTC, antonbijl
no flags Details
Proposed Patch (10.23 KB, patch)
2004-11-18 11:20 UTC, sragavan
no flags Details | Diff
Updated patch with respect to m65. (10.61 KB, patch)
2005-01-31 11:06 UTC, sragavan
no flags Details | Diff
Fix for remote database (1.30 KB, patch)
2005-02-09 11:26 UTC, sragavan
no flags Details | Diff
picture (46.54 KB, image/png)
2006-10-19 10:31 UTC, mmeeks
no flags Details
revised patch (16.06 KB, patch)
2008-02-14 00:40 UTC, kyoshida
no flags Details | Diff
screenshot showing the revised location of the data source selection box (48.40 KB, image/png)
2008-02-14 00:42 UTC, kyoshida
no flags Details
revised patch (15.17 KB, patch)
2008-02-14 19:41 UTC, kyoshida
no flags Details | Diff
revised patch (18.18 KB, patch)
2008-02-14 22:23 UTC, kyoshida
no flags Details | Diff
new patch without calling FillOldParam(...) (18.08 KB, patch)
2008-02-15 17:25 UTC, kyoshida
no flags Details | Diff
even more revised patch (no need to call FillLabelData(...) upon closing) (18.03 KB, patch)
2008-02-15 19:48 UTC, kyoshida
no flags Details | Diff

Note You need to log in before you can comment on or make changes to this issue.
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.


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.

Comment 3 sragavan 2004-11-18 11:20:31 UTC
Created attachment 19467 [details]
Proposed Patch
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" 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
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 24 mmeeks 2006-10-19 10:31:56 UTC
Created attachment 39875 [details]
Comment 25 utomo99 2006-11-19 09:05:35 UTC
Lho, please answer michael meeks questions. 
so we can have better OOo 2.X

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

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, it has been discused
since 2003 and still there's no fix available. Would be nice if this can be
fixed soon, is a real good tool for almost everything :-)

thanks in advance
Comment 31 niklas.nebel 2007-12-04 18:25:37 UTC
Target 3.0
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
Comment 34 kyoshida 2007-12-21 17:28:45 UTC
*** Issue 84821 has been marked as a duplicate of this issue. ***
Comment 35 kyoshida 2008-02-14 00:40:58 UTC
Created attachment 51504 [details]
revised patch
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 41 kyoshida 2008-02-14 19:41:45 UTC
Created attachment 51516 [details]
revised patch
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

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 46 kyoshida 2008-02-14 22:23:26 UTC
Created attachment 51520 [details]
revised patch
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 =>
If you want to know more about the handling of 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? :-(