Page 1 of 1
Is it possible to have a Form Generate a Report
Posted: Sat Oct 12, 2013 12:17 am
by Reversephase
So what I would like to do is, Have a Simple form, With a Start Date box a End Date Box and a Generate Report Button
So I can simply enter a Start date, And Date, click the button, and have it pass the 2 data fields to my Report
my Report is basically like this
Select * From Table Where Start > :Enter_Start_date AND < :Enter_End_date
if I run the report, I get a rather ugly input box to enter the dates and all is fine, But can I have thoes dates entered Via a form to make a more Polished Solution ?
Using OO 4.0.1
Re: Is it possible to have a Form Generate a Report
Posted: Sat Oct 12, 2013 12:00 pm
by Villeroy
Re: Is it possible to have a Form Generate a Report
Posted: Sat Oct 12, 2013 12:36 pm
by F3K Total
Hi,
yes, that's possible. I usually use a "Filter" table as Villeroy does, and a small makro to open the report.
Find attached a sample.
R
Re: Is it possible to have a Form Generate a Report
Posted: Sun Oct 13, 2013 3:00 am
by Reversephase
Grate Examples guys, F3k, Thats almost bang on what im looking for....
Now, Is it possible to do this with out a filter table ? Say for example I'm working with a database where I do not have the ability to modify the structure, Add tables... Read only access
Can I pull the data right out of the form, or must I have a access to a table set up to hold the data?
Also, Looking at the Date box, and the supported date types, YYYYY/MM/DD , MM/DD/YYYY etc... But What I don't see is a unix time stamp option... or a epoch time stamp format.
One of my challenges is, that the software developer of the program im working with, has chosen to use epoch timestamps as the dates
Here is a basic pseudo code of my End game
Open form
Enter Start Date
End End Date
Convert Start date to epoch timestamp and set to a variable Sdate
Convert End date to epoch timestamp and to variable Edate
Click Generate Report button
Generate Report button triggers a report i have created in reports
Report referances query i have created to fet my data
Query selects everything from table where createdtimestamp > Sdate and < Edate
User then Prints the report for record keeping.
So far i have taught my self and figured out enough to make the query, and generate the report how i want it, the query is currently using :Enter_Start_date and :Enter_End_date when I run the query, I get a ugly little box, head over to epoch time converter website convert my dates, and put them in... I'm trying to make a more polished way of doing it... And from your examples, I should be able to pull it off , with some tweaks here and there...
Re: Is it possible to have a Form Generate a Report
Posted: Sun Oct 13, 2013 9:26 am
by F3K Total
Hi,
find attached a new Version of my sample, including a second form, query and report, working without a filter table.
It's modifiing the query by code.
You can adjust the query to each type of database, if you adjust these codelines, using the correct SQL-Syntax of the currently connected database
Code: Select all
sSQL = "SELECT ""Tbl_Contacts"".""ID"", ""Tbl_Sessions"".""ID"", ""Tbl_Contacts"".""SName"", ...
if odatFROM.Date > 0 then
sdatFROM$ = format(cdatefromiso(odatFROM.Date),"'YYYY-MM-DD'"
sSQL = sSQL & "AND ""Tbl_Sessions"".""Date"" >= "+sdatFROM
endif
if odatTO.Date > 0 then
sdatTO$ = format(cdatefromiso(odatTO.Date),"'YYYY-MM-DD'")
sSQL = sSQL & "AND ""Tbl_Sessions"".""Date"" <= "+sdatTO
endif
R
Re: Is it possible to have a Form Generate a Report
Posted: Tue Oct 15, 2013 8:55 pm
by Reversephase
Perfect!
That works great on my home computer! and I was able to adapt it to do what i needed it to do...
Unfortinatly it does not work on my work computer, and i'm starting to get really ticked off with Openoffice
Re: Is it possible to have a Form Generate a Report
Posted: Tue Oct 15, 2013 9:01 pm
by F3K Total
Hi,
there must be a difference between your home and your work computer.
R
Re: Is it possible to have a Form Generate a Report
Posted: Tue Oct 15, 2013 10:10 pm
by Reversephase
I wish i could figure out the difference....
Re: Is it possible to have a Form Generate a Report
Posted: Tue Oct 15, 2013 10:16 pm
by F3K Total
you've 32-Bit Java on each machine?
Re: Is it possible to have a Form Generate a Report
Posted: Tue Oct 15, 2013 10:29 pm
by Reversephase
Yup, even downgraded the java on this computer to match the java version on my home computer, no luck...
Installed a Fresh Openoffice and newst java in a virtual machine, and the dam thing works. Did a fresh install of java and Openoffice on this machine, still no go...
Re: Is it possible to have a Form Generate a Report
Posted: Tue Oct 15, 2013 10:31 pm
by F3K Total
You're sure, you have 32-bit Java on that 64-Bit machine???
Re: Is it possible to have a Form Generate a Report
Posted: Tue Oct 15, 2013 10:42 pm
by Reversephase
Yup, Turns out its an issue with Regional Settings, Work computer is set to English ( Canada ) home computer is set to English ( United States ) changed work computer to English ( United States ) now my code will execute...
Re: Is it possible to have a Form Generate a Report
Posted: Tue Oct 15, 2013 10:49 pm
by F3K Total
Computer are like submarines, open a window ...
Re: Is it possible to have a Form Generate a Report
Posted: Tue Oct 15, 2013 11:10 pm
by Villeroy
If you simply store your filter criteria in a filter table, you don't have all these problems with 7 different date types.
Bind your report to the following query:
Code: Select all
SELECT * FROM "Data","Filter" WHERE "Filter"."RowID"=1 AND "Data"."Date" BETWEEN "Filter"."D1" AND "Filter"."D2"AND "Data"."SomeOtherField"="Filter"."Blah"
Re: Is it possible to have a Form Generate a Report
Posted: Wed Oct 16, 2013 12:24 am
by Reversephase
Evan if i stored the data into another table, i would still have to datediff it to covert it to the correct timestamp...
Re: Is it possible to have a Form Generate a Report
Posted: Wed Oct 16, 2013 12:50 am
by Villeroy
Code: Select all
SELECT *, DateDiff('ss', '1970-01-01', "Data"."Date") AS "Epoch Time"
FROM "Data","Filter"
WHERE "Filter"."RowID"=1 AND "Data"."Date" BETWEEN "Filter"."D1" AND "Filter"."D2"AND "Data"."SomeOtherField"="Filter"."Blah"