Reporting with dBASE Plus:
A Tutorial
Michael Nuwer
October 28, 2002
|
Reporting with dBASE Plus:
|
|
Using a Report Viewer |
The goals for this Session are:
The ReportViewer is a form control that lets you preview a report on a form. In the dB2K/dBASE application tutorial, we used the Preview.wfm form from the dUFLP to view the reports. This is a valuable tool and we will discuss it later in this Session. But it's possible to add a report preview to any form.
In this Session we will explore the ReportViewer object. We will begin by creating our own form with a reportViewer object. The form will also contain some controls to filter the data that is printed in the report. Creating this form is a useful way to understand how the ReportViewer works (and how preview.wfm works since it uses this object).
After creating our own preview form, we will explore the Preview.wfm form from the dUFLP in greater depth. Our main focus is to explore how to integrate the Preview.wfm form with forms that solicit user input. It is very common for a reporting system to provide a mechanism so that users can enter some parameters (like a date range or a sort order) that will modify the report's output. This is a topic that was beyond the dB2K/dBASE Tutorial but will be explored later in this document.
Begin by opening the form named ReportViewer.wfm in the form designer. This form contains two container objects with the "Anchor" property of container1 set to "2 - Top" and of container2 set to "1 - Bottom." The form also contains a ReportViewer object with the anchor property set to "6 - Container". Using the anchor property is useful for automatically resize the objects when the form is resized.
You will notice that container1 (the one at the top of the form) has four pushbuttons and container2 (the one at the bottom of the form) has two entryfields, a combobox, and a pushbutton. In this form, the pushbuttons in container1 will be used to open a report, move from page to page, and print the report. The controls in container2 will be used to set a filter in the report.
Lets add some code to this form and view a report.
In this form, we will use the first pushbutton to open the report. You will need to add an onClick event handler for this pushbutton and enter the following line of code:
function PBOPEN_onClick this.form.ReportViewer1.filename = "ViewerSample.rep" return
The "filename" property tells the ReportViewer object which report file to open. The report will become visible as soon as the filename property is assigned. The report refered to in the above code ("ViewerSample.rep" ) is included in the working folder, which means you can run this form and view the report.
After testing this pushbutton, return to the form designer.
The ReportViewer class stores a live reference to the report object in its "ref" property. This is very useful. It means that the "ref" property can be used to reference the actual report from within the form. Thus, you can read or set any of the report's properties by using this "ref" property.
To see how it works, let's use the "ref" property to increment and decrement the report's page properties. This will let us show different pages in the ReportViewer object. Add an onClick event handler to the third pushbutton (the "Next Page" button) and enter the following code:
function PBNEXTPAGE_onClick
local oViewer
oViewer = form.reportViewer1
if not oViewer.ref.isLastPage()
oViewer.ref.startPage++ // increment
oViewer.ref.render()
endif
return
This code (1) creates a shortcut to the reportViewer1 object (oViewer); (2) checks that the report is not already at the last page; then (3) increments the startPage property in the report object. The next line is essential. After changing a report property, the report must be re-rendered for the change to be visible in the ReportViewer1 object.
The "Previous Page" button is very similar to the "Next Page" button. Add an onClick event handler for the second pushbutton control and enter the following code.
function PBPREVPAGE_onClick
local oViewer
oViewer = form.reportViewer1
if not oViewer.ref.reportPage = 1
oViewer.ref.startPage-- // decrement
oViewer.ref.render()
endif
return
Again you can save the form and give it a test. Click the "Open Report" button to render the report, and then click the "Next Page" and "Previous Page" buttons to navigate through the report.
The last pushbutton in the container1 object is used to print the report. Switch back to the form designer and add an onClick event handler for the "Print" pushbutton. Then enter the following code:
function PBPRINT_onClick local oViewer, bPrinter oViewer = form.reportViewer1 bPrinter = oViewer.ref.printer.ChoosePrinter()
if bPrinter
oViewer.ref.startPage = 1
oViewer.ref.output = 1 // printer
oViewer.ref.render()
oViewer.ref.output = 3 // default
endif
return
This code starts by calling the ChoosePrinter() method of the reports printer object. This gives the user the option of choosing an installed printer. If the user selects a printer, then the code (1) sets the startPage to 1; (2) sets the output property to the printer; and (3) renders the report. After the report is rendered, the output is set back to the default which, in this case, is the ReportViewer. One might, however, close the form after the report prints.
There are many other features that we could add to this form (see Preview.wfm for examples). But our form should give you a good idea about how the ReportViewer interacts with the Report. So lets move on, and discuss the filters.
It is common for a reporting system to give users the ability to select parameters that customize a report. These often include values that are used to filter the data and/or options to sort the data. We will use container2 in the ReportViewer form to get filter values from the user and set them in the report.
The standard method of "passing" parameters from a ReportViewer object to a report object is to use the ReportViewer's "params" property. This is an associative array; it gets loaded in the form and is used in the report.
We have seen that the ReportViewer class stores a reference to the report being viewed. This reference is a two-way link. The report class contains a "reportViewer" property, which stores a reference back to the form. This report property provides access to the properties and methods contained in the form.
Lets see how it works. In our ReportViewer.wfm form, add an onClick event handler to the "Filter" pushbutton with the following code:
function PBFILTER_onClick
local oViewer, cFilter
cFilter = ""
if not empty(this.parent.entryfield1.value)
// add the start date to the filter string
cFilter += "Born => '" + dtoc(this.parent.entryfield1.value) + "'"
// add the end date to the filter unless
// it is empty. Then use the start date
// as the end date.
if not empty(this.parent.entryfield2.value)
cValue = dtoc(this.parent.entryfield2.value)
else
cValue = dtoc(this.parent.entryfield1.value)
endif
cFilter += " AND Born =< '" + cValue + "'"
// Assign the filter string to an
// array element called "dates"
form.reportViewer1.params[ "dates" ] = cFilter
endif
// If a sex is selected, create a second filter
// filter string and a second array element
if not empty(this.parent.combobox1.value)
cFilter = "Sex = '" + this.parent.combobox1.value + "'"
form.reportViewer1.params[ "sex" ] = cFilter
endif
// Open the report, if none is open, otherwise
// re-execute the report.
If empty(form.reportViewer1.filename)
form.reportViewer1.filename = "ViewerSample.rep"
else
form.reportViewer1.reExecute()
endif
return
When this code runs, the ReportViewer object will contain an associative array with as many as two elements. These elements will look something like the following:
dates = "Born => '01/01/1980' AND Born =< '01/01/1981'" sex = "Sex = 'F'"
Each report is different, so you will need to construct a filter strings that fits your needs. This code is only an example of one possible option. And makes sense for the report we're working with.
Loading the reportViewer's params array is one side of the coin. The other side is to modify the report so that it uses the information in that array. There are a few different ways to do this. We will use the report's bootstrap code for this example and later, when we look at the Preview.wfm form, we will use the report's render event.
Open the ViewerSample.rep report in the Source Code Editor. We need to add code at the very top of the file. Not inside the class ... endclass statement, but above the following lines:
** END HEADER -- do not remove this line // // Generated on 09/23/2002 //
The original "bootstrap" code contains three lines:
local r r = new ViewerSampleReport() r.render()
In our report we want to substitute a more robust procedure between instantiating and rendering the report. We need to evaluate the params array and set the filter. Here is the code that you can add at the very top of this report file:
if argcount() >= 1
local r, cFilter, bFirst, cKey
r = new ViewerSampleReport( )
bFirst = true
// Get the first key in the assocArray
cKey = r.ReportViewer.params.firstKey
// Loop through the assocArray to
// construct a filter string.
for i = 1 to r.ReportViewer.params.count()
if bFirst
cFilter = r.ReportViewer.params[cKey]
bFirst = false
else
cFilter += " AND " + r.ReportViewer.params[cKey]
endif
cKey = r.ReportViewer.params.nextKey(cKey)
next
// Set the report filter
r.streamSource1.rowset.filter := cFilter
// render the report
r.render( )
return
endif
This code (1) creates a new instance of the report; (2) loops through the associative array and constructs a single filter string; (3) sets the filter in the report; and then (4) renders the report.
Note: if your associative array contains elements for a filter and elements for other parameters (like a sort order or index key), then you may want to use some additional characters in the names of the array elements. For example, the filter elements might all begin with "Filter_" ("Filter_dates" and "Filter_sex"). Then the "for" loop in the above code should test for these characters and skip those that don't matches. Don't forget that the name elements in an associative array are case sensitive.
Now when we run the ReportViewer.wfm form we can enter some filter conditions and then our code will (1) add these conditions to the params array of the ReportViewer object; (2) open the report file which will run the custom bootstrap code; (3) set the filter in the report.
The Preview.wfm form, included in the dUFLP, is a robust ReportViewer. It uses some of the principles that we explored above, but it offers a number of additional features. This form was used to display all of the reports in the dB2K/dBASE application Tutorial. When we attached the reports to the application menu in Phase VII, we used code similar to the following:
set procedure to inventory.rep additive fPreview = new PreviewForm() fPreview.bModal = true fPreview.viewer.fileName := "inventory.rep" fPreview.Open()
This code works just fine if you simply need to open a report, but how can we use some parameters that control the report's output?
But wait. You probably recall that there are two reports in the Tutorial where parameters are used. The Invoice report sets a filter for the selected customer ID and the Statements report sets a filter for a date range. In both cases we used the _app object (the OOP equivalent of a public variable) to "pass" the filter conditions. The form set the value of the _app object and then the report read that value.
There is one problem with the approach we used in the Tutorial application. As Gary White notes, because the _app.property object is globally visible, a second instance of the form will overwrite the other's value. This is not a good thing. The better approach is to encapsulate the value as a property of the form.
When you encapsulate a value within the form to which it pertains, multiple instances of the form will not overwrite each other's data. _app.properties should be used only as a last resort, when truly global visibility is needed.
The next section will explore a better (albeit more complex) way to "pass" a filter conditions from a form to a report.
Lets begin with a form that gets a date range from the user. This form is going to call the Preview.wfm form and the Preview.wfm form is going to open a report.
Open GetDates2.wfm in the form designer. This form is a modified version of the GetDates.wfm form found in the dUFLP. The main difference between this form and the one in the dUFLP is that this form encapsulates the start date and the stop date properties.
The only thing that you need to do to integrate this form into a reporting system is to add some code to the pushbutton event handler. We need to (1) create an instance of the Preview.wfm form; (2) create an associative array and add the date range as a filter string; (3) assign this associative array to the "params" property of the Preview form (This is built into the preview form. If you pass this array, the form will assign it to the ReportViewer object.); (4) assign a report file; and (5) open the form.
Function KMYESBUTTON1_onClick
local dStart, dStop
set procedure to preview.wfm additive
fPreview = new PreviewForm()
if not form.kmCustCheckboxIgnoreDates.value
aParams = new AssocArray()
dStart = form.kmCustSpinboxStart.value
dStop = form.kmCustSpinboxStop.value
cFilter = "Born => '" + dtoc(dStart) + "'" ;
+ " and Born =< '" + dtoc(dStop) + "'"
aParams[ "Filter" ] = cFilter
fPreview.params = aParams // parameter associative array
endif
fPreview.viewer.fileName := "PreviewerSample.rep"
fPreview.Open()
form.close()
Notice that the params property is attached to the Preview form, not to the Viewer object (in Preview.wfm the ReportViewer object is named "Viewer"). The Preview form contains some complicated code that manages the params array. The code ensures that, if the array exists, it is assigned to the Viewer object and available to the report.
As users of the Preview form we do not need to know all the details of how this done. That's the beauty of object oriented programming. Objects, like the Preview form, encapsulate their data and procedures. This means that the Preview form can be used without studying all the details that make it work. Encapsulated objects are like a "black box," once created, they work consistently to do their job.
The report. When you use the Preview.wfm form with a parameter array, that array is available to the report. This means that the report must check for the array and use the information it contains. The Preview form is designed to handle this by adding an overriden render event to the report. The render event is used so that the report can find the parameter array before creating the report.
Using an overridden render event handler is a slightly different approach than the one we used in our ReportViewer.wfm form. But, the dUFLP makes all this very easy. All of the reports that we have used in this tutorial are derived from the custom report found in duflp.crp. This custom class already includes an overridden render() event handler that looks for your parameter array.
This custom report class cannot, however, know what filters (or indexes) you want to use, so it includes a call to a method named "setParams()." If this method exists in your report, the custom report class will call it. The setParams method is where you can add code to set the filters or otherwise use the information in the parameter array.
At the time of this writing the setParams() method is not included in the dUFLP. I proposed adding this to the custom reports, and Ken Mayer, the dUFLP librarian, has indicated he will likely make this addition to the next version of the library. The call to setParams() is contained in the duflp.crp custom report included with this tutorial.
We are going to use PreviewSample.rep for the report in this example. It is a simple list of dogs with, among other things, their birth dates. We will use this field for the filter. The GetDates2.wfm form that was modified above, adds a filter sting to its parameter array. That element looks like this:
Filter = "Born => '01/01/1980' AND Born =< '01/01/1981'"
We want to use this array element our report. Open the PreviewSample.rep report in the Source Code Editor and add a standalone method. Scroll down to line 130, which is at the end of the class constructor. Click the "Method" menu item, and then click "New Method."
Rename the method "setParams" (the custom report class is expecting this name, so it's required). Then add the following code:
function setParams
if this.params.isKey( "Filter" )
// assign the filter property of the
// rowset used in the report
this.form.streamsource1.rowset.filter := ;
this.params[ "Filter" ]
endif
return
Remember that the key name of an associative array is case sensitive. We used "Filter" as the key name in the GetDates2.wfm form, so we must use "Filter" (not "filter") here too.
"SetParams()" is now a method of the report and the custom report class will automatically run it when the render() event fires. It really is that simple.
In your own reports, if they are derived from duflp.crp, you can can add a setParams and insert any code you need in order to set report parameters.
You can run this example by running GetDates2.wfm and entering a range of dates (try dates from the 1980s). The OK button loads a params array, assigns it to the Preview form and opens that form. When the Preview form opens, it assigns the params array to the reportViewer object and to the report object. The report's render event then uses this information to control the reports output.
The programmer needs to code the onClick event handler in GetDate2.wfm form and the setParams method in the report.
The Thirteenth issue of the dBASE Developers Bulletin contains an article by Steven Hawkins that covers passing parameters with the Preview.wfm form. Steven's approach is slightly different than the approach covered here, but the principles are substantially the same. You may find this article useful: Example of Preview.wfm and Report Parameters
The Legal Stuff: This document is part of the dBASE Reporting Tutorial
created by Michael Nuwer. This material is copyright © 2002, by Michael
Nuwer. dBASE, dBASE Plus, dBASE SE, and dB2K are copyrighted, trademarked, etc.,
by dBASE, Inc., the BDE (Borland Database Engine) and BDE Administrator are
copyrighted, trademarked and all that by Borland, International. This document
may not be posted elsewhere without the explicit permission of the author, who
retains all rights to the document.