Joined: 14 Mar 2010
|Posted: Sun Mar 14, 2010 5:52 am Post subject: How to open a report with a changeable filter from a macro?
|I have [b]form[/b] with a [b]button[/b] that can [b]open a report[/b]. But the report always shows all records from the underlying table. I want to open the report with restrictions that I can choose from time to time. The filter may be a string with a logical expression or it may be an SQL statement. It is not difficult to construct the filter in the calling form, it is difficult to make the report understand the filter, which is strange, for it is seldomly useful to have a report containing all records, so there ought to be an easy way to do this.
Filtereing a form with a table contol is relatively easy, e.g.
oDrawPage.Filter= "booktitle LIKE '*p*'"
although the filter must be placed at some exotic corner of the drawpage and not directly on the object frmForm2. I am happy to see that from OOo 3.1 on, forms and reports can be opened in a much simpler way than before, (no more fussing with connections, conatiners and properties, hooray!) .
But how do I filter a report?
Joined: 14 Mar 2010
|Posted: Wed Mar 24, 2010 9:41 am Post subject: [Solved (sort of)] Opening a report with changeable filter
|Description of a workaround general solution to open a report with changeable filter
Well, I'll answer it myself, then. A few times I have experienced that asking others for help inspired me to find a solution myself. My solution is based on many fragments from the OOo Forum, and the indispensable guessing tool Xray.
I use a workaround solution with an in-between query. It has drawbacks, but it works.The essential steps are these:
1) Anchor data source :The data source that provides data can be a table or a query. I call this the "anchor data source".
2) In-between query: Make a simple in-between query just mentioning all columns in the anchor data source. In SQL terminology "SELECT * FROM anchor_data_source_name>". This query will be changed by the below mentioned function openIBQReport (in-between query report)
3) Report: Make the report based on the in-between query
4) Controlling form: Make a form in which filtering and sorting criterions can be specified and with a button that activates a macro that in turn calls a macro
5) Macro: A macro to call the function openIBQReport, that
a. Makes an SQL statement to do filtering and sorting
b. Updates the underlying in-between query with the SQL statement
c. Opens the report
The function does not explicitly check for correct syntax of the filtering and sorting.
The function openIBQReport is:
function openIBQReport(strFilter as string, strSorting as string, _
strAnchorDataSourceName as string, _
strQueryWorkName as string, strReportName as string, _
optional strTitle as string) as Object
' Open a report based on filtering a query made from another query or table
' No explicit checking for correct syntax of the filter and order specification
' For OOo Base ver. 3.2
' Svend Larsen (alias Sveinir), 22/3-10
' strFilter a string containing a filter expression (an SQL WHERE clause
' without the keyword WHERE)
' If no filtering wanted, use a zero-length string
' strSorting a string containing sorting columns (and eventulally ASC or DESC)
' (an SQL ORDER BY clause without the keywords OREDER BY)
' If no sorting wanted, use a zero-length string
' strAnchorDataSourceName the name of a table or query that provides the data
' strQueryWorkName the name of the in-between query that shall be equipped with
' filter (a WHERE or HAVING clause) and /or sorting columns (ORDER BY)
' strReportName the name of the report to open
' strTitle optional text to show in the report window frame
dim oTables as Object ' Tables collection
dim oQuerydefs as Object ' Query definition collection
dim oQuerydef as Object ' The in-between query definition
dim oReport as Object ' The report to open
dim SQLstatement as string ' a string to hold the resulting SQL statement
' to put into the in-between query
dim strErrMsgHdr as string ' Error message header
strErrMsgHdr= "ERROR in opening the report '" & strReportName & "':" & chr(10)
' Initiating the report object
' Does the anchor data source exist?
if not (oTables.hasByName(strAnchorDataSourceName) _
or oQuerydefs.hasByName(strAnchorDataSourceName)) then
msgbox strErrMsgHdr & "The anchor data source '" & strAnchorDataSourceName _
& "' does not exist, neither as a table nor a query"
' Find the in-between query
if not oQuerydefs.hasByName(strQueryWorkName) then
msgbox strErrMsgHdr & "The in-between query definition '" _
& strQueryWorkName & "' does not exist"
' Put together an SQL statement with the purpose to add filter and sorting
' to the in-between query
SQLstatement= "SELECT * FROM " & strAnchorDataSourceName
if strFilter<>"" then
SQLstatement= SQLstatement & " WHERE " & strFilter
if strSorting<>"" then
SQLstatement= SQLstatement & " ORDER BY " & strSorting
' Update the query with the SQL statement
' Open the report
if not ThisDatabaseDocument.ReportDocuments.hasByName(strReportName) then
msgbox strErrMsgHdr & "The report definition '" & strReportName & _
"' does not exist"
if IsNull(oReport) then
msgbox strErrMsgHdr & "The report '" & strReportName & "' was not opened" _
& chr(10) & " SQL statement: " & SQLstatement
' Optionally add a title
if not IsMissing(strTitle) then
' Return the report object (can be used e.g. for adding a title to the report window)
end function ' openIBQReport
An example of a testing macro:
' Testing call of function openIBQReport.
' No input from a form, but activated from a form button
dim filter as string ' filter string
dim sorting as string ' sort order string
dim oReport as Object ' report object
' Books published later then 1900
' Books with author names beginning with A
'filter= "author like 'A*'"
' Books with author names begynning with A or a
'filter= "LCASE(author) LIKE 'a*'"
' Books with author names beginning with D through M (or d through m)
'filter= "LCASE(LEFT(author,1)) BETWEEN 'd' and 'm'"
' Book with titles containing the word 'mermaid'
'filter= "LCASE(booktitle) LIKE '*mermaid*'"
' No filter
sorting= "author, booktitle"
'sorting= "author DESC, booktitle"
oReport= openIBQReport(filter, sorting, "tblBooks","qryBooksWork","rptBooks","Book list ")
'oReport.title= "Books with titles containing the word 'mermaid' "
End Sub ' viewReport_StandAlone
An example where the criteria for filtering and sorting are typed in into fields of a form. The filter is fixed to searching for a substring.
' Testing call of function openIBQReport.
' Input from a form, but activated from a form button
dim filter as string
dim sorting as string
dim oReport as Object
dim oFormDrawPage as Object ' The calling form
dim strFilterColumn as string
dim strFilterSearchString as string
dim strSortOrder as string
' Read from the form
oFormDrawPage= ThisComponent.DrawPage.Forms.getByIndex(0) ' form drawpage
strFilterColumn= oFormDrawPage.fldFilterColumn.text ' filter column
strFilterSearchString= oFormDrawPage.fldSearchText.text ' search string
strSortOrder= oFormDrawPage.fldSortOrder.text ' sorting
' Elaborate the filter to search for a substring
if strFilterColumn<>"" and strFilterSearchString<>"" then
filter= "LCASE(" & strFilterColumn & _
") LIKE '%" & lcase(strFilterSearchString) & "%'"
oReport= openIBQReport(filter, strSortOrder, "tblBooks","qryBooksWork","rptBooks",filter & " ")
End Sub ' viewReport
- It is clumsy to make an in-between stored query.
- When I close the database I am asked whether I want to save it. This is because the in-between query has been changed.
- It works.
- I cannot in a macro read the data source of a report, neither set it to a desired value. It would be a better workaround solution to replace the report's data source with different SQL statements than to make a stored query.
- In which library should I place the function openSQLReport in order to be able to run it from other database?
- I would like to make a field in the report, in which to place som representaion of the filter. I can find no way to do this (Xray of the drawpage reveals no report fields). How can it be done?
Last edited by Sveinir on Wed Mar 24, 2010 10:19 am; edited 1 time in total
Joined: 04 Oct 2004
||All times are GMT - 8 Hours
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