| View previous topic :: View next topic |
| Author |
Message |
maingroup
Newbie
Joined: 24 Sep 2011
Posts: 3
|
 Posted: Sat Sep 24, 2011 7:05 pm Post subject: Reports and Macro |
 |
|
I have several questions.
1. I have a report. Everytime it opens, I want it to execute this code: MsgBox "Hello World" How do I do that?
2. I have a report based on a query. The query is : Select * from students where name =: EnterYourName. When I open the report, I am prompted to enter a name, then the report displays. I also have a form. On the form there is a text field and a button. Please consider the following scenario. The text field on the form has the text of "Jane Doe". After I click on the button, "Jane Doe" is retrieved from the text field and passed onto the report. The underlying query now reads like this: Select * from students where name = 'Jane Doe' , then the report opens. How do I do that?
Thanks a lot in advance |
|
| Back to top |
|
 |
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sun Sep 25, 2011 6:42 am Post subject: |
|
|
Hello
I have no answer on the first question but you can insert some lines in this macro.
This macro assumes something and I don't if that can work for you.
You must give the report name in the additionnal information field of the button. The report name must be an hierarchicalname. what can be: reportname. A hierarchical name do you use when you have sub maps for the reports.
You must filter the data in your form with the same fieldnames as in your report.
The button must be in the dataform in which you filter the data
I believe this two subs can be used for all buttons,forms and databases without modifying
The function openreport can be used for other more if you give it the good values
I hope this helps you.
Romke
| Code: | sub CreateReportCalledfromButton(oEvent as object)
' We need this sub for a button in a standalone form
dim oCreateButton,oForm ' This are form details
dim oConnection,oReportsDocuments
' The connection we need for finding the database document
oCreateButton=oEvent.source.model
if oCreateButton.tag = "" then
msgbox "No report name in the button tag" & chr(13) & "This is the thrird from below of the property on the common tabpage"
else ' Now we have to start to find all kind of objects
oForm=oCreateButton.parent
if oForm.filter="" then
print "You have not filtered data in your form"
else
oConnection=oForm.Activeconnection
oReportsDocuments=oConnection.parent.DatabaseDocument.ReportDocuments ' This point to the report containers
' Test if the name exist.
if oReportsDocuments.hasByHierarchicalName(oCreateButton.tag) then
'Call openreport with the parameters
openReport(oConnection, oReportsDocuments, oCreateButton.tag , oForm.Filter)
else print "There is a wrong report name"
end if
end if
end if
end sub |
| Code: | function openReport(oConnection, oReportsDocuments,aReportName,sFilter) as object
' With working in the way I do now I can use these sub for calling from a document
' what is stored in a databasedocument but also from a standalone form
' It is assumend you do all the test before you call this sub. This means
' You must be sure there is a connection to the database.
' The document you want call must be
' a)in the databasedocument from this connection.
' b) The document must be a report
' When I use a standalone form then the databasedocument does not have a
' currentcontroller. It can be that the database is already and it does have a currentroller
' but this is also working
' We make here the array we use in both commands
dim aProp(2) as new com.sun.star.beans.PropertyValue
dim oReportDesign
aProp(1).Name = "ActiveConnection"
aProp(1).Value = oConnection
aProp(0).Name = "Hidden"
aProp(0).Value = true
aProp(2).Name = "OpenMode"
aProp(2).Value = "openDesign"
'Open report in design mode and hidden
oReportDesign=oReportsDocuments.loadComponentFromURL(aReportName ,"",0,aProp())
oReportDesign.filter=sFilter ' Change the filter
' Two Value must be changed
aProp(0).Value = false
aProp(2).Value = "open"
' Now the result of the report wil be showed. and the the hidden report in design mode is closed
openReport=oReportsDocuments.loadComponentFromURL(aReportName ,"",23,aProp())
end function |
|
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Mon Sep 26, 2011 8:53 am Post subject: |
|
|
Hello
This is an example for selecting data in a form and then make a reportdocument.
First the form and the document must have the same fieldnames special the names who are used for filtering. Other fieldnames can be different but I have not test it. When you use a wrong fieldname then you get an error when the report is generated. This method assumes you use the standard form-filter but when you use binoculair or Record search then it is not working.
I think you can use this for all database engines there it is based on the standard filter.
I use some subs
The report generator
The report generator is a real simple sub. It get four different parameters.
a) The connection for the database
b) The object where the reportdocuments are in this database: thisDatabaseDocument.ReportDocuments
c) The reportname with his hierarchicalname. This names starts not with a slash
d) The complete filter
I have not test it but possible this report generator can be used for more databases and also for standalone forms. Then the macro must be stored in My macros.
Be a ware make-ing a report is two steps process
a) loading the report in design mode. I load it hidden and then change the filter
b) generating the report. You see only this. I call it the result report.
The result report is not part of the database it is a normal writer document what you can print. There I use the loadcomponentfromurl in the macro it is all real short to the OOo API
The filter
I do not make a filter by code but copy the filter you are making with the form-filter.
Romke |
|
| Back to top |
|
|
maingroup
Newbie
Joined: 24 Sep 2011
Posts: 3
|
| Posted: Fri Sep 30, 2011 7:48 pm Post subject: |
|
|
| Thanks for your reply. I have a report based on a query. The viability of a text box depends on the result of the query. How do I do that? |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sat Oct 01, 2011 1:47 am Post subject: |
|
|
Hello
You did get two answers: from Villeroy and from me. It is to you to make a choice about how to do. But you have to learn how OOo is working.
When you want follow your own idea then you must learn how the API is working. You can use the second macro and feed it with the good parameters.
There are lots of macro whos take a value from a text control. Then make from that value a filter and pass it to the function. But I think it is better to work with my idea or with the idea of Villeroy
Romke |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sat Oct 01, 2011 1:55 pm Post subject: |
|
|
Hello
I think the next sub can maybe also good for some purpose. But I think it is not what the OP ask. I hope I have given enough information as comment.
Romke
| Code: | sub CreateReportCalledfromButton(oEvent as object)
' Do it with a select field
' We need this sub for a button in a standalone form
' This sub use a little other method.
' with a big dataset you can use the form filter. Then for selecting some
' records you can select a field. This field will be used
' In the filter. But I don't use the form filter but use the
' filter of the Singleselectquerycomposer. Each time I copy the filter of the form
' to the Singleselectquerycomposer filter and add the selcted field to
' the filter and I pass this filter to the report.
' The form filter is unchanged
' I think there is no reason to change any thing in this sub
dim oCreateButton,oForm ' This are form details
dim oConnection,oReportsDocuments
' The connection we need for finding the database document
oCreateButton=oEvent.source.model
' The button must not get the focus
if oCreateButton.FocusOnClick=true then
oCreateButton.FocusOnClick=false
msgbox "Change the get focus flag to false" & chr(13) & _
"Click button again"
end if
' The tagfield is addtional information must contain the report name
if oCreateButton.tag = "" then
msgbox "No report name in the button tag" & chr(13) & "This is the thrird from below of the property on the common tabpage"
else ' Now we have to start to find all kind of objects
dim oFormModel,oFormController
oFormModel=oCreateButton.parent ' Go from a button to the form
oFormController=thiscomponent.currentcontroller.getFormController(oFormModel) 'Take the formview
dim oCurrentControl,oWorkcontrol
oCurrentControl=oFormController.CurrentControl ' Take the current control in the form
' When the currentcontrol is in a grid then it is more difficult
if oCurrentControl.implementationname = "com.sun.star.form.FmXGridControl" then
' We can get here an error but select a field and it works
if oCurrentControl.CurrentColumnPosition=-1 then
msgbox("Select a field in a grid")
exit sub
end if
oWorkcontrol=oCurrentControl.getbyindex(oCurrentControl.CurrentColumnPosition)
else oWorkcontrol=oCurrentControl ' For a control not in a grid
end if
dim oSingleComposer
oSingleComposer = oFormModel.SingleSelectQueryComposer
' I must init the filter every time other wise it is still growing
oSingleComposer.filter=oFormModel.filter ' Take the filter of forms
oSingleComposer.appendFilterByColumn(oWorkcontrol.model.boundfield ,TRUE,1)
if oSingleComposer.filter="" then
print "You have not filtered data in your form"
else
oConnection=oFormModel.Activeconnection
oReportsDocuments=oConnection.parent.DatabaseDocument.ReportDocuments ' This point to the report containers
' Test if the name exist.
if oReportsDocuments.hasByHierarchicalName(oCreateButton.tag) then
'Call openreport with the parameters
openReport(oConnection, oReportsDocuments, oCreateButton.tag , oSingleComposer.Filter)
else print "There is a wrong report name"
end if
end if
end if
end sub
|
|
|
| Back to top |
|
|
maingroup
Newbie
Joined: 24 Sep 2011
Posts: 3
|
| Posted: Sat Oct 01, 2011 6:09 pm Post subject: |
|
|
| Thank you. |
|
| Back to top |
|
|
|
