Apache OpenOffice Community Forum

Posted: **Thu Jan 15, 2015 12:37 pm**

Hi, I am new to openoffice, I have used M$ products till now, but I was sick of paying for such crappy software, so I decided to use OO from now... I had database in Access, so I must create new one in order to use it with OO... but I have a problem. I have queries that asks for user input like this:

Code: Select all

SELECT "ID", "Name", "Service", "Price", "Date1", "P?", "P Way", "DP" FROM "tblServices" WHERE LOWER ( "Name" ) LIKE '%' || LOWER ( :IMM ) || '%' AND "P?" = 0

Queries works fine, but when I try to make report from this query some random values are displayed and report doesn't ask for input... In access this was simple, report was 100% based on query, and in OO I can't solve this...

please help,what to do in order to display report that is based on query with user input.

tnx in advance

Posted: **Thu Jan 15, 2015 12:58 pm**

Hello,
that can e.g. be done with a one row Filter table

Code: Select all

CREATE TABLE "tblFilter"("ID_F" BOOLEAN NOT NULL PRIMARY KEY,"F_NAME" VARCHAR(50))

Check the PK in that table enter the search-Name into that one row, and store the row to make it existing.
Now modify your Query for the report

Code: Select all

SELECT "ID", "ID_F", "Name", "Service", "Price", "Date1", "P?", "P Way", "DP" FROM "tblServices","tblFilter" WHERE (LOWER ( "Name" ) LIKE '%' || LOWER ("F_NAME" ) || '%'  OR "F_NAME" IS NULL) AND "P?" = 0

You can make these things more comfortable if you use a Form, containing a filterfield bound to "F_NAME" and a Button with makro to directly open the report from that form.
R
EDIT: If you don't the Oracle-Report-Builder, but the shipped one, a report based on a parameter-query will promt the input dialog for the parameters, see attached example.

Posted: **Thu Jan 15, 2015 3:40 pm**

I am using shipped report builder, I found what the problem was...query works if I don't set report sorting by date... I am not sure why, but without sorting, everything is OK

Apache OpenOffice Community Forum

Report based on query with user input

Report based on query with user input

Re: report based on query with user input

Re: report based on query with user input