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

how to: send results of table in form to report
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Thu Nov 01, 2007 8:31 am    Post subject: how to: send results of table in form to report Reply with quote

I have looked over the forums time and time again with different search criteria to attempt to find a solution but have not yet succeeded.
I'm sure there are some experts here who can shed some light on this subject.

I have successfully created a form (within BASE) to allow for filtering data from an SQL database table and display a portion of the results in a table on the form.

I have also created a report (within BASE) using the wizard that displays ALL data from the aforementioned SQL database table.

The form is using macros (in BASIC syntax) to create the filters and reloading the form.

What I need to know is the syntax necessary to access the created REPORT and populate it with the table/filter currently in the form.
Back to top
View user's profile Send private message MSN Messenger
QuazzieEvil
Super User
Super User


Joined: 17 Jan 2007
Posts: 599
Location: Houston, TX

PostPosted: Thu Nov 01, 2007 10:02 am    Post subject: Reply with quote

look at the following code (bound to a push button on form)
Code:

Sub filterReport(Event As Object)
   Dim Form As object
   Dim FormCollection As Object
   Dim FormDoc As Object
   Dim DBDoc As Object
   Dim Report As Object
   Dim Args(1)  As New com.sun.star.beans.PropertyValue
   'GlobalScope.BasicLibraries.LoadLibrary("MRILib")

   Form=Event.Source.Model.Parent
   FormCollection=Form.Parent
   FormDoc=FormCollection.Parent
   DBDoc=FormDoc.Parent

   Args(0).Name="ActiveConnection"
   Args(0).Value=Form.ActiveConnection
   Args(1).Name="OpenMode"
   Args(1).Value="open"

   
   Report=DBDoc.ReportDocuments.getByName("Report1")
   'mri report
   Report.Component.Filter="CATEGORY='Delivery'"
   'Report.Component.ApplyFilter=True
   'Report.Component.Load(Args())
   'MRI Report
   DBDoc.ReportDocuments.LoadComponentFromURL("Report1","_blank",0,Args())
End Sub


this works (successfully applies a filter. but it is saved to the report permanently. this means that you must set the desired filter everytime you open it. if you open it directly (not from code) this will be an issue. also, did not have enought time to play around with it, the report has a load function, but could not get it to do anything. so had to use the loadComponentFromURL(..) function. I an not sure yet what the report's load function is supposed to do.
Back to top
View user's profile Send private message Visit poster's website
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Thu Nov 01, 2007 10:41 am    Post subject: Reply with quote

terriffic!

thanks for the reply... I'll give it a shot.
Back to top
View user's profile Send private message MSN Messenger
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Thu Nov 01, 2007 10:53 am    Post subject: Reply with quote

using the above code (editing the names for the report) I'm receiving a runtime error:

"BASIC runtime error.
Object variable not set."

This occurs here:
Report.Component.Filter="datetime >= '2007-11-01'"

I am using this same type of filter on my form table with no problems...
ideas?
Back to top
View user's profile Send private message MSN Messenger
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Thu Nov 01, 2007 11:06 am    Post subject: Reply with quote

I'm just learning to use 'xray'; but the results of:
xray Report does not display a Filter Property under the .Component Property of the report.

I'm looking for the .Filter property in xray and cannot locate it.

When I edit the report however, I can definitely find the .Filter property within the Form Properties of the ReportSource's Data tab...

I feel like the lingo is getting a bit thick at this point... I hope I am explaining this correctly.

strange?
Back to top
View user's profile Send private message MSN Messenger
QuazzieEvil
Super User
Super User


Joined: 17 Jan 2007
Posts: 599
Location: Houston, TX

PostPosted: Thu Nov 01, 2007 2:36 pm    Post subject: Reply with quote

very strange. what version of OOo do you have?
Back to top
View user's profile Send private message Visit poster's website
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Thu Nov 01, 2007 2:41 pm    Post subject: Reply with quote

running v2.2
Back to top
View user's profile Send private message MSN Messenger
mobi-doc
OOo Enthusiast
OOo Enthusiast


Joined: 30 Jan 2007
Posts: 130
Location: Thessaloniki

PostPosted: Thu Nov 01, 2007 2:49 pm    Post subject: Reply with quote

I tried the same code.
The runtime error doesn't appear when the report is already open for editing.
Strange...
Back to top
View user's profile Send private message Send e-mail Visit poster's website
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Fri Nov 02, 2007 9:54 am    Post subject: Reply with quote

I still receive the error when the report is already open for editing.

When I comment out the line for applying the Filter the report opens; but ofcourse with the command/query already bound to the Report from when it was created (select *).

can someone, anyone explain this?
how do I apply the filter to this form properly?
Back to top
View user's profile Send private message MSN Messenger
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Mon Nov 05, 2007 9:11 am    Post subject: Reply with quote

bump
Back to top
View user's profile Send private message MSN Messenger
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Mon Nov 05, 2007 9:49 am    Post subject: Reply with quote

OK, well I think this will help.

When you use the report wizard ( in OO.o 2.2 that is all you have to work with ) and you build your report against a table then the wizard constructs a select statement and stores it in a hidden control, in the report definition.

In this case there is no such thing as a filter property to work with.

If on the other hand you base your wizard generated report on a query then the name of the query is stored in the definition.

Know this you could then build a macro that updated the query's filter property via the api before calling the report.

Does that help

Drew
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Mon Nov 05, 2007 10:59 am    Post subject: Reply with quote

YES, makes complete sense...

in doing so:
Query=DBDoc.DataSource.QueryDefinitions.getByName("Query_WWALMDB")
Query.Filter=Form.Filter
Query.ApplyFilter=1

I have created a new Query that selects ALL from the table (Query_WWALMDB)
I have also created a new Report based on the aforementioned query.
I then copy the filter from the form to the query's filter property.
then apply the filter...

when I use xray on the query object the .command property still shows the 'select *' query statement, and the filter contains the properly formatted 'datetime >= '2007...' as expected. However the report still is populating the ENTIRE table (without the filter applied).

ideas?
Back to top
View user's profile Send private message MSN Messenger
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Mon Nov 05, 2007 11:16 am    Post subject: Reply with quote

actually; I was not loading the "args" into the report.component...

I had commented this line out; because (1) I didn't understand it's importance and (2) it causes a runtime error.
I suppose loading the arguments would allow for this filter to be properly applied.

here is the code causing the error:
Report.Component.Load(Args())

here is the error msg:
BASIC runtime error.
An Exception occurred
Type: com.sun.star.frame.DoubleInitializationException
Message: .

thanks for your help thus far
Back to top
View user's profile Send private message MSN Messenger
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Mon Nov 05, 2007 11:34 am    Post subject: Reply with quote

ok, Haven't seen that error before...I'll try to reproduce it and see what I can do..

It'll be a few hours at the earliest..and most likely tomorrow before I get back..so you know.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
poovs
General User
General User


Joined: 01 Nov 2007
Posts: 23
Location: San Antonio, TX

PostPosted: Tue Nov 06, 2007 12:49 pm    Post subject: Reply with quote

bump
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
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