I have a report based on a query which contains a variable parameter in a condition. When I run it directly, a dialog box asks me to supply a value for the parameter. How can I call this report in a macro and pass the value to it?
Hello,
You can do this by using a single record table filter. Instead of the query using a parameter it uses the field in the table record. This field can be filled from another source such as an existing field on a form.
A sample of this is in this post → Base: Print Button
The “Invoices” form is used. It has a push button with macro attached to get the InvoiceID and update the filter table with it and then execute the report. The query reads the filter table field instead of a parameter.
Thank you. This was very helpful, but the route I chose (closest to what I already knew from Paradox) was to create a temporary table (as shown in [Asking for help with Temporary Tables y LibreOffice Base]) and to use that as the source for the report design.
I was however, disappointed to find that the syntax
CREATE [TEMP] TABLE "Answer" AS SELECT ...
did not seem to work (“AS” objected to), requiring the much longer SQL statement
CREATE [TEMP] TABLE "Answer" ("Field1" type, "Field2" type etc.)
INSERT INTO "Answer" SELECT ...
Does LO Base not support the shorter syntax?
Base is NOT a database. It is the database you are using which determines the syntax in use.
The temp table is basically the same as what was presented as a filter table. Depending upon the database it may always be there.
Perhaps, rather than asking whether Base supports CREATE TABLE [name] AS SELECT ..., I should have asked if this is supported by HSQL.
Cannot recall you ever stating what database you are actually using. You have posted a sample using HSQLDB embedded but that is just a sample. I do that all the time and my personal use DB is PostgreSQL. Even the mention in the comment does not specify if this is the embedded version (hope not as this is prone to data loss especially with large amounts of data) or if this is a split DB and which specific version is used.
This appears not available in v1.8 - CREATE TABLE
But is in later versions - v2.3.4
Although attempts are made to answer specific DB questions, this is actually not part of LO. There are many DB’s and it makes it difficult to cover all of it.
That is a rather disquieting response! Yes, I am using the embedded HSQLDB; I am conscious of instability in LO Base while designing, but assumed that would not apply to general ‘operation.’ Sounds as though I should be using one of the other (many) versions of SQL. Have solved one of the other issues I was going to post, but it has been a tough (slow) day …
The potential for HSQLDB embedded problems is often noted and most suggest it is to be used with caution. It is not difficult to move to a split HSQLDB. See →
[Wizard] Create a new ‘split’ HSQL 2.x database
I do not really experience much if any instability while designing.
This, along with the fact HSQLDB embedded (it is v1.8.x) is very old is reason attempts to switch to Firebird 3.x embedded (available since at lease LO 5.x) have been undertaken.
Edit:
Should also warn that if you do change to a different database (even a version change) you probably will get some SQL errors. This is due to the age of embedded v1.8.x and standards changing. There should be no problem with forms unless based upon a query. Usually query problems are relatively easy to fix.
Phew! I already had another copy of the database in SQLite. Have now successfully connected, but it has taken me an entire day to instal ODBC, ODBC Manager and then still to configure files manually, guessing at what was not tying up. Hope to move forward again and to check out existing SQL statements. Useful link for using LibreOffice as front end to SQLite: Andrew Heiss blog