I have a Calc spreadsheet connected to a registered Base database which is in turn a connection to an existing PostgreSQL database. If I view the data in Base the date is formatted correctly. If I view in calc using F4 the dates are alos formatted correctly. But when I create a PIVOTTABLE from the registered database then the dates are formatted as numbers. Obviously I can format the column(s) but this is lost on refresh. Running ubuntu 14.04 and LO Version: 4.2.3.3 Build ID: 420m0(Build:3)
I went through the steps, and have reproduced the results. When doing the pivot table from a registered database, the dates are in numeric format - not in date format. I am on 4.2.5.2 on Fedora 20
I have the same problem since LO 4.0.0. It appends when data come from outter file - database or Spreadsheet. I tried with dbf files and MS Excel 5.0. It's OK when data are in a sheet of the same file. There's the same "bug" in Apache OpenOffice 4.0.1 but it's fixed on AOO 4.1.0.
Adding self to CC if not already on
Hello I filed this ages ago... its a regression so I'm not sure why it hasn't been assigned yet.
(In reply to mshiner from comment #4) > Hello > > I filed this ages ago... its a regression so I'm not sure why it hasn't been > assigned yet. A regression over which previous version. As I don't currently have a postgres db setup to connect to, I can not check whether this still occurs in the latest production release of LO. I would encourage you to retest this with LO 4412 and report back your findings here, as the bug might have been fixed in the meantime. Setting to NEEDINFO, please set to UNCONFIRMED once you have re-tested and reported back.
Please also indicate how you connect your ODB to your postgres instance - jdbc, native driver, ODBC, etc.
Additionally, is the problem only with dates from postgres data sources or any database source ? For example, do you see the same problems with a simple hsqldb embedded database ? Note that there are currently quite a few date formatting issues with PivotTables in general, and you may just have come across one of them. This would corroborate with the dates looking OK in Base, but screwed up in Calc.
Its a problem with all data sources I have tested - even spreadsheets. You can test by setting up a calc sheet then routing through a base database into the pivot table in a second sheet. The bug is still present in Version: 4.2.7.2 Build ID: 420m0(Build:2)
(In reply to Alex Thurgood from comment #5) > (In reply to mshiner from comment #4) > > Hello > > > > I filed this ages ago... its a regression so I'm not sure why it hasn't been > > assigned yet. > > A regression over which previous version. As I don't currently have a > postgres db setup to connect to, I can not check whether this still occurs > in the latest production release of LO. > > I would encourage you to retest this with LO 4412 and report back your > findings here, as the bug might have been fixed in the meantime. > > Setting to NEEDINFO, please set to UNCONFIRMED once you have re-tested and > reported back. Sorry...can't remember which version I was running before 4.2.3.3 Build ID: 420m0(Build:3)
Have just updated to Version: 4.4.2.2 Build ID: 40m0(Build:2) and it is still there.
*** Bug 92116 has been marked as a duplicate of this bug. ***
Bug is there with: Win7x64Ultimate Version: 5.0.0.0.beta3 (x64)Build ID: 96345c15d8ab19c49014f055fe41ba8e1f421e5c As commented in https://bugs.documentfoundation.org/show_bug.cgi?id=92116 is reproducible with a spreadsheet as registered source.
confirmed in comment 12, setting as NEW
Wow..... this is STILL not fixed after nearly two years! Version: 5.0.2.2 Build ID: 00m0(Build:2) Locale: en-GB (en_GB.UTF-8) Bug still exists and is reproducible. Any timescale (hopefully this side of hell freezing over) as to when this might be fixed.
On pc Debian x86-64 with master sources updated today, I could reproduce this. I create a simple ods sheet, then an odf file from it and finally a second ods with a table pilot from this odf file.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Chaps This bug is still present FOUR YEARS AFTER THE ORIGINAL REPORT!!!! I'm guessing you don't think it important enough to fix even though its a regression from a previous build. Tested on ubuntu 18.04 Version: 6.0.3.2 Build ID: 1:6.0.3-0ubuntu1 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: en-GB (en_GB.UTF-8); Calc: group I used both a connection a postgreSQL database AND a spreadsheet
On pc Debian x86-64 with master sources updated today, I can still reproduce from comment 15 (A first ods file with a odb wrapper file and a final ods file using the odb file). Several remarks: 1) In first file, even when typing date with 4 years, Calc uses 2 numbers by default for date. I must format the column explicitely. (perhaps not related directly to this bugtracker but concerns format problem) Note: I didn't use column headers in this file. 2) In odb file, Base uses the format with 4 numbers for years in column name of the table, but uses the by default the format with 2 numbers for years for the values. 3) In third file (so the second ods file), the name of column uses the format with 4 numbers for years and like the bug tracker describes, I got just numbers for values. But I noticed that when clicking on arrow of the column in pivot table (eg to filter it), values appeared as dates with a format with 2 numbers for years. Eike: thought you might be interested in this one since it concerns Pivot part. Any thoughts?
Dear mshiner, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Chaps Have tested using both and ods and postgreSQL as a source. Bug is still present. Concur with comment 18 that the filter section of the report shows the values as dates Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.8 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: en-GB (en_GB.UTF-8); Calc: group Regs Martyn
Here on windows 7, libreOffice Version : 6.3.5.2 (x64). The bug is still present : I have a pivot table in calc based on a request and data from an HSQLBD file. Dates are correctly formatted in my database. I can format them to appear correctly in the database but as soon as I refresh they appear as numbers again. Because of this format bug, the pivot table is unusable. I hope this gets solved one day...
(In reply to philnsicab from comment #21) > Here on windows 7, libreOffice Version : 6.3.5.2 (x64). > ... When retesting, it's more relevant to test with not an EOL version. I mean last stable one is 6.4.6 and brand new one is 7.0.1. (Don't misunderstand me, I don't pretend it's already fixed in these last versions, I haven't given a try)
Bug is still present Ubuntu 20.04 Version: 6.4.6.2 Build ID: 1:6.4.6-0ubuntu0.20.04.1 CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3; Locale: en-GB (en_GB.UTF-8); UI-Language: en-US Calc: threaded I narrowed it down to the way styles are applied to the PT. Basically on a new PT (with data in a tab in the file or an external database) you get 6 styles Pivot Table Category Pivot Table Corner Pivot Table Field Pivot Table Result Pivot Table Title Pivot Table Value If you change background colour/font etc on, say, the Category or Value it is applied and survives a refresh. Number format, however is not. Even if you modify the number format in the style it is not applied at all - I assume the default of 'general' is applied. If you have your data in a calc tab on the same sheet you can apply the number format there and the PT respects it. Conclusion, the PT style for number format is always set to 'general' unless the number is 'formatted' in the data - which only works if the data is in the same sheet on another. I also found this link https://mikekaganski.wordpress.com/2019/04/29/xlsx-interoperability-pivot-tables-related-improvements/ and specifically this and this fix https://git.libreoffice.org/core/+/26e85974a0287ab5869e7ff0145a66b853d66a02 related to this bug https://bugs.documentfoundation.org/show_bug.cgi?id=124772 report. Mike notes "Excel relies on per-field format setting in pivot tables, while Calc takes fields formatting from source." which I think is where the problem resides. Regards Martyn
(In reply to mshiner from comment #23) > > Mike notes "Excel relies on per-field format setting in pivot tables, while > Calc > takes fields formatting from source." which I think is where the problem > resides. > A database knows dates as a distinct data type. There should be no disambiguation about the actual data type. In Base you can also declare the prefered display format of a date field in table design (but not in query design). However, doing so makes no difference. The dates come in as serial day nummbers (LO 7.2.2)
btw: AOO does this right.
On pc Debian x86-64 with master sources updated today, I could still reproduce this. I noticed this on console: +++++ column root -- result member 'Total' column totals * not calculated [val=0; aux=0; count=0] -- dimension 'testdatetime' -- result member '10/02/22 01:00' column totals * not calculated [val=0; aux=0; count=0] data root -- data member '' * not calculated [val=10; aux=0; count=1] * not calculated [val=0; aux=0; count=0] -- result member '11/02/22 02:00' column totals * not calculated [val=0; aux=0; count=0] data root -- data member '' * not calculated [val=20; aux=0; count=1] * not calculated [val=0; aux=0; count=0] -- result member '12/02/22 03:00' column totals * not calculated [val=0; aux=0; count=0] data root -- data member '' * not calculated [val=30; aux=0; count=1] * not calculated [val=0; aux=0; count=0] +++++ row root -- result member 'Total' column totals * not calculated [val=0; aux=0; count=0] data root -- data member '' * 60 [val=60; aux=0; count=-3] * not calculated [val=0; aux=0; count=0] -- data dimension 'testdatetime' -- data member '10/02/22 01:00' * 10 [val=10; aux=0; count=-3] * not calculated [val=0; aux=0; count=0] -- data member '11/02/22 02:00' * 20 [val=20; aux=0; count=-3] * not calculated [val=0; aux=0; count=0] -- data member '12/02/22 03:00' * 30 [val=30; aux=0; count=-3] * not calculated [val=0; aux=0; count=0] I suppose it's due to the fact I build locally with enable-dbgutil. Above all, we can see LO Calc sees date values here. The method which logs this is ScDPDataMember::Dump in sc/source/core/data/dptabres.cxx, see here: https://opengrok.libreoffice.org/xref/core/sc/source/core/data/dptabres.cxx?r=06ca3d89#2668
Eike: I tested this patch diff --git a/sc/source/core/data/dptabres.cxx b/sc/source/core/data/dptabres.cxx index e49fbd365b44..adfb881eae12 100644 --- a/sc/source/core/data/dptabres.cxx +++ b/sc/source/core/data/dptabres.cxx @@ -1369,7 +1369,7 @@ void ScDPResultMember::FillMemberResults( } const ScDPDimension* pParentDim = GetParentDim(); - if ( bIsNumeric && pParentDim && pResultData->IsNumOrDateGroup( pParentDim->GetDimension() ) ) + if ( bIsNumeric && pParentDim && !pResultData->IsNumOrDateGroup( pParentDim->GetDimension() ) ) { // Numeric group dimensions use numeric entries for proper sorting, // but the group titles must be output as text. and dates appear. But then I did "make sc.check" and it failed on several tests. At least, it may be a start for some research.
Dear mshiner, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug