I have a report which prints out records according to a date inputted by the user as a parameter for the associated query. When the parameter input box appears the parameter box is empty. I should like it to show a default date. Is that possible?
Regards, Nocton
Default value for parameter
Default value for parameter
OpenOffice 4.1.12 on Windows 10
Re: Default value for parameter
A report based on the following should work:
The issue is: The GUI prompt can not handle empty parameters properly. It treats empty numeric parameters as zero, empty string parameters as empty string while it raises data types errors in any case when the empty parameter is compared with a date field.
It works properly with a criteria table where you enter optional value(s).
"Filter" is a small extra table with filter criteria having one record where the "RowID" is 1. You compare the data table with the criteria in that particular row of the filter table. Coalesce returns the current_date if the date in row #1 is null.
The technique has been discussed frequently as "power filtering" by form controls. You can enter your report criteria into a tiny form for the parameter input and you may show resulting records in a subform before calling the report.
You may also use the direct table view to enter your criteria into row #1 of your filter table instead of the standard parameter dialog.
Code: Select all
SELECT *
FROM "Table"
WHERE "Date"=COALESCE(:paramDate,CURRENT_DATE)
It works properly with a criteria table where you enter optional value(s).
Code: Select all
SELECT "T".*
FROM "Table" AS "T", "Filter" AS "F"
WHERE ("F"."RowID"=1) AND ("T"."Date"=COALESCE("F"."Date",CURRENT_DATE))
The technique has been discussed frequently as "power filtering" by form controls. You can enter your report criteria into a tiny form for the parameter input and you may show resulting records in a subform before calling the report.
You may also use the direct table view to enter your criteria into row #1 of your filter table instead of the standard parameter dialog.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Default value for parameter
Thank you for this, Villeroy. I have used the small filter table method before and I agree that it is a useful technique. However, for the current situation I wanted to stick with the simple parameter approach, so not to have to change the report much. So your use of COALESCE looks ideal. However, when I tried it the default value was not changed from Null. Any ideas why not?
Regards, Nocton
Regards, Nocton
OpenOffice 4.1.12 on Windows 10