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.
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.
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.
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.
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.
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.
@Robert Please confirm the latest information provided in relation to this bug, and indicate how to move forward,
@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.
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
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.
(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.
*** Bug 136700 has been marked as a duplicate of this bug. ***
(In reply to Alex Thurgood from comment #9) > 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. I experience the same behaviour (.odt - once / .ods - twice) using firebird ? and LibO Version: 6.4.7.2 Build ID: 1:6.4.7-0ubuntu0.20.04.1, though the parameter expression is slightly different:<br>.... HAVING ( MIN( "tblcondensed"."type" ) = UPPER ( :give_b_or_v ) <br>where the content of the column "tblcondensed"."type" is either 'B' or 'V'<br>Did I not fulfill the recommendation 'column name <> parameter name' ?
(In reply to Alex Thurgood from comment #9) > 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. I experience the same behaviour (.odt - once / .ods - twice) using firebird ? and LibO Version: 6.4.7.2 Build ID: 1:6.4.7-0ubuntu0.20.04.1, though the parameter expression is slightly different: .... HAVING ( MIN( "tblcondensed"."type" ) = UPPER ( :give_b_or_v ) where the content of the column "tblcondensed"."type" is either 'B' or 'V' Did I not fulfill the recommendation 'column name <> parameter name' ?
Dear Dreamquartz, 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