Bug 135352 - Report Builder requires double entry of query parameter for Calc document type output when based on a query with required parameter input
Summary: Report Builder requires double entry of query parameter for Calc document typ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.4.5.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-07-31 22:50 UTC by Dreamquartz
Modified: 2020-09-15 10:45 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
Example for the use of 10 digits DocumentNumber in a Report (18.27 KB, application/vnd.oasis.opendocument.database)
2020-07-31 22:50 UTC, Dreamquartz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dreamquartz 2020-07-31 22:50:50 UTC
Created attachment 163832 [details]
Example for the use of 10 digits DocumentNumber in a Report

There is an error in the Report Builder in LO 6.4.5.2

The moment the User creates a specific report the result-set is incorrect.

Reports are created as follows:
1. The User creates a Query with the required result-set.
2. The User then uses the Wizard to create a report in the Spreadsheet format to save it as an csv-file.

When creating a report in Text format, the User has enter the parameter once.

The moment the User creates a report in Spreadsheet format, the User needs to the parameter twice.
The parameter for the report is the DocumentNumber. The number consists of 10 digits.
The input format:

Code: LIKE ‘%’ || :DocumentNumber || ‘%’


This allows the User to enter any part of a DocumentNumber.
Using ĹIKE' is a design requirement and cannot be changed.

In the result-set shows the DocumentNumbers as not making any sense.

Attached you will find an example of the information the User uses and a report.
Comment 1 Robert Großkopf 2020-08-01 12:47:01 UTC
You have named the parameter with a name of a field. Try :DocNumber instead of :DocumentNumber, which is also used in the first field of the query.

Don't know why the parameter is asked for two times. Will have a look at this also.
Comment 2 Robert Großkopf 2020-08-01 13:03:29 UTC
The only bug I could find is:
Output-format in Calc requires to input parameter twice
Output-format in Writer requires input parameter only one time.

You could report a new bug for this. But the original reported behavior with wrong result-set is a wrong query. 

If you try to add fields from the query to the report you will see: The field "DocumentNumber" will appear twice. The parameter will be a part of the report. You could ask for all the parameters you add in a report. So the report will show the content of the query and the parameters. And this will only work with different names for the fields.
Comment 3 Dreamquartz 2020-08-09 09:50:36 UTC
Further my investigation.

The result-set of the Report is correct in a sense, when not considering the DocumentNumber.
When comparing the Query result-set to the Report result-set, it is clear that the only difference is the representation of the DocumentNumber.

It appears therefore that there is no need to change the Parameter name.
The Report is apparently not translating the Parameter correctly.
Comment 4 Robert Großkopf 2020-08-09 13:46:16 UTC
The only way for a report to get the content of the parameter is the name of the parameter.

So you could call it a bug ReportBuilder needs different names for fields and parameters. Then change the title of this bug report and I will confirm that the ReportBuilder needs different names. But it works with different names, so it will be a request for an enhancement.
Comment 5 Dreamquartz 2020-08-09 16:15:43 UTC
The problem is not so much the Parameter name therefore, but having the ability to use the Wizard to create a Report from a Query.

The actual DataBase is used where Queries are used in conjunction with Reports.
It is therefore not logical that the User is allowed to create a Query, where LIKE '%'|| :DocumentNumber || '%' can be used, and this Query can be presented to the Report Wizard where Report Builder does not allow it.

It is a a direct functionality link available to the User to use a Query and to create a Report from it.

From a User point of view it does not make sense to use different names for exactly the same thing. The Report uses the Query to run.
The Report is just a way to present result-set of the Query.
The Query does not have a Spreadsheet format as output.

The csv-format is used to be able import data into other external, non controllable databases, owned and operated by external Agencies.
Comment 6 Dreamquartz 2020-08-20 05:01:48 UTC
@Robert

Please confirm the latest information provided in relation to this bug, and indicate how to move forward,
Comment 7 Robert Großkopf 2020-08-20 05:30:00 UTC
@Dreamquartz: Seems there are too much differences between us how the ReportBuilder could/should work. I will remove me from the CC of this bug.
Comment 8 Alex Thurgood 2020-09-15 10:21:15 UTC
Confirming the behaviour with the provided test file.

A report based on the same query asks for the parameter once (ODT) or twice (ODS) depending on the destination document type of the report, all other report properties apparently being equal. This is inconsistent behaviour.

Tested with
Version: 7.0.1.2
Build ID: 7cbcfc562f6eb6708b5ff7d7397325de9e764452
CPU threads: 4; OS: Mac OS X 10.15.6; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR
Calc: threaded
Comment 9 Alex Thurgood 2020-09-15 10:29:51 UTC
One thing I noticed which I don't understand is why the report, irrespective of the report document type, produces two separate entries for the parameter "2020", which do not appear in the query on which the report is based, when using the same parameter.

If the parameter name is changed to DocNumber, or some string not corresponding to a field name, this error disappears.

I suppose as a general rule, one should not be using parameter names that are the same as the field name for which the parameter is being defined.
Comment 10 Alex Thurgood 2020-09-15 10:45:50 UTC
(In reply to Robert Großkopf from comment #2)


> If you try to add fields from the query to the report you will see: The
> field "DocumentNumber" will appear twice. The parameter will be a part of
> the report. You could ask for all the parameters you add in a report. So the
> report will show the content of the query and the parameters. And this will
> only work with different names for the fields.

I guess this explains why the string "2020" as the parameter produces those extra entries, because the report takes the parameter as a report field.

I'm not sure the report builder should be doing this though. A parameter in query isn't a separate field of that query, it is a filter operation.