Is it possible to have a Form Generate a Report

[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

[Villeroy]

http://forum.openoffice.org/en/forum/do ... p?id=14591

[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

[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...

[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

[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

[F3K Total]

Hi,
there must be a difference between your home and your work computer.
R

[Reversephase]

I wish i could figure out the difference....

[F3K Total]

you've 32-Bit Java on each machine?

[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...

[F3K Total]

You're sure, you have 32-bit Java on that 64-Bit machine???

[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...

[F3K Total]

Computer are like submarines, open a window ...

[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"

[Reversephase]

Evan if i stored the data into another table, i would still have to datediff it to covert it to the correct timestamp...

[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"