Issue 27745 - Basing autofiltering on the result set, not all rows.
Summary: Basing autofiltering on the result set, not all rows.
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: 680m32
Hardware: All All
: P3 Trivial with 33 votes (vote)
Target Milestone: ---
Assignee: frank
QA Contact: issues@sc
: 20945 39988 42315 54203 59898 70506 71839 87478 (view as issue list)
Depends on:
Blocks: 77677
  Show dependency tree
Reported: 2004-04-13 16:35 UTC by belzecue
Modified: 2013-08-07 15:14 UTC (History)
10 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

Proposed part Patch (573 bytes, patch)
2005-02-10 06:37 UTC, sragavan
no flags Details | Diff
Testdocument for Testcasespecification (12.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-09-18 14:16 UTC, frank
no flags Details
Testcase Specification (10.65 KB, text/html)
2007-09-18 14:16 UTC, frank
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description belzecue 2004-04-13 16:35:11 UTC
This is another behavioural difference between OO and Excel that you may or 
may not wish to emulate.  The behaviour relates to autofiltering.

Take, as an example, a sheet with two columns labeled 'First' and 'Second'.  
Both columns have three rows containing numbers 1, 2, 3:

First | Second
1 | 1
2 | 2
3 | 3

- Activate autofilter on the column headers.

- For column 'First' select '1' from the autofilter dropdown list.  Now only 
one row is displayed in the sheet: 1 | 1

- Examine the autofilter dropdown list for column 'Second'.  Notice that all 
three values (1, 2, 3) are displayed, even though, logically, there is only 
one row in the active result set and only one possible choice for the value of 
column 'Second' (the value 1) given the active filtering on column 'First'.  
Selecting anything other than the value '1' in the second column dropdown list 
results in a null result (no matching rows).

In Excel, autofilter dropdown lists only show the possible values based on 
already-active filtering, so that it is not possible to return a null set.  
For example, in the above demonstration, clicking on the 'Second' column 
autofilter dropdown arrow would present a list with only one item: '1'.  In 
other words, Excel bases its autofiltering on the active result set, not the 
original dataset/table.

Andrew Ferguson / Belzecue
Comment 1 frank 2004-04-14 06:51:45 UTC
Hi Bettina,

this may be related to Issue 16112 but not to Issue 364 as stated there. So I
re-assign this Issue to you for further Investigations.

Comment 2 youssefassad 2004-09-13 14:28:46 UTC
The behavior described in <a
href="">Issue 16112</a>
does match this issue somewhat, however I will vote for this one since the two
others have been closed. <a
href="">Issue 364</a>, based
on which <a href="">Issue
16112</a> was closed, also is relevant.

The reasoning for closing <a
href="">Issue 364</a> was,
quote: <i>Our behaviour is technically corret.</i> I would hesitate to disagree
before understanding why this behavior is correct, yet my initial impulse is to
insist (as respectfully as possible) that the way Excel handles this aspect of
Autofiltering is more appropriate.

Andrew did a perfect job describing the behavior and how he felt it needed
modification, and I agree with him entirely.

Believe it or not, this simple issue keeps me from migrating my spreadsheet
usage to oocalc.
Comment 3 youssefassad 2004-09-13 14:30:42 UTC
Sincere apologies for the href's... First time posting to a bugtracking forum.
Comment 4 frank 2005-01-17 11:37:37 UTC
*** Issue 39988 has been marked as a duplicate of this issue. ***
Comment 5 frank 2005-02-09 11:54:48 UTC
*** Issue 42315 has been marked as a duplicate of this issue. ***
Comment 6 sragavan 2005-02-10 06:37:42 UTC
Created attachment 22407 [details]
Proposed part Patch
Comment 7 sragavan 2005-02-10 06:38:51 UTC
I have attached a part of the patch.

The function RowFiltered is 

inline BOOL ScDocument::RowFiltered ( USHORT nRow, USHORT nTab ) const
Comment 8 sragavan 2005-02-10 06:39:54 UTC
I have attached a part of the patch.

The function RowFiltered is in document.hxx (added by me)

inline BOOL ScDocument::RowFiltered ( USHORT nRow, USHORT nTab ) const
  return pTab[nTab]->IsFiltered (nRow);
Comment 9 ooo 2005-08-11 11:00:31 UTC
Stumbled about this because of a mail on the dev@ list,

The patch looks appealing for its simplicity on first glance, but it
leads to only values being selectable that are not already filtered,
even in columns where the original filter criteria was applied. To
filter on other values, first the filter would have to be removed. This
is not what the user expects. Just a small example of what would be
correct instead:

 A | B
 1 | 1
 1 | 2
 2 | 3
 2 | 1
 3 | 2
 3 | 3

Filtering on column A for values of 2 would display

 A | B
 2 | 3
 2 | 1

The filter list on column B now should offer 1 and 3, this is also what
the patch does. But the filter on column A should still offer all values
1,2,3 to be able to quickly select another set, whereas the patch also
narrows these down to only display the value 2.

Furthermore, if in this state one selects 3 for column B, the result is

 A | B
 2 | 3

The filter list of column A should now offer the values 2 and 3, because
both would be in the set of all values 3 of column B. The filter list of
column B should offer 1 and 3, because both are in the set of all values
2 of coumn A.

So there is quite some more work to be done for this enhancement.

Comment 10 frank 2005-09-23 09:50:42 UTC
*** Issue 54203 has been marked as a duplicate of this issue. ***
Comment 11 sophia 2005-10-21 18:51:18 UTC
I observed this bug on 2.0rc3 french version.
Comment 12 sophia 2005-10-28 14:08:37 UTC
I observed this bug on the 2.0 french version.

This feature is widly used in my company !
Comment 13 paulbraun81 2005-11-23 09:59:47 UTC
Don't forget to vote for this issue. 
In my company this feature is frequently used and we cannot work without this
filter. My first question to this problem is long time ago and nothing has
changed. What can I do that this problem been solved in the next release?
Comment 14 hwoarang 2006-01-02 15:37:59 UTC
*** Issue 59898 has been marked as a duplicate of this issue. ***
Comment 15 rvolke 2006-01-03 16:09:05 UTC
My company also requires the filtering to work like Excel's, in that the second 
filter choices are limited to those values still present in the data set.  When 
working with large amounts of data it is imperative to be able to tell what 
values are still available after filtering (rather than seeing all options in 
the original data set when clicking on the combo box).  I hope this feature is 
implemented soon, it certainly has my vote.
Comment 16 sophia 2006-01-18 12:42:34 UTC
Always occur on 2.0.1 version.

This is the first feature my colleagues will test !

Is it complex to solve ? In order to plan an OO migration, do you know when this
feature will be available ?

Comment 17 sophia 2006-03-19 11:36:21 UTC
always occur on 2.0.2 version
Comment 18 paulbraun81 2006-07-10 09:36:28 UTC
always occur on 2.0.2 version
Comment 19 paulbraun81 2006-07-10 09:36:35 UTC
always occur on 2.0.3 version
Comment 20 lanhelp 2006-08-04 14:55:17 UTC
The problem go away in 2.0.2 version from Ubuntu Dapper Drake Final.
Comment 21 sophia 2006-08-04 16:38:22 UTC
This always occurs in the 2.0.3 version for GNU/Linux in french.
Comment 22 sophia 2006-10-12 21:58:15 UTC
always occurs with 2.0.4rc3_0 for GNU/Linux !!!!!!!!
Comment 23 sophia 2006-10-13 08:21:48 UTC
Can someone change the issue type ?
I mean it is not an enhancement
but a defect issue
since it does not provide the expected
behavior to the user.
When using one autofilter,
we don't want to see values
which has already been
discarded by an other filter.
Comment 24 ooo 2006-10-17 14:48:41 UTC
*** Issue 20945 has been marked as a duplicate of this issue. ***
Comment 25 frank 2006-10-17 15:21:48 UTC
*** Issue 70506 has been marked as a duplicate of this issue. ***
Comment 26 frank 2006-11-22 09:31:47 UTC
*** Issue 71839 has been marked as a duplicate of this issue. ***
Comment 27 kk31 2007-02-20 10:33:36 UTC
always occurs with 2.1.0 and 2.2.0rc1
Comment 28 iavor 2007-05-30 09:22:29 UTC
I disagree with Eike - the filter on column B must reduce the options in the
filter on column A. For me if it does not - the issue is half solved and even
more confusing than now.
Comment 29 frank.loehmann 2007-07-20 12:43:39 UTC
Set target.
Comment 30 sophia 2007-07-21 17:04:28 UTC
why not 2.3 ?
Comment 31 thomas.benisch 2007-08-01 10:39:34 UTC
reassigned to TBE
Comment 32 thomas.benisch 2007-08-01 10:46:23 UTC
Comment 33 frank.loehmann 2007-08-02 17:11:25 UTC
Please find the new spec here:
Comment 34 thomas.benisch 2007-08-13 11:16:52 UTC
fixed on CWS calcautofilter

The following files are affected:

Comment 35 thomas.benisch 2007-08-14 13:59:00 UTC
TBE->FST: Please verify on CWS calcautofilter.
Comment 36 frank 2007-09-18 14:16:10 UTC
Created attachment 48310 [details]
Testdocument for Testcasespecification
Comment 37 frank 2007-09-18 14:16:47 UTC
Created attachment 48311 [details]
Testcase Specification
Comment 38 kpalagin 2007-09-18 14:57:53 UTC
Thank you very much!!
Comment 39 frank 2007-09-19 12:17:50 UTC
Found fixed on cws calcautofilter using Linux, Solaris and Windows build
Comment 40 frank 2007-12-14 10:40:57 UTC
found integrated on master m239 using Linux, Solaris and Windows build
Comment 41 frank 2008-03-26 20:42:21 UTC
*** Issue 87478 has been marked as a duplicate of this issue. ***