Bug 63998 - PIVOTTABLE: Does not filter on page field
Summary: PIVOTTABLE: Does not filter on page field
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.2.2 release
Hardware: All All
: high major
Assignee: Kohei Yoshida
QA Contact: Jorendc
URL:
Whiteboard: target:4.2.0 target:4.1.0 target:4.0.5
Keywords:
Depends on:
Blocks:
 
Reported: 2013-04-27 14:03 UTC by Ferry Toth
Modified: 2013-07-16 13:55 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample spreadsheet showing the problem with pivot tables (27.66 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-04-27 14:04 UTC, Ferry Toth
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ferry Toth 2013-04-27 14:03:47 UTC
When you create a pivot table you can drop a field in the 'page field' area. This adds a drop down list box to the pivot table on which you can filter the data use for the pivot table (in my case: show only the data for a project).

We use this to create time sheets that go into our invoices, it is therefore of great importance to us :-)

This has worked for a long time very nicely at least until libo 3.6.2.2 (Ubuntu Quantal). Now with Raring libo 4.0.2.2 the functionality is broken.

I will attach a spreadsheet with the data embedded, although normally the pivot table is run directly against our pgsql database.

Tab 1 shows the data,
Tab 2 show a normal pivot table
Tab 3 shows the same pivot table with data grouped per year, month, day

In libo 3.6 you can refresh the table, or select other projects as is supposed to, in libo 4 after refresh all the times from all projects are shown, no matter which project is selected.

I hope this can be fixed in a minor update that can go into ubuntu raring, otherwise we can not upgrade to raring and must block the windows users from upgrading as well to 4.xx.

Ferry
Comment 1 Ferry Toth 2013-04-27 14:04:41 UTC
Created attachment 78555 [details]
Sample spreadsheet showing the problem with pivot tables
Comment 2 Joel Madero 2013-04-30 22:17:11 UTC
Can you describe step by step what to do with this spreadsheet? I just tried what I thought was the bug but it worked:

1. Open spreadsheet
2. Select Draaitabel_Blad1_1_2
3. On pivot table right click and do "filter"
4. Select employee name = some name
5. See what occurs (filters right)
6. Refresh pivot table


Nothing changes (still filtered)

Am I missing something?
Comment 3 Joel Madero 2013-04-30 22:17:32 UTC
Marking as NEEDINFO - once you provide detailed steps (and correct mine) mark as UNCONFIRMED and we will investigate
Comment 4 Ferry Toth 2013-05-01 09:24:44 UTC
Hello Joel,

The filter has already been set to the 'Project Name'. You don't need to change that.

Steps to reproduce:
- download the spreadsheet (don't open embedded in your browser)
- goto the sheet Draaitabel_blad1_1
- you will see the project hours are already filtered on APL-01-1 by the Page field (not the filter button)
- the total Resultaat (sum of billable and non-billable hours) is 10:45:00
- right click in table and select Renew or Refresh or similar depending on your interface language
- in LibO 3.6.2.2 nothing will change, you will have 13 rows of data
- in LibO 4.0.2.2 you will 61 rows of data, the total will change to 2:59:00. 

- The sum of the time is not shown correctly because the format of the style needs to be changed to [UU]:mm. However the name of the Style depends on your language, a minor irritation for now. Just note that the total in 4.0.2.2 equals to the sum of the column Timespent on the sheet 'Blad1'. This means all rows are shown in the Pivot Table, not just the ones for APL-01-1 as intended and functioning correctly in 3.6.2.2.

Next:
- go to sheet Draaitabel_Blad1_1_2
- in the drop down box next to Project Name select APL-01-1 (in LibO 4.0.2.2 you need to first unselect All, then select APL-01-1)
- right click in the Pivot Table and Renew or Refresh
- in 3.6.2.2 you get 13 rows, split our per year, month and day
- in 4.0.2.2 you get 100 rows

Further in 4.0.2.2 if you continue after the last step:
- click on Filter and fill in Project Name = APL-01-1, now the correct rows are shown

It seems that there are 2 filters:
- one accessed by clicking on Filter. This one I never use, but it seems to be working.
- the second is the Page Field in the Pivot Table design, in this example 'Project Name'. In 4.0.2.2 this filter is not working.

Ferry
Comment 5 Jorendc 2013-05-02 19:20:53 UTC
Thanks for reporting!

I can reproduce this behavior using Linux Mint 14 x64 with LibreOffice 4.0.3.2.

I tried to bibisect this one, but this bug is not reproducible with the latest version in our bibisect40 package. (Version 4.1.0.0.alpha0+ (Build ID: 8450a99c744e9005f19173e4df35d65640bcf5c))

I can reproduce this using my own build master (Version: 4.1.0.0.alpha0+
Build ID: 201f5fb6367a67cd39fa8e4396f5f86589db6be) (last commit included: commit 201f5fb6367a67cd39fa8e4396f5f86589db6be0 Date:   Thu May 2 16:23:10 2013 +0100)

So ... this is a regression from 4.0.x.x vs 4.0.2 (or even older).

Looks very bad.

Therefore I mark this bug as 'Major High'.

Kind regards,
Joren
Comment 6 Jorendc 2013-05-02 19:24:09 UTC
@Moggi, Kohei, Eike: looks like a bad regression. As far I can see such use case of the pivot table seems quite borked.

Kind regards,
Joren
Comment 7 Jorendc 2013-05-02 19:50:39 UTC
I used our new bibisect repo:

1052ac38cca720aae33fe25886c7bb840479c32f is the first bad commit
commit 1052ac38cca720aae33fe25886c7bb840479c32f
Author: Jean-Baptiste Lallement <jean-baptiste.lallement@canonical.com>
Date:   Tue Feb 5 17:05:15 2013 +0000

    source-hash-32e7b29f51c4f6bce08a23e454ec47b83849c9df
    
    commit 32e7b29f51c4f6bce08a23e454ec47b83849c9df
    Author:     Caolán McNamara <caolanm@redhat.com>
    AuthorDate: Fri Dec 14 11:12:22 2012 +0000
    Commit:     Caolán McNamara <caolanm@redhat.com>
    CommitDate: Fri Dec 14 11:37:23 2012 +0000
    
        drop converted TP_OPTPRINT_PAGE page
    
        Change-Id: Ieac443b40fd5fa3b1b13b492e1b97a5df35835b5

:100644 100644 b9d46e1a0972eaf1cc564e568bef0260e4e2b350 53deb854a6a161e5226277db0e84a2284a448c46 M	ccache.log
:100644 100644 9259a0cf3daf61332f21c9c0cd1b6a87819a97f7 c36aaf96dcf7ddeb920cacaebc1f1deea5bcc49a M	commitmsg
:100644 100644 03744e7909a710b85fcd138512c8bde2c49dc5fd 791e52fe2d054126e6cfef6b7db69d171b7e30a8 M	dev-install.log
:100644 100644 2e55653beb4afc158fe0023e5c7452102d5cae7b c97e58c5e3afdaa3fd790cd9d9ea8fa9b90d7d93 M	make.log
:040000 040000 1b2afbfcc4f1e6534557b56473d4ce5ddb76c9a6 0cc36d61b54fc8aa4de9c25741694538cd41dde5 M	opt

git bisect start 'latest' 'oldest'
# bad: [82f40a7baab8fa06ffaa3f9182542521d36da341] source-hash-323c5ee69d5814f8400c0af3038023df9336b59c
git bisect bad 82f40a7baab8fa06ffaa3f9182542521d36da341
# bad: [d92cb222b5c64d906fec4d633bb304d1eb6916d5] source-hash-c2e3d09bb740749e1a6e045ff08bf32e5812fa9e
git bisect bad d92cb222b5c64d906fec4d633bb304d1eb6916d5
# good: [4a235115e3732662112e07d7fc132e8135da2e5c] source-hash-56ec8f4ef1935af73b874ab11928bac4c867b40a
git bisect good 4a235115e3732662112e07d7fc132e8135da2e5c
# good: [336f37b1f5c47ba88efc1a072882d3a7f2acebe4] source-hash-1b6598c9e71cdabbdf73a15c5033c0f33d071be5
git bisect good 336f37b1f5c47ba88efc1a072882d3a7f2acebe4
# good: [ca7b2d20dca225305b0811f55ff17e7965ccf43e] source-hash-b552f521f6bbf917ae41ccfcddeb498c75c824e3
git bisect good ca7b2d20dca225305b0811f55ff17e7965ccf43e
# bad: [1052ac38cca720aae33fe25886c7bb840479c32f] source-hash-32e7b29f51c4f6bce08a23e454ec47b83849c9df
git bisect bad 1052ac38cca720aae33fe25886c7bb840479c32f
# good: [67ec75c69c10fef889576ea5fbe7646d5eb80c29] source-hash-2551fec6d2a73d10cfa2aec85ae7d00e237e0294
git bisect good 67ec75c69c10fef889576ea5fbe7646d5eb80c29

Range: http://cgit.freedesktop.org/libreoffice/core/log/?qt=range&q=2551fec6d2a73d10cfa2aec85ae7d00e237e0294..32e7b29f51c4f6bce08a23e454ec47b83849c9df
Comment 8 Jorendc 2013-05-02 19:54:39 UTC
@Kohei: as far I (a non-dev) can see this potentially result in this bug http://cgit.freedesktop.org/libreoffice/core/commit/?id=1360b28b477f980a2c58659ffc298d0dd2cda20b
Am I right/can you verify that?

Thanks for your time!
Comment 9 Kohei Yoshida 2013-05-02 20:12:55 UTC
Not a regression (though it may look like one) as the bug existed for years, dating back to OOo.  It was never exposed because the old popup used different mechanism to do the filtering by page fields.  The new popup uses field members' hidden flags to achieve filtering by multiple members, but unfortunately that code has this bug.
Comment 10 Ferry Toth 2013-05-02 20:39:56 UTC
Regression or not: for me as a user a well working piece of functionality is now broken.

I really appreciate you all looking into this.

Ferry
Comment 11 Ferry Toth 2013-05-28 17:05:25 UTC
Verified the Page Field filter doesn't work for PIVOTTABLE on IA32/WinXP as well, have set platform to all.

This bug caused the pivot table to be useless in many cases. Glad to see importance has been set to High/Major.

Will nobody take this bug? If I could I would, but I can't...

Ferry
Comment 12 Kohei Yoshida 2013-07-13 18:25:31 UTC
I'm on my spare time. I'm looking into it right now.
Comment 13 Kohei Yoshida 2013-07-13 18:59:40 UTC
I already found the problem code. Yeah, it's been there pretty much since the beginning of LibreOffice time.  I'll work on fixing that.
Comment 14 Kohei Yoshida 2013-07-13 21:01:06 UTC
Sent backport request for 4.1 to gerrit: https://gerrit.libreoffice.org/#/c/4894/
Comment 15 Commit Notification 2013-07-13 21:03:09 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=76488b00ed75bf7915d6d57af9875c6434e740d6

fdo#63998: Filtering by page fields to work again.



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 16 Kohei Yoshida 2013-07-13 23:03:36 UTC
Backport request for 4.0: https://gerrit.libreoffice.org/#/c/4897/
Comment 17 Joel Madero 2013-07-14 03:07:17 UTC
Thanks Kohei - although working on your spare time - I hope you mean "on the clock" spare time ;)
Comment 18 Commit Notification 2013-07-14 15:55:34 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7ee62f1d499c02c329f228a1d94eb6d64b80589b

fdo#63998: Oops I introduced a new bug. Let's not.



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 19 Commit Notification 2013-07-15 14:49:37 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=eb426753811befc69b3929146ac68cb97f92f7b0&h=libreoffice-4-1

fdo#63998: Filtering by page fields to work again.


It will be available in LibreOffice 4.1.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 20 Commit Notification 2013-07-15 14:58:35 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6877ddc50d601f57d4cf77379ae613a33df19711&h=libreoffice-4-0

fdo#63998: Filtering by page fields to work again.


It will be available in LibreOffice 4.0.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 21 Kohei Yoshida 2013-07-15 16:29:15 UTC
I'll mark this fixed.
Comment 22 Ferry Toth 2013-07-15 18:15:58 UTC
Thank you Kohei!

I'll test this as soon as it appears in LO PPA (currently 4.0.4rc2) or otherwise the 4.0.5 portable version when it appears..

Ferry
Comment 23 Eike Rathke 2013-07-16 09:28:41 UTC
Pending review for 4-1-0 as https://gerrit.libreoffice.org/4925
Comment 24 Commit Notification 2013-07-16 13:55:05 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6af62db1f697abffa8c38c2d5d41bccab39b5b0a&h=libreoffice-4-1-0

fdo#63998: Filtering by page fields to work again.


It will be available already in LibreOffice 4.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.