Bug 100380 - REPORT BUILDER : Chart in report contains no data unless y values are field 0
Summary: REPORT BUILDER : Chart in report contains no data unless y values are field 0
Status: RESOLVED DUPLICATE of bug 68663
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.1.3.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-06-14 20:10 UTC by branestawm
Modified: 2016-06-17 22:04 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
2 tables each 5 records, 3 fields in embedded HSQLDB with chart reports (43.37 KB, application/vnd.oasis.opendocument.database)
2016-06-14 20:10 UTC, branestawm
Details
Date formatted in query as string - linechart shows dates (23.92 KB, application/vnd.oasis.opendocument.database)
2016-06-16 19:31 UTC, Robert Großkopf
Details
Extended version of attachment to comment 9 (51.86 KB, application/octet-stream)
2016-06-17 17:29 UTC, branestawm
Details
Comparison test of line and xy charts in Calc (18.15 KB, application/octet-stream)
2016-06-17 17:30 UTC, branestawm
Details
Date formatted as integer - linechart and xy-chart are shown right (33.59 KB, application/vnd.oasis.opendocument.database)
2016-06-17 18:40 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description branestawm 2016-06-14 20:10:07 UTC
Created attachment 125652 [details]
2 tables each 5 records, 3 fields in embedded HSQLDB with chart reports

When configuring a line chart in Report Builder, if the chart data range option 'First column as label' is cleared and the report executed, the chart contains no data unless the y value field is field 0.

It is not possible to clear this option permanently: after the report is saved in Report Builder, then closed and reopened, 'First column as label' is reset true and chart data series are also reset. 

Example charttest.odb file attached (2 tables each 5 records, 3 fields in embedded HSQLDB). Report 'Line chart' uses table 'Events' as data source, which has y values in field 1 - data visible in Report Builder but no data in chart when report executed. Report 'Line chart2' uses table 'Events2', which has y values in field 0 - data visible in Report Builder (with correct x values) and in chart when report executed (but x values are formatted as dates!).

Reports 'XY chart'/'XY chart2' show similar behaviour but are there to show another anomaly which I will report separately (x values field setting is not remembered).

LibreOffice 5.1.3.2 on OpenSuse 13.2 x64.


[I'm a noobie to Base and to Bugzilla. I think I'm using Base charts correctly - no response on AskLO - and I hope this report is clear].
Comment 1 Alex Thurgood 2016-06-15 09:30:37 UTC
Version: 5.1.3.2
Build ID: 644e4637d1d8544fd9f56425bd6cec110e49301b
CPU Threads: 2; OS Version: Mac OS X 10.11.5; UI Render: default; 
Locale: fr-FR (fr.UTF-8)

Opened report called LineChart - no data is displayed in chart.

In Report Edit mode, I see a preview of the line chart data which bears no relationship to the values of the table actually referenced. This is probably because the Dates are displayed as integer values.

Double-clicking on the chart, and then right mouse button click on the chart, choosing Data Ranges from the menu, shows DataSeries as BeginDate, the Range for Name as "label 0" and Y value as 0. First Column as Label is also set to True.


If I unset the First Column as Label and set Y = 0 in the Data Range menu, save the report definition and execute the report, I get a chart with two line graphs, dates across the bottom on the X axis and integer values on the Y axis..

If I close the generated report, close the report definition, then relaunch the report generation, I see a chart with a single line graph, dates across the X axis and integer values on the Y axis. However, double-clicking on the chart and examining the Data Ranges dialog shows that the Label = 0 / Y = 0 settings have indeed returned (so first bug), and second the DataSeries now includes two entries for BeginDate, one entry for CostPerPerson and another entry for Status.
Comment 2 Alex Thurgood 2016-06-15 09:32:09 UTC
I don't use charts in Reports so putting Robert and Lionel on CC as they probably have a better idea of what's going on, or is supposed to happen.
Comment 3 Alex Thurgood 2016-06-15 09:36:16 UTC
I can confirm that on
Version: 5.1.3.2
Build ID: 644e4637d1d8544fd9f56425bd6cec110e49301b
CPU Threads: 2; OS Version: Mac OS X 10.11.5; UI Render: default; 
Locale: fr-FR (fr.UTF-8)

the First Column as Label, and Y=0 settings are not respected when removed/unset and the report definition saved then re-opened for editing. This is IMHO a bug.
Comment 4 Robert Großkopf 2016-06-15 17:38:51 UTC
Isn't this bug a duplicate of this one: https://bugs.documentfoundation.org/show_bug.cgi?id=91289

You are using dates. This seems to be a special problem for charts. Have a look at the x-Axis: Values have nothing to do with the values you have set in the table.
Comment 5 branestawm 2016-06-15 23:09:16 UTC
(In reply to robert from comment #4)
> Isn't this bug a duplicate of this one:
> https://bugs.documentfoundation.org/show_bug.cgi?id=91289
> 
> You are using dates. This seems to be a special problem for charts. Have a
> look at the x-Axis: Values have nothing to do with the values you have set
> in the table.

The bug as I reported it is absence of data points in the body of the chart, unless y values are field 0.

My 'Line chart' reports use integers (y values), not dates. They do show dates in the x-axis but these should be ordinal integers as they are in a Calc line chart - this may be another bug.

My bug description differs from #91289 which reports "with no X-axis value at all", whereas my line charts show an x-axis (albeit with bad values and in date format) - and no data points in the body of the chart.

Perhaps you were looking at the 'XY chart' reports which use dates on the x-axis but are for my second bug report #100381 concerning the x-axis?

Useful to know that dates are a problem with charts in Base - thanks for your interest!
Comment 6 Alex Thurgood 2016-06-16 09:26:42 UTC
Is this not a DUP of bug 68663 ?
Comment 7 Alex Thurgood 2016-06-16 09:32:47 UTC
(In reply to Alex Thurgood from comment #6)
> Is this not a DUP of bug 68663 ?

The absence of data points is bug 68663 which Lionel originally provided a fix for, but which fix was reverted because it ruined copy/paste of Calc charts (bug 72727) into other LO documents.

The reason that this wasn't tested in the 5.0.x series was because chart display in reports was screwed for that series.
 

So we are now in a situation where this bug persists, and I have re-opened bug 68663 as a result.

*** This bug has been marked as a duplicate of bug 68663 ***
Comment 8 branestawm 2016-06-16 12:26:09 UTC
(In reply to Alex Thurgood from comment #7)
> (In reply to Alex Thurgood from comment #6)
> > Is this not a DUP of bug 68663 ?
> 
> The absence of data points is bug 68663 which Lionel originally provided a
> fix for, but which fix was reverted because it ruined copy/paste of Calc
> charts (bug 72727) into other LO documents.
> 
> The reason that this wasn't tested in the 5.0.x series was because chart
> display in reports was screwed for that series.
>  
> 
> So we are now in a situation where this bug persists, and I have re-opened
> bug 68663 as a result.
> 
> *** This bug has been marked as a duplicate of bug 68663 ***

Disagree that this bug duplicates bug 68663. This is about missing data points in the body of a line chart, for which the x-values setting in bug 68633 is surely redundant? I can find no mention of missing data points in bug 68633.

My (other) bug report 100381 would seem to be the duplicate of 68633 - both are concerned with x-values field setting in XY charts.
Comment 9 Robert Großkopf 2016-06-16 19:31:53 UTC
Created attachment 125696 [details]
Date formatted in query as string - linechart shows dates

For linecharts must be given two coulumns of a query. One to show the label (x-axis) and one to show the value (y-axis).
The problem seems to be the dates in the table. Have a look at the new created charts in the report. The only difference to the old line-chart-report is: 
1. Dates are formatted as text in the first column of the query.
2. There are two columns in the query. First column is used for x-axis.
Comment 10 branestawm 2016-06-17 17:29:04 UTC
Created attachment 125714 [details]
Extended version of attachment to comment 9

Extended version of attachment to comment 9 to support response.
Comment 11 branestawm 2016-06-17 17:30:14 UTC
Created attachment 125715 [details]
Comparison test of line and xy charts in Calc
Comment 12 branestawm 2016-06-17 17:32:53 UTC
I have experimented with your helpful example - see previous 2 attachments. It seems that the problem lies in Base's Report Builder:

- in Base, report 'line_chart_query_date2' based on 'query_date_cost2' with real dates in field 0, shows x-axis correctly in Report Builder but when executed it shows data points but no x-axis - bug 91289,

- report 'line_chart_query_date3' based on 'query_date_cost3' with dates-as-text in field 1, also shows x-axis correctly in Report Builder but when executed it shows data points but no x-axis - bug 91289,

- report 'XY_chart_query_date2' based on 'query_date_cost2' does not show x-axis correctly in Report Builder and when executed it shows data points but no x-axis,

- in Calc, a Line chart and XY chart work OK, using data copied from table 'Events'. Dates work OK in the XY chart.

For line charts in both Base and Calc, there is one line (set of y-values) for each data series  and no option to specify a data series for x-axis values. Presumably Calc uses row numbers and Base's Report Builder *requires* x-axis values in field 0, although this is apparently not documented.
Comment 13 Robert Großkopf 2016-06-17 18:36:50 UTC
(In reply to branestawm from comment #12)
> Presumably Calc uses row numbers and Base's Report Builder
> *requires* x-axis values in field 0, although this is apparently not
> documented.

I don't know anything about a documentation about charts in the Report-Builder. So I had written down all I have tested and put it in one chapter of the Base-Handbook. Could be i will add something about this special problem.

You could compare charts of Report-Builder with charts of Writer. Don't know if it works all the same in Calc.
Comment 14 Robert Großkopf 2016-06-17 18:40:18 UTC
Created attachment 125716 [details]
Date formatted as integer - linechart and xy-chart are shown right

Have tested a little bit more. If you will change the date to an integer-value, you could use the date for line-charts and xy-charts.
Comment 15 branestawm 2016-06-17 22:04:06 UTC
(In reply to robert from comment #14)
> Created attachment 125716 [details]
> Date formatted as integer - linechart and xy-chart are shown right
> 
> Have tested a little bit more. If you will change the date to an
> integer-value, you could use the date for line-charts and xy-charts.

Thank you for your very helpful suggestion. Although your test file works OK for me, when I tried to use your DATEDIFF function in my test file, I had to change the parameters. In my LO v5.1.3.2, DATEDIFF has only 2 parameters: 'dd' is omitted/assumed and your 2nd & 3rd parameters are reversed.

Perhaps you could add your workaround (and this note) to your Base handbook?

Unfortunately when I try it with my full database which has 1000+ records with grouping, and an xy chart in the group footer, Report Builder shows only some of the data points (10) on the first Chart and when executed produces each chart with all 1000+ data points (no grouping). But the charts do have correct x-axes. I won't have time to work on it some more this weekend, but If I get anywhere next week, I'll post an update.