[Solved] Report filtered with form variable

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Report filtered with form variable

Postby Nocton » Tue Apr 01, 2014 12:37 pm

I have a text box (txtGroupName) on a form which is linked to the field GroupName. I want to run a report directly from the form showing data in the associated query that is filtered as GroupName = txtGroupName. Is it possible to pass txtGroupName directly to the query or to the report filter (as I can do in MS Access)?

Regards,
Nocton
Last edited by Nocton on Wed Apr 02, 2014 6:18 pm, edited 1 time in total.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 508
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Report filtered with form variable

Postby MTP » Tue Apr 01, 2014 4:24 pm

It is possible with a macro. This thread might be relevant to what you are wanting: Reporting Single Records
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Report filtered with form variable

Postby Nocton » Tue Apr 01, 2014 4:38 pm

Thank you MTP. Some interesting ideas, but not really what I want. I already have the Base report which works perfectly for all the records in the table, but occasionally I want to select/filter just one record based on the record shown in the current Base form. I can do it manually, via the associated query but I want to pass the filter parameter directly without the user having to intervene. As I said, I could do this easily in MS Access because in a query I can point directly to the form name and the required textbox which contains the search/filter string.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 508
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Report filtered with form variable

Postby RPG » Tue Apr 01, 2014 5:43 pm

Hello

I assume you use the Oracle reportbuilder.

Nocton wrote: Is it possible to pass txtGroupName directly to the query or to the report filter (as I can do in MS Access)?
You can not do it in that way.

When the report and the form are based on the same table and or query then you can pass the same filter maybe to the report and then print the report.

https://forum.openoffice.org/en/forum/v ... 3&p=209235

See the end of the post

Romke
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2187
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Report filtered with form variable

Postby MTP » Tue Apr 01, 2014 5:49 pm

Base is much less developed than MS Access. I think what you want is possible, but it is much more difficult to implement in Base (requiring macro coding or jumping through hoops with filter setups) compared to Access.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Report filtered with form variable

Postby Nocton » Tue Apr 01, 2014 6:00 pm

Thank you Romke for reminding me of this. In fact I already use your code to call my reports from the forms, so I'll look at the possibilities of getting the filter from the form.

Regards,
Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 508
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Report filtered with form variable

Postby Villeroy » Tue Apr 01, 2014 6:28 pm

https://forum.openoffice.org/en/forum/d ... p?id=14591 filters a subform by stored list box values. The report uses the same filter values for its record set.
You may add any type of form control where you enter the unique identifier (primary key) of the record you want to report.

Other options:
You can use a serial letter as stand-alone Writer report with mail merge fields. When you are going to print that "letter" you get the oportunity to pick a distinct record (by row selection or by parameter query) and send the result to some printer or odt file.
Calc can do the same with database data using a parameter query, Calc's filtering or lookup methods.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report filtered with form variable

Postby Nocton » Wed Apr 02, 2014 11:44 am

Thank you, Villeroy. I have looked at Filter.odb. Very interesting and without using macros. However, could you explain how query ParamFilter receives its parameters. I have not found anywhere where these parameters are set. Currently in my application I have a similar query, but the user must enter the parameter. Your solution seems to give what I want, if only I could understand it!

Regards,

Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 508
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Report filtered with form variable

Postby Villeroy » Wed Apr 02, 2014 12:49 pm

ParamSubformDataProperties.png
Data properties of a subform based on parameter query
Open the form in edit mode.
Get the form navigator (button #5 on toolbar "Form Design").
Study the properties on the Data tab of the filtering parent form "FilterForm" and its subform "DataForm".
The parent form is bound to one particular row of a filter table which has no other purpose than storing filter criteria in one particular row.
Subforms bound to parameter queries can substitute parameters from their parent form which allows a lot more sophisticated subform relations than (A.X=B.X) AND (A.Y=B.Y).
The sub-subform "CalculationForm" is bound to a multi-table query "qAggregate" which selects data records matching the criteria stored in our filter table row.
The report uses the same type of query: SELECT <many fields> FROM <various tables>, "Filter" WHERE "Filter"."FID"=1 AND <lots of other criteria>, AND <relations between data tables>
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report filtered with form variable

Postby Nocton » Wed Apr 02, 2014 6:18 pm

Thank you, Villeroy, I've got it now.
However, I have decided that the simplest way for me is to use an intermediate table (FilterReport) to hold the filtered record ID and update this from the form (Before record action event) using a small macro to implement SQL, thus.
Code: Select all   Expand viewCollapse view
Sub FilterID_update
dim oForm1,oForm2 as object
dim sText1 as string
oForm1=ThisComponent.Drawpage.Forms.getByName("MainForm")
sText1 = oForm1.getByName("fldGroupID").currentvalue
oForm2=oForm1.getByName("FilterForm")
oStatement = oForm2.ActiveConnection.createStatement() 'Create an SQL statement object
sSQL = "UPDATE ""FilterReport"" " & " SET ""FilterID"" = " & sText1
oStatement.executeUpdate( sSQL )
End Sub

Then the parameter in my report query can be replaced by the FilterID field.
Simple and easy to understand.

Regards,
Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 508
Joined: Fri Nov 05, 2010 10:27 am
Location: UK


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest