Bug 124408 - Report builder does not work with aliases and grouping
Summary: Report builder does not work with aliases and grouping
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.5.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-03-28 20:27 UTC by John Talbut
Modified: 2019-04-10 06:54 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Test database for grouping and aliases (228.94 KB, application/vnd.oasis.opendocument.database)
2019-03-28 20:31 UTC, John Talbut
Details

Note You need to log in before you can comment on or make changes to this bug.
Description John Talbut 2019-03-28 20:27:51 UTC
Description:
If I use aliases for calculated values in a query and use the aliases in a report the fields are blank, but I can use grouping for the report.
If I don't use aliases for calculated values in a query and use the generated names for the fields (e.g. CONCATENATED) in a report the fields shown correctly, but I if use grouping for the report I get errors when I try to run the report.

Steps to Reproduce:
1. Using the attached test database, try each of the four reports.  These give the four different outputs, with and without aliases and with and without grouping.


Actual Results:
See the reports produced.

Expected Results:
Reports with all fields correct and grouping.


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 John Talbut 2019-03-28 20:31:57 UTC
Created attachment 150380 [details]
Test database for grouping and aliases
Comment 2 Alex Thurgood 2019-03-29 15:06:16 UTC
Testing against LO6212 on macOS 10.14.3 :

In all of the reports that will open (the first 3), I repeatedly see what appears to be HTML table instructions in the left hand column, which indeed appears erroneous (and from what I can tell, not present in the query results). Is this relevant, or was the report designed this way ?


The 1st report TestGroupAlias :

- shows truncated data in the second column

The 2nd report TestGroupNoAlias :
- appears to show the required information in the right hand column

The 3rd report TestNoGroupAlias :
- shows truncated data in the second column

The 4th report TestNoGroupNoAlias :
- generates the following error message :


org.libreoffice.report.ReportExecutionException: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Invalid command
*no column name specified for column number 6 in derived table __LibreOffice_report_result
caused by
'isc_dsql_prepare'

org.libreoffice.report.ReportExecutionException: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Invalid command
*no column name specified for column number 6 in derived table __LibreOffice_report_result
caused by
'isc_dsql_prepare'

	at org.libreoffice.report.pentaho.PentahoReportJob.execute(PentahoReportJob.java:350)
	at org.libreoffice.report.pentaho.SOReportJobFactory$_SOReportJobFactory.execute(SOReportJobFactory.java:217)
Caused by: org.jfree.report.ReportDataFactoryException: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Invalid command
*no column name specified for column number 6 in derived table __LibreOffice_report_result
caused by
'isc_dsql_prepare'

	at org.libreoffice.report.pentaho.StarReportDataFactory.queryData(StarReportDataFactory.java:68)
	at org.jfree.report.data.CachingReportDataFactory.queryData(Unknown Source)
	at org.jfree.report.data.ReportDataRow.createDataRow(Unknown Source)
Comment 3 Alex Thurgood 2019-03-29 15:09:19 UTC
There is a whole bunch of bug reports against the report builder linked to problems with grouping, although I couldn't see any that seemed to combine both the grouping and the alias issue.
Comment 4 John Talbut 2019-03-29 15:34:53 UTC
The HTML coding is intentional, the report is used to create the page here: http://dpets.uk/efios/efios/efioscourses.html
Comment 5 John Talbut 2019-03-29 15:38:27 UTC
(In reply to Alex Thurgood from comment #2)
> Testing against LO6212 on macOS 10.14.3 :
> 
> In all of the reports that will open (the first 3), I repeatedly see what
> appears to be HTML table instructions in the left hand column, which indeed
> appears erroneous (and from what I can tell, not present in the query
> results). Is this relevant, or was the report designed this way ?
> 
> 
> The 1st report TestGroupAlias :
> 
> - shows truncated data in the second column
> 
> The 2nd report TestGroupNoAlias :
> - appears to show the required information in the right hand column
> 
> The 3rd report TestNoGroupAlias :
> - shows truncated data in the second column
> 
> The 4th report TestNoGroupNoAlias :
> - generates the following error message :
> 
> 
> org.libreoffice.report.ReportExecutionException: firebird_sdbc error:
> *Dynamic SQL Error
> *SQL error code = -104
> *Invalid command
> *no column name specified for column number 6 in derived table
> __LibreOffice_report_result
> caused by
> 'isc_dsql_prepare'
> 
> org.libreoffice.report.ReportExecutionException: firebird_sdbc error:
> *Dynamic SQL Error
> *SQL error code = -104
> *Invalid command
> *no column name specified for column number 6 in derived table
> __LibreOffice_report_result
> caused by
> 'isc_dsql_prepare'
> 
> 	at
> org.libreoffice.report.pentaho.PentahoReportJob.execute(PentahoReportJob.
> java:350)
> 	at
> org.libreoffice.report.pentaho.SOReportJobFactory$_SOReportJobFactory.
> execute(SOReportJobFactory.java:217)
> Caused by: org.jfree.report.ReportDataFactoryException: firebird_sdbc error:
> *Dynamic SQL Error
> *SQL error code = -104
> *Invalid command
> *no column name specified for column number 6 in derived table
> __LibreOffice_report_result
> caused by
> 'isc_dsql_prepare'
> 
> 	at
> org.libreoffice.report.pentaho.StarReportDataFactory.
> queryData(StarReportDataFactory.java:68)
> 	at org.jfree.report.data.CachingReportDataFactory.queryData(Unknown Source)
> 	at org.jfree.report.data.ReportDataRow.createDataRow(Unknown Source)

Yes, these are the results that I get.
Comment 6 Robert Großkopf 2019-03-29 16:04:34 UTC
For reports some hints:
Report-Builder will analyize the code of the query. This won't work when the code should be executed in direct SQL. And if you switch the analyzing to no, grouping won't work.

Best solution for all reports: Don't user queries. Use views instead. The reports of the test-database will all work with views.

But then another "error" appears. Don't worry about the error-dialogue. The view will be created and could be seen after the tables will be actualized by "View" > "actualize Tables" (or something like this - haven't opened LO in English mode.
Comment 7 Julien Nabet 2019-03-29 16:08:17 UTC
- Reportdesign corresponds to legacy reports according to README https://opengrok.libreoffice.org/xref/core/reportdesign/README?r=fe95086e

- Reportbuilder README indicates:
 Report Builder, or "new style reports", replacing legacy reports (in reportdesign).
 Started as an extension called "Sun Report Builder" then "Oracle Report Builder"
 [http://extensions.services.openoffice.org/project/reportdesign],
 which got bundled with LibreOffice, then converted to an optionally installable
 (but installed by default) part of LibreOffice proper.
 
 Uses the Pentaho Reporting Flow Engine of Pentaho BI [http://www.pentaho.com/].

- https://bugs.documentfoundation.org/show_bug.cgi?id=67832 shows that Pentaho and JFreeReports which are related to Reportbuilder haven't been updated since about 6 years.


1) for Reportbuilder, is this possible to debug this if the pb is in external sources? (or perhaps already fixed in these!) 
Knowing that these seem difficult to update and/or don't seem to interest enough people to give it a try, how can we advance here?


2) Reportdesign has been considered legacy since several years, could it be removed?
(Goal: simplify LO code)
Comment 8 Robert Großkopf 2019-03-29 16:23:27 UTC
(In reply to Julien Nabet from comment #7)
> 2) Reportdesign has been considered legacy since several years, could it be
> removed?
> (Goal: simplify LO code)

Many people who use Ubuntu will get LO without Report-Builder but with the internal legacy reports. Also many people, who use AOO and LO will use this report-engine. So don't remove it. Seems to be a solution which generates a report in a fast and usable way for this people.
Comment 9 Drew Jensen 2019-03-29 16:35:09 UTC
(In reply to Robert Großkopf from comment #8)
> (In reply to Julien Nabet from comment #7)
> > 2) Reportdesign has been considered legacy since several years, could it be
> > removed?
> > (Goal: simplify LO code)
> 
> Many people who use Ubuntu will get LO without Report-Builder but with the
> internal legacy reports. Also many people, who use AOO and LO will use this
> report-engine. So don't remove it. Seems to be a solution which generates a
> report in a fast and usable way for this people.

No any longer - at least not with the binaries from TDF. ReportBuilder (to create templates for the Pentaho engine) can not be removed, at least not with the installation here under Ubuntu. 

The legacy reports still run fine. The UI for creating/edit new legacy reports is disabled. Legacy style reports can be created by hand though and added into a Base file via a macro and they work.

I would not be in favor of removing the legacy reports, quite the opposite I would like to see an extension that made them available via the GUI again.