[Solved] Display report for current record
[Solved] Display report for current record
Hey guys and girls,
I've really come a long way since I started messing around with ooBase and databases in general... I'm getting closer to completing the first version of my medical electronic archive database, and I'm very proud of it
What I'm still missing is just this one issue, but it's ESSENTIAL!!! How, oh HOW do I get the report created for the current record that I am looking at in the form?? I suppose I'll have to use a macro for this, but I don't know whare to start... it would be neat to get a macro to:
and I believe the best approach would be:
delete temp database
copy current record to the temp database
open the report for the temp database
I suppose I could organise the form so I print the forms directly, but than I won't be able to get the NICE printout with concat and whatnots
So, please, please, PLEASE help me out on this... I've heard idas but nothing that would actually help me get it working....
I've really come a long way since I started messing around with ooBase and databases in general... I'm getting closer to completing the first version of my medical electronic archive database, and I'm very proud of it
What I'm still missing is just this one issue, but it's ESSENTIAL!!! How, oh HOW do I get the report created for the current record that I am looking at in the form?? I suppose I'll have to use a macro for this, but I don't know whare to start... it would be neat to get a macro to:
and I believe the best approach would be:
delete temp database
copy current record to the temp database
open the report for the temp database
I suppose I could organise the form so I print the forms directly, but than I won't be able to get the NICE printout with concat and whatnots
So, please, please, PLEASE help me out on this... I've heard idas but nothing that would actually help me get it working....
Last edited by NiksaVel on Fri May 09, 2008 11:29 am, edited 1 time in total.
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Display report for current record
OK well, I'm going to try and give you an example no this tonight.
One possible solution is to
But anyway it will show you the basic steps.
One possible solution is to
- Base your report on a Query
- From the Forum use a macro to set a Filter on that query
- Open the report
But anyway it will show you the basic steps.
Edit: Change in plan - I think I'll base this on a much simpler database and report so that I don't confuse the issues in involved |
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Display report for current record
Three tables
Employees
Tasks
I will keep this simple, so three is only a 1..n relationship here. In other tasks belong to one and only one employee.
"Employees"("EmployeeID" IDENTITY PRIMARY KEY,"EmployeeNumber" VARCHAR(50),"FirstName" VARCHAR(50),"MiddleName" VARCHAR(50),"LastName" VARCHAR(50));
"Tasks"("Description" LONGVARCHAR,"EndDate" DATE,"Notes" LONGVARCHAR,"StartDate" DATE,"TaskID" IDENTITY NULL PRIMARY KEY. "EmployeeID" INTEGER)
A single form that will allow us to add emplyee and tasks.
Two reports - One a Report Wizard report and the second a Report Builder report.
For the Repot Wizard report we will list all tasks for Employees - then limit this as needed to a single Employee.
For the Report Builder report we will lsit all tasks for Employees - but we want to be able to limit to a Single Employee and a Range of ending dates for the tasks.
That should be a resaonable amount of functionalility without getting lost in the weeds with extraneous coding I think..
Alright then here is the basic Database - without any macro calls...so the buttons don't work. Now we will start to add the macro(s)...
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Create a new Basic Library
The very first thing you need to do now is to create a library to hold your macros.
From any OpenOffice.org document window select
Tools > Macros > Organize Dialogs
Select the tabl 'Libraries'
Click the new button and give the new library a meaningful name: I choose to name them the same as the database, so "single_record_report:
Now this library will be selected in the list on the left of the dialog and you want to click on the Edit button.
As soon as the Basic IDE opens we want to enter two stubs for our macros:
Click on the save file button.
Now we need to assign these to the buttons on the form.
With the databse opened, open the form for editing ( right click on the form name and select edit )
Now double click the first button "Report Wizard" and the property editor opens. Select the tab "Events" and find the event "When Initializing". All the way to the right is a button with 3 dots ".." click on this.
This opens the "Assign Action" dialog and you want to click the button "Macro"
You will see two folders on the left list of the dialog - the library you just made is under "My Macros"
Expand this and you will see the libary "single_record_report" - click this and you then see Module 1, click that again and you see the procedures you created.
Select the onClickbuttn_reportWizard and click OK, click OK again.
Now select the other button, repeat the process but select the proceudre onClickbutton_reportBuilder
Save the form and close it.
Now you should be able to double click the report to run it and when you click on the first button a dialog pops up with
"Clicked report wizard" and the second says "Clicked report builder"
Assuming that is working - then you are ready to do the actual work.
From any OpenOffice.org document window select
Tools > Macros > Organize Dialogs
Select the tabl 'Libraries'
Click the new button and give the new library a meaningful name: I choose to name them the same as the database, so "single_record_report:
Now this library will be selected in the list on the left of the dialog and you want to click on the Edit button.
As soon as the Basic IDE opens we want to enter two stubs for our macros:
Code: Select all
sub onClickButton_reportWizard( oEvent as object )
print "Clicked report wizard button"
end sub
sub onClickButton_reportBuilder( oEvent as object )
print "Clicked report builder button"
end sub
Now we need to assign these to the buttons on the form.
With the databse opened, open the form for editing ( right click on the form name and select edit )
Now double click the first button "Report Wizard" and the property editor opens. Select the tab "Events" and find the event "When Initializing". All the way to the right is a button with 3 dots ".." click on this.
This opens the "Assign Action" dialog and you want to click the button "Macro"
You will see two folders on the left list of the dialog - the library you just made is under "My Macros"
Expand this and you will see the libary "single_record_report" - click this and you then see Module 1, click that again and you see the procedures you created.
Select the onClickbuttn_reportWizard and click OK, click OK again.
Now select the other button, repeat the process but select the proceudre onClickbutton_reportBuilder
Save the form and close it.
Now you should be able to double click the report to run it and when you click on the first button a dialog pops up with
"Clicked report wizard" and the second says "Clicked report builder"
Assuming that is working - then you are ready to do the actual work.
Edit: Also, not a bad time to save your databse file |
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Form Control Events pass an Event Strucure
Alright those macros are pretty simple. But before you go any further you should under stand one thing
sub onClickButton_reportWizard( oEvent as object )
What does "oEvent as object" mean?
For all controls on a form, when you assign an event to a macro there is always one parameter passed to the macro - this parameter is a Structure and is named the EventObject - You can find the documenation for this at:
http://api.openoffice.org/docs/common/r ... bject.html
Now that documentaiton is fairly sparse, but it does show that there will always be at least one member of the structure and thast is named Source.
In our case then the oEvent.Source is the button that was clicked.
Sometimes the EventObject will have additional pieces of information also - additional memerbs - but it will always have this Source.
( An Important point here is that this is always TRUE for events from Controls on Forums but alwyas true for other types of events in OpenOffice.org )
Fine - next piece of information to understand - all controls on a form belong to a hierarchy ( ownership strucure ). You can see this in the form navigator window:
The hierarchy always begins with the root "forms", this is a container which owns all the controls on the form, visible or hidden.
Just below this root "forums" is the control named "MainForm". This first level is always a control of type "dataform" and is a non-visual control that acts as the glue between the records in your database and the visual controls on the form.
( NOTE: When you use the Form Wizard to create a form the top level form is always nemed MainForm and if you add a sub form it is named SubForm )
So - Every visual control ona form will belong to one of these "dataform" controls. This dataform control is referred to as the visual controls "Parent".
Open the Employee form in edit mode again and open the forum navigator. ( you can do this on the "Form Design" toolbar - it is the 5th button from the left )
Now select the control "MainForm", right click and select "Properties".
Select the tabl "Data"
Like I said this is the link to the actual data. In this case we are connected directly to the Table, Employees.
This is emportant because to run our report for just one Employee we must find out which employee is currently being viewed in the form.
With this abiltiy to get back to the actual link to the table this becomes easy.
For our purposes what we really want to know is:
What is the value of the EmployeeID field for the employee record currently in the form?
Here is the macro updated to find that information.
Go ahead and copy the above macro into your library, save it, close the form and run it...now click the first button.
Move to the second record and click the button again.
See even though the EmployeeID field is not displayed on the form, because we are connected to the Employees table then we can get any field we want.
The acutal documentaiton for the getInt function can be found at:
http://api.openoffice.org/docs/common/r ... tml#getInt
and the documentaiton for the findColumn function is at:
http://api.openoffice.org/docs/common/r ... findColumn
Now make the changes to the onClickbutton_reportBuilder to also get the EmployeeID value.
sub onClickButton_reportWizard( oEvent as object )
What does "oEvent as object" mean?
For all controls on a form, when you assign an event to a macro there is always one parameter passed to the macro - this parameter is a Structure and is named the EventObject - You can find the documenation for this at:
http://api.openoffice.org/docs/common/r ... bject.html
Now that documentaiton is fairly sparse, but it does show that there will always be at least one member of the structure and thast is named Source.
In our case then the oEvent.Source is the button that was clicked.
Sometimes the EventObject will have additional pieces of information also - additional memerbs - but it will always have this Source.
( An Important point here is that this is always TRUE for events from Controls on Forums but alwyas true for other types of events in OpenOffice.org )
Fine - next piece of information to understand - all controls on a form belong to a hierarchy ( ownership strucure ). You can see this in the form navigator window:
The hierarchy always begins with the root "forms", this is a container which owns all the controls on the form, visible or hidden.
Just below this root "forums" is the control named "MainForm". This first level is always a control of type "dataform" and is a non-visual control that acts as the glue between the records in your database and the visual controls on the form.
( NOTE: When you use the Form Wizard to create a form the top level form is always nemed MainForm and if you add a sub form it is named SubForm )
So - Every visual control ona form will belong to one of these "dataform" controls. This dataform control is referred to as the visual controls "Parent".
Open the Employee form in edit mode again and open the forum navigator. ( you can do this on the "Form Design" toolbar - it is the 5th button from the left )
Now select the control "MainForm", right click and select "Properties".
Select the tabl "Data"
Like I said this is the link to the actual data. In this case we are connected directly to the Table, Employees.
This is emportant because to run our report for just one Employee we must find out which employee is currently being viewed in the form.
With this abiltiy to get back to the actual link to the table this becomes easy.
For our purposes what we really want to know is:
What is the value of the EmployeeID field for the employee record currently in the form?
Here is the macro updated to find that information.
Code: Select all
sub onClickButton_reportWizard( oEvent as object )
dim tblEmployees
dim EmplID as integer
REM oEvent is an EventObject
REM Source is the control that called the event - the button
REM Model is what we use when we want to get to properties avialable at design time
REM Parent is the name of the contol that the button belongs to
tblEmployees = oEvent.Source.Model.Parent
EmplID = tblEmployees.getInt( tblEmployees.FindColumn( "EmployeeID" ) )
print "Currently Viewing record for employee with ID =" & EmplID
end sub
Move to the second record and click the button again.
See even though the EmployeeID field is not displayed on the form, because we are connected to the Employees table then we can get any field we want.
The acutal documentaiton for the getInt function can be found at:
http://api.openoffice.org/docs/common/r ... tml#getInt
and the documentaiton for the findColumn function is at:
http://api.openoffice.org/docs/common/r ... findColumn
Now make the changes to the onClickbutton_reportBuilder to also get the EmployeeID value.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Get the query we want to change
Alight then assuming you can now click either button and have the message:
Currently Viewing record for employee with ID = 0
disiplay you are ready to add the next piece.
Remember I said that they way I would do this is:
Base the report on a query
Set a filter on the query
Run the report
So what has to happen now is to get the query.
To make it simple I based both reports on the same query, "qryEmplTasks_ReportWizard", I was going to use 2 separate queries but decided against it..so the name is a little misleading but...none the less.
Alright well there are any number of different ways you could get a Query ( really what you want is the Query Definition object - those are what you see in the Base file Query window ).
For this example I am going to stick to working with a starting point of the EventObject.
You have a variable in your macros now that is the MasterForm dataform control - which you treated as a table. That is actually a trick that Basic does for you, as the dataform control has many properties.
Another one of these properties is named "ActiveConnection", which has as one of it's properties "Queries". This "Queries" property is a container for all the QueryDefinitions in the Base file. Darned convienent actually.
So let's enhance the macro to fill a variable with the actual QueryDefinition that was used as the record source for the Reports.
Update your library with this code, save it and run your form.
Now when you click the first buton you should see this dialog on your screen: Hopefully at this point you shuold understand how we got to this point.
All the information needed is now in variables in the macro.
What's left is how to set that filter and for that OpenOffice.org API offers a couple of helpers.
Currently Viewing record for employee with ID = 0
disiplay you are ready to add the next piece.
Remember I said that they way I would do this is:
Base the report on a query
Set a filter on the query
Run the report
So what has to happen now is to get the query.
To make it simple I based both reports on the same query, "qryEmplTasks_ReportWizard", I was going to use 2 separate queries but decided against it..so the name is a little misleading but...none the less.
Alright well there are any number of different ways you could get a Query ( really what you want is the Query Definition object - those are what you see in the Base file Query window ).
For this example I am going to stick to working with a starting point of the EventObject.
You have a variable in your macros now that is the MasterForm dataform control - which you treated as a table. That is actually a trick that Basic does for you, as the dataform control has many properties.
Another one of these properties is named "ActiveConnection", which has as one of it's properties "Queries". This "Queries" property is a container for all the QueryDefinitions in the Base file. Darned convienent actually.
So let's enhance the macro to fill a variable with the actual QueryDefinition that was used as the record source for the Reports.
Code: Select all
sub onClickButton_reportWizard( oEvent as object )
dim tblEmployees
dim EmplID as integer
dim RepQuery as variant
REM oEvent is an EventObject
REM Source is the control that called the event - the button
REM Model is what we use when we want to get to properties avialable at design time
REM Parent is the name of the contol that the button belongs to
tblEmployees = oEvent.Source.Model.Parent
RepQuery = tblEmployees.ActiveConnection.Queries.getByName( "qryEmplTasks_ReportWizard" )
EmplID = tblEmployees.getInt( tblEmployees.FindColumn( "EmployeeID" ) )
REM The acutal SQL statement for the QueryDefinition is in a property named Command
print RepQuery.Command
end sub
Now when you click the first buton you should see this dialog on your screen: Hopefully at this point you shuold understand how we got to this point.
All the information needed is now in variables in the macro.
What's left is how to set that filter and for that OpenOffice.org API offers a couple of helpers.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Setting a filter on a Query
OK - here I am going to dig a little deeper into the API. Sorry no good away around this.
I am going to restructure the macros in the library and add a new one.
Open your Basic IDE and replace the first macro with this:
Then add this macro at then end of the file
You should be able to run your form now and when you click on the first button you should see this:
Notice that we have the same SQL command as before BUT there is now an added clause
AND ( "EmployeeID" = 1 )
What I have done is to use a helper a SingleQueryComposer - you can find documentiaton on this at
http://api.openoffice.org/docs/common/r ... poser.html
This is a very powerful tool allowing us to manipulate the SQL command without having to write code to deal with each and every part of the command.
I am going to restructure the macros in the library and add a new one.
Open your Basic IDE and replace the first macro with this:
Code: Select all
sub onClickButton_reportWizard( oEvent as object )
dim tblEmployees
dim EmplID as integer
dim RepQuery as variant
REM oEvent is an EventObject
REM Source is the control that called the event - the button
REM Model is what we use when we want to get to properties avialable at design time
REM Parent is the name of the contol that the button belongs to
tblEmployees = oEvent.Source.Model.Parent
EmplID = tblEmployees.getInt( tblEmployees.FindColumn( "EmployeeID" ) )
ExecuteReport( tblEmployees.ActiveConnection, _
"qryEmplTasks_ReportWizard", _
"EmployeeID = " & EmplID, _
"rptEmplTasks_ReportWizard" )
end sub
Code: Select all
sub ExecuteReport( aConnection as variant, _
aQueryName as string, _
aFilter as String, _
aReportName )
dim composer as variant
composer = aConnection.createQueryComposer
composer.setQuery( aConnection.Queries.getByName( aQueryName ).Command )
composer.setFilter( aFilter )
print composer.ComposedQuery
end sub
AND ( "EmployeeID" = 1 )
What I have done is to use a helper a SingleQueryComposer - you can find documentiaton on this at
http://api.openoffice.org/docs/common/r ... poser.html
This is a very powerful tool allowing us to manipulate the SQL command without having to write code to deal with each and every part of the command.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Display report for current record
Well - it is getting late and I am getting tired...so I won't explain all this, till tomorrow, but here is the final macro
I added one last step and that is to put the query back to the way it was - without the added filter - before we finish...a reather important step that I almost didn't think of...until I ran it twice without doing it...LOL
Code: Select all
sub ExecuteReport( aConnection as variant, _
aQueryName as string, _
aFilter as String, _
aReportName )
dim composer as variant
dim oReport as variant
dim aryProp(1) as new com.sun.star.beans.PropertyValue
composer = aConnection.createQueryComposer
composer.setQuery( aConnection.Queries.getByName( aQueryName ).Command )
composer.setFilter( aFilter )
aConnection.Queries.getByName( aQueryName ).Command = composer.ComposedQuery
aryProp(0).Name = "ActiveConnection"
aryProp(0).Value = aConnection
aryProp(1).Name = "OpenMode"
aryProp(1).Value = "open"
aConnection.Parent.DatabaseDocument.ReportDocuments.loadComponentFromURL( aReportName, "_blank", 0, pProp() )
aConnection.Queries.getByName( aQueryName ).Command = composer.getQuery
end sub
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: Display report for current record
Thanks a lot Drew... I see you've really put a lot of time and effort into helping me so I just wanted to give you a big THANK YOU!
It'll take me awhile to go through all this so ... I'll post back later yippeeee
It'll take me awhile to go through all this so ... I'll post back later yippeeee
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Display report for current record
Alright then - let's push on...
Here is all the Basic code so far:
You will see that I made a few changes - So, at this point both buttons are working. One produces the Wizard created report and one the Builder created report.
But, we don't have quite what I set out for do we.
Both reports are filtered to only show the Tasks for the current Employee record in the form - but for the Report Builder based report I was going to go a bit further and add a range of ending dates also...Before we try to add that however, a closer look at the procedure that does the work and the QueryComposer is in order and that is what I'll add today.
Here is all the Basic code so far:
Code: Select all
sub onClickButton_reportWizard( oEvent as object )
dim tblEmployees
dim EmplID as integer
tblEmployees = oEvent.Source.Model.Parent
EmplID = tblEmployees.getInt( tblEmployees.FindColumn( "EmployeeID" ) )
ExecuteReport( _
tblEmployees.ActiveConnection, _
"qryEmplTasks_ReportWizard", _
"EmployeeID = " & EmplID, _
"rptEmplTasks_ReportWizard" _
)
end sub
sub onClickButton_reportBuilder( oEvent as object )
dim tblEmployees
dim EmplID as integer
tblEmployees = oEvent.Source.Model.Parent
EmplID = tblEmployees.getInt( tblEmployees.FindColumn( "EmployeeID" ) )
ExecuteReport( _
tblEmployees.ActiveConnection, _
"qryEmplTasks_ReportWizard", _
"EmployeeID = " & EmplID, _
"rptEmplTasks_ReportBuilder"_
)
end sub
sub ExecuteReport( _
aConnection as variant, _
aQueryName as string, _
aFilter as String, _
aReportName _
)
dim composer as variant
dim oReport as variant
dim aryProp(1) as new com.sun.star.beans.PropertyValue
composer = aConnection.createQueryComposer
composer.setQuery( aConnection.Queries.getByName( aQueryName ).Command )
composer.setFilter( aFilter )
aConnection.Queries.getByName( aQueryName ).Command = composer.ComposedQuery
aryProp(0).Name = "ActiveConnection"
aryProp(0).Value = aConnection
aryProp(1).Name = "OpenMode"
aryProp(1).Value = "open"
aConnection.Parent.DatabaseDocument.ReportDocuments.loadComponentFromURL( aReportName, "_blank", 0, aryProp() )
aConnection.Queries.getByName( aQueryName ).Command = composer.getQuery
end sub
But, we don't have quite what I set out for do we.
Both reports are filtered to only show the Tasks for the current Employee record in the form - but for the Report Builder based report I was going to go a bit further and add a range of ending dates also...Before we try to add that however, a closer look at the procedure that does the work and the QueryComposer is in order and that is what I'll add today.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: Display report for current record
I'd suggest you put this as a sticky... I've found a lot of people asking for this very functionality and this is really an in-depth explanation with code and all
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
Re: Display report for current record
Alright, feedback
I'm super happy to report that I've managed tu integrate the code into my database and it's working as expected!!! Thank you so much Drew, you really helped me out here!!!
Now that I have a working archiving system via open office, this may be the first step to getting my department/hospital to migrate from MS Office to OOo
thanks again,
N
P.S. if you get around to give that code to use time filtering, it could also help me out later when I start making statistical reports etc...
I'm super happy to report that I've managed tu integrate the code into my database and it's working as expected!!! Thank you so much Drew, you really helped me out here!!!
Now that I have a working archiving system via open office, this may be the first step to getting my department/hospital to migrate from MS Office to OOo
thanks again,
N
P.S. if you get around to give that code to use time filtering, it could also help me out later when I start making statistical reports etc...
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Display report for current record
Glad to hear it..
Not sure I have given you this before
Built in functions for Base Queries
Check out the section for Date / Time
Also - from this post I got off onto writing up a small tutorial on working with Quereis - with a heavy dose of using the SingleSelectQueryComposer service form Basic. This is what you really want to be using to do fancy filtering from scripts.. I'm close to done, so maybe later this evening it will go up on the board... I'll post a notice here when I do
Not sure I have given you this before
Built in functions for Base Queries
Check out the section for Date / Time
Also - from this post I got off onto writing up a small tutorial on working with Quereis - with a heavy dose of using the SingleSelectQueryComposer service form Basic. This is what you really want to be using to do fancy filtering from scripts.. I'm close to done, so maybe later this evening it will go up on the board... I'll post a notice here when I do
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: Display report for current record
ok, thanks!
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
Re: Display report for current record in OpenOffice using MySql
Thank You Drew for very useful macro.
Has anyone run it with OpenOffice connected to MySql?
I have exported the example database to mysql database connected through ODBC on Ubuntu (changed Id numbers from 0, as otherwise they did not import to mysql).
The form is working, but the macro for generating report doesn't.
It seems to me that there should be done some modifications in query and in the macro (it works till the filter in query is set), but I'm not advanced enough to fix it.
Does any one knows what to do? Thanks very much in advance!
Has anyone run it with OpenOffice connected to MySql?
I have exported the example database to mysql database connected through ODBC on Ubuntu (changed Id numbers from 0, as otherwise they did not import to mysql).
The form is working, but the macro for generating report doesn't.
It seems to me that there should be done some modifications in query and in the macro (it works till the filter in query is set), but I'm not advanced enough to fix it.
Does any one knows what to do? Thanks very much in advance!
Re: Display report for current record in OpenOffice using MySql
Hello again.
The reason why the macro was not working after exporting base to MySql was very simple to solve.
The error I was getting was: "Column 'EmployeeID' in where clause is ambiguous",
so what I did I added another column with equal data to EmployeeID and named it EmployeeID2 and used it as a filter.
Now it works perfect!
The reason why the macro was not working after exporting base to MySql was very simple to solve.
The error I was getting was: "Column 'EmployeeID' in where clause is ambiguous",
so what I did I added another column with equal data to EmployeeID and named it EmployeeID2 and used it as a filter.
Now it works perfect!
kububa wrote:Thank You Drew for very useful macro.
Has anyone run it with OpenOffice connected to MySql?
I have exported the example database to mysql database connected through ODBC on Ubuntu (changed Id numbers from 0, as otherwise they did not import to mysql).
The form is working, but the macro for generating report doesn't.
It seems to me that there should be done some modifications in query and in the macro (it works till the filter in query is set), but I'm not advanced enough to fix it.
Does any one knows what to do? Thanks very much in advance!
OOo 3.1.1 on Ubuntu 9.10
Re: Display report for current record
A simple way to print a report for current screen shot of a FORM, is to Export the page as a PDF file. Icon 6 from the left on toolbar.
Retain as a PDF file or Print it from any PDF reader.
No programing required and you keep the position of all details you wanted in designing your form.
pjeppie
Retain as a PDF file or Print it from any PDF reader.
No programing required and you keep the position of all details you wanted in designing your form.
pjeppie