[Solved] Report filtered with form variable

[Nocton]

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

[MTP]

It is possible with a macro. This thread might be relevant to what you are wanting: Reporting Single Records

[Nocton]

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.

[RPG]

Hello

I assume you use the Oracle reportbuilder.

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.

viewtopic.php?f=39&t=45263&p=209235

See the end of the post

Romke

[MTP]

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.

[Nocton]

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

[Villeroy]

download/file.php?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.

[Nocton]

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

[Villeroy]

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>

[Nocton]

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

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