Filter Reports

[dstockman12]

I am using the Report Builder extension to create reports. I have created a basic report structure. I need to vary the output based on changing variables. Presently, the data is pulled using a static query. I thought maybe I could filter the report, just like I filter a form after it is created. This would seem to be the easiest method to select only the data I need for a specific report. At least in the visual Report Builder window, there is a Filter selection. I cannot find this from code. I fear I am looking in the wrong place. I have been using Mri to search the created report (see code below). The created report is a Writer document and there is no “Filter” I can find in the created report. I assume I am looking in the wrong place and need to look at the Report before it generates the Writer document (but maybe I am wrong). Can anyone show me how to get to the Filter structure, so I can set it? BTW, I am a novice so explicit instructions/code provides me with the greatest chance for success.

If setting the Filter structure dynamically is not possible, then I will probably use SQL commands to dynamically create a table based on the changing query parameters and create the report off of this table. This approach is a bit more work and problems may arise when I go to a multi-user environment. Then I would have to ensure multiple users are not attempting to create reports at the same time. Therefore, setting the Filter structure seems the wisest path to a workable solution.

Some specifics if needed
Application: Printing medication prescriptions
Query pulls patient demographics and specifics about the prescription/s

Main variables that need to change dynamically
Medical record number: MRN
New Rx that needs to be output: RxNow
Rx Output: printer, fax, electronic, etc.

Once the user chooses the Rx's that need to be output, they select a button. This calls the code. I need to filter on MRN, RxNow and then will need to run the code one or more times based on where the output is going. I would run one report and send all current Rxs that are supposed to be printed to the selected printer, then run again and select the Rxs going out by Fax, etc.

I think I can create the code to at least run the Report and then print. I am stuck at the Filter part.

Any and all help is greatly appreciated!

Code to Open Report

Code: Select all

'Function OpenReport(RptName As String) As Object
Function OpenReport() As Object
Dim Context As Object
Dim DBDoc As Object
Dim Conn As Object
Dim DB As Object
Dim Report As Object
Dim Args(1) As New com.sun.star.beans.PropertyValue

Dim RptName As String  ' *** temporary variable until call function after testing done
RptName = "RxPrintRPT2"

Globalscope.BasicLibraries.LoadLibrary( "MRILib" )

Context = CreateUnoService ("com.sun.star.sdb.DatabaseContext")
DB = Context.getByName("DS_Pts090714")
Conn = DB.getConnection("","")
DBDoc = DB.DatabaseDocument

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

OpenReport = DBDoc.ReportDocuments.LoadComponentFromURL(RptName, "_blank", 0, Args())

Mri DBDoc
msgbox "DBDoc"

Mri OpenReport
msgbox "OpenReport"

End Function

[Villeroy]

Do you want to filter a record set on a form and then dump the resulting set into a report? http://user.services.openoffice.org/en/ ... hp?id=6442

[dstockman12]

Villeroy:

The prescriber will do the initial selection of medications to output from a form (not the same as the Rx output, very different appearance). However, when prescribing medications, it is helpful to view all the medications the patient is taking. Then various medications need to be sent to various outputs and some of the medicines will not be acted upon. In the state I live in, some medication Rxs can be faxed or sent electronically, while others (controlled substances) can only be printed on tamper resistant paper and therefore must be sent to a printer. The prescriber may have to print out existing medications (renew) as well as enter new medications and send to various outputs. I hope this makes sense. I will attempt to attach or insert a graphic of the form the prescriber will use.

At first I thought about creating a form and then printing the form for each record generated by the query. It then seemed to me, this is the purpose of the Report Builder. The query pulls data from various tables and then is placed in specific locations on the report.

[img]

[/img]

I hope I am making sense.

Doug

[Villeroy]

However, when prescribing medications, it is helpful to view all the medications the patient is taking.

Like the existing medications in your screen shot?
Bind a list box to some field in a dummy table (analog to my combo box bound to FILTER.CHARS where ROW=0).
Make the visible list box entry like "Smith,Henry,1949" if the combination of name and birth year is unique. So you can easily pick a person typing into a list box.
However your subform is bound to the person-ID, you can bind a report on the same set of criteria which is stored in a dummy table.
I use to solve all kinds of complex stuff with many subforms, default values, filters with linked forms and reports without writing a single line of macro code.

[dstockman12]

Villeroy:

Thanks for replying! I fear either this solution is much simpler than I can see or I am not explaining myself well. The Patient identifier, MRN, is already set/known. I have a separate form and Sub that does that. That sub allows the user to find a specific patient based on one or more parameters (MRN, last name, first name, adddress, etc.). In addition to that, I need to create reports based on output type - print, fax, electronic, etc. I decided to create a sub and function to handle all this. The code below does what I want. I still have the issue that may arise as I go to a multi-user environment because I am writing to one table. Although I have solved my problem, I did not do it by filtering a report. Therefore, I do not believe I can mark this topic as solved.

Doug

Code: Select all

 Sub CreateRxTmp(Event As Object)
Dim str As String
Dim str2 As String
Dim Stmt as Object
Dim strSQL(80) as String
Dim strSQL2 As String
Dim i As Integer   ' *** used to count number of Rxs
Dim j As Integer   ' Used to create Rxs up to max of i
Dim k As Integer  ' *** used to count 'Print' or 'Fax'
Dim Conn
Dim Result
Dim Context As Object
Dim DB As Object
Dim Form As Object
Dim RxOutput As String

Globalscope.BasicLibraries.LoadLibrary( "MRILib" )

If(LEN(myindex)) < 5 Then  ' *** No valid MRN chosen, exit Sub
	Exit Sub
EndIf

k = 0

Form = Event.Source.Model.Parent ' *** will need this later to reload the form after change RxNow to unchecked ***

Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB = Context.getByName("DS_Pts090714")
Conn = DB.getConnection("","")
Stmt = Conn.createStatement()


' **** Get patient demographics ****
strSQL2 = "SELECT ""Patient Lname"", ""Patient Fname"", ""DOB2"", ""ADDR 1"", ""ADDR 2"", ""CITY"", ""State"", ""ZIP"", ""HOME PHONE"", ""MRN""  FROM ""DS_Pts"" AS ""DS_Pts"" WHERE ""MRN"" = '" & myindex & "'"
Result = Stmt.executeQuery(strSQL2)
While Result.next()
	str = Result.getString(1) & ",  " & Result.getString(2) & "   **  DOB: " & DateConvUSstr(Result.getString(3))  & chr(13) & Result.getString(4) & " ,  " & Result.getString(5) & chr(13) & Result.getString(6) & ",  " & Result.getString(7) & "  " & Result.getString(8) & chr(13) & "Phone: " & Result.getString(9) & " **   MRN: " & Result.getString(10)
Wend

RxOutput = "Print"
For k = 0 to 1
	If (k = 1) Then  ' *** first time through loop, Rxs go to print.  Next time go to fax
		RxOutput = "Fax"
	EndIf
	i = 0
	j = 0

	' ***** clear tmp table of previous entries
	strSQL2 = "DELETE FROM TMPOUTPUTRX"
	Stmt.executeUpdate(strSQL2)

	' **** create Medication text ******
	strSQL2 = "SELECT ""MedName"", ""MedSz"", ""MedSig"", ""DispenseNum"", ""DispenseTxt"", ""RefillNum"", ""RefillTxt"", ""MDD"", ""DateRx"", ""LinkDx"" FROM ""MedsTBL"" AS ""MedsTBL"" WHERE ""MRN"" = '" & myindex & "' AND ""RxNow"" = 'TRUE' AND ""RxOutput"" = '" & RxOutput & "'"
	Result = Stmt.executeQuery(strSQL2)
	While Result.next()
		str2 = "* " & Result.getString(1) & "  " & Result.getString(2) & chr(13) & "* Sig: " & Result.getString(3) & chr(13) & "* Dispense: " & Result.getString(4) & "  (" & Result.getString(5) & ")  ** Refill #: " & Result.getString(6) & " (" & Result.getString(7) & ")   **   MDD: " & Result.getString(8) & chr(13) & "* DateRx: " & DateConvUSstr(Result.getString(9)) & "  **     Diagnosis: " & Result.getString(10)
		strSQL(i) = "INSERT INTO TMPOUTPUTRX (PTINFO, MEDINFO) VALUES ('" & str & "', '" & str2 & "')"
		i = i + 1
	Wend

	If i > 0 Then   ' **** This means we have an Rx to send to either printer or fax ****
		' **** write both Pt demographics and Rx information to TMPOUTPUTRX table*****
		For j = 0 to (i-1)
			Stmt.executeUpdate(strSQL(j))
			strSQL(j) = ""
		Next j

		' **** Open report, will then need to figure out how to send to printer and to fax ****
		OpenReport("RxPrintRPT3") ' **** need to do error checking on this at some point ***

		' **** Next need to set RxNow to No, once we print/fax out the Rxs ****
		strSQL2 = "UPDATE ""MedsTBL"" SET ""RxNow"" = 'FALSE' WHERE MRN =  '" & myindex & "' AND ""RxNow"" = 'TRUE' AND ""RxOutput"" = '" & RxOutput & "'"
		Stmt.executeUpdate(strSQL2)
	EndIf
Next k

Conn.close()
Form.reload  ' *** reload MedManageFRM after with update RxNow checkboxs to FALSE ***
End Sub


Function OpenReport(RptName As String) As Object
'Function OpenReport() As Object
Dim Context As Object
Dim DBDoc As Object
Dim Conn As Object
Dim DB As Object
Dim Report As Object
Dim Args(1) As New com.sun.star.beans.PropertyValue

Context = CreateUnoService ("com.sun.star.sdb.DatabaseContext")
DB = Context.getByName("DS_Pts090714")
Conn = DB.getConnection("","")
DBDoc = DB.DatabaseDocument

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

OpenReport = DBDoc.ReportDocuments.LoadComponentFromURL(RptName, "_blank", 0, Args())

End Function