Report based on query with user input

Getting your data onto paper - or the web - Discussing the reports features of Base

Report based on query with user input

Postby danthe88 » 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   Expand viewCollapse view
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
OpenOffice 4.1.1 / Windows 7 64BIT
danthe88
 
Posts: 2
Joined: Thu Jan 15, 2015 12:32 pm

Re: report based on query with user input

Postby F3K Total » Thu Jan 15, 2015 12:58 pm

Hello,
that can e.g. be done with a one row Filter table
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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.
Attachments
Parameter_Report.odb
(72.29 KiB) Downloaded 117 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 977
Joined: Fri Dec 16, 2011 8:20 pm

Re: report based on query with user input

Postby danthe88 » 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
OpenOffice 4.1.1 / Windows 7 64BIT
danthe88
 
Posts: 2
Joined: Thu Jan 15, 2015 12:32 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests