Default value for parameter

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Default value for parameter

Post by Nocton »

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
OpenOffice 4.1.12 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Default value for parameter

Post by Villeroy »

A report based on the following should work:

Code: Select all

SELECT * 
FROM "Table"
WHERE "Date"=COALESCE(:paramDate,CURRENT_DATE)
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).

Code: Select all

SELECT "T".* 
FROM "Table" AS "T", "Filter" AS "F"
WHERE ("F"."RowID"=1) AND ("T"."Date"=COALESCE("F"."Date",CURRENT_DATE))
"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.
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
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Default value for parameter

Post by Nocton »

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
OpenOffice 4.1.12 on Windows 10
Post Reply