| View previous topic :: View next topic |
| Author |
Message |
Sveinir
General User
Joined: 14 Mar 2010
Posts: 20
Location: Denmark
|
 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.
[code]
frmForm2= ThisDatabaseDocument.FormDocuments.getByName("strForm2").open
oDrawPage= frmForm2.DrawPage.getByIndex(0).control.parent
oDrawPage.Filter= "booktitle LIKE '*p*'"
oDrawPage.ApplyFilter= True
[/code]
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?
Sveinir
_________________
Sveinir |
|
| Back to top |
|
 |
Sveinir
General User
Joined: 14 Mar 2010
Posts: 20
Location: Denmark
|
| 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:
| Code: |
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
' Parameters:
' 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
oReport= Nothing
' Does the anchor data source exist?
oTables= ThisDatabaseDocument.DataSource.Tables
oQuerydefs= ThisDatabaseDocument.DataSource.QueryDefinitions
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"
exit function
end if
' Find the in-between query
if not oQuerydefs.hasByName(strQueryWorkName) then
msgbox strErrMsgHdr & "The in-between query definition '" _
& strQueryWorkName & "' does not exist"
exit function
end if
oQuerydef= oQuerydefs.getByName(strQueryWorkName)
' 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
end if
if strSorting<>"" then
SQLstatement= SQLstatement & " ORDER BY " & strSorting
end if
' Update the query with the SQL statement
oQuerydef.Command= SQLstatement
' Open the report
if not ThisDatabaseDocument.ReportDocuments.hasByName(strReportName) then
msgbox strErrMsgHdr & "The report definition '" & strReportName & _
"' does not exist"
exit function
end if
oReport= ThisDatabaseDocument.ReportDocuments.getByName(strReportName).open
if IsNull(oReport) then
msgbox strErrMsgHdr & "The report '" & strReportName & "' was not opened" _
& chr(10) & " SQL statement: " & SQLstatement
else
' Optionally add a title
if not IsMissing(strTitle) then
oReport.title= strTitle
end if
end if
' Return the report object (can be used e.g. for adding a title to the report window)
openIBQReport= oReport
end function ' openIBQReport
|
An example of a testing macro:
| Code: |
Sub viewReport_StandAlone
' Testing call of function openIBQReport.
' No input from a form, but activated from a form button
' Examples
dim filter as string ' filter string
dim sorting as string ' sort order string
dim oReport as Object ' report object
' Books published later then 1900
filter= "year_published>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
'filter= ""
sorting= "author, booktitle"
'sorting= "author DESC, booktitle"
'sorting= ""
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.
| Code: |
Sub viewReport
' Testing call of function openIBQReport.
' Input from a form, but activated from a form button
' Examples
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
filter= ""
if strFilterColumn<>"" and strFilterSearchString<>"" then
filter= "LCASE(" & strFilterColumn & _
") LIKE '%" & lcase(strFilterSearchString) & "%'"
end if
oReport= openIBQReport(filter, strSortOrder, "tblBooks","qryBooksWork","rptBooks",filter & " ")
End Sub ' viewReport
|
DRAWBACKS
- 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.
ADVANTAGES
- It works.
WANTED
- 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.
QUESTIONS
- 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?
_________________
Sveinir
Last edited by Sveinir on Wed Mar 24, 2010 10:19 am; edited 1 time in total |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
|
|
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
|
