OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Reports and Macro

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
maingroup
Newbie
Newbie


Joined: 24 Sep 2011
Posts: 3

PostPosted: Sat Sep 24, 2011 7:05 pm    Post subject: Reports and Macro Reply with quote

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
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sun Sep 25, 2011 6:42 am    Post subject: Reply with quote

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
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Sep 26, 2011 8:53 am    Post subject: Reply with quote

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
View user's profile Send private message
maingroup
Newbie
Newbie


Joined: 24 Sep 2011
Posts: 3

PostPosted: Fri Sep 30, 2011 7:48 pm    Post subject: Reply with quote

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
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Oct 01, 2011 1:47 am    Post subject: Reply with quote

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
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Oct 01, 2011 1:55 pm    Post subject: Reply with quote

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
View user's profile Send private message
maingroup
Newbie
Newbie


Joined: 24 Sep 2011
Posts: 3

PostPosted: Sat Oct 01, 2011 6:09 pm    Post subject: Reply with quote

Thank you.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group