Reporting with dBASE Plus:
A Tutorial
Michael Nuwer
October 28, 2002
|
Reporting with dBASE Plus:
|
|
Using Temporary Tables |
The goals for this Session are:
All but one of the reports created in the dB2K/dBASE Tutorial used a dataModule to organize the reporting data. Using a dataModule has the advantage of reusability. Any methods, calculated fields, lookup fields, etc. that are created for a form can be reused in a report. In addition, a datamodule must be used if you want to use a dataModRef object where a form and a report share the same rowset. (You can find an example of this approach in the Hands-on Project that accompanies my "Getting Started with dB2K" courseware.)
The disadvantage of using a dataModule with a report is that the report designer doesn't handle Parent-Child-Grandchild relations very well. Additionally, the report designer doesn't handle interleaved data vary well, where a Parent table has two Children. For example, an accounting application might store charges in one table and payments in a separate table. To print a Customer statement we need to merger or interleaf the charges and payments by date.
An alternative to the dataModule approach is to use a single SQL query that joins all the tables. The Statements report in the dB2K/dBASE Tutorial used this type of query. We joined three tables into one rowset, sorted the rowset on three fields, set a filter on a date range and printed the report. (That report was not interleaved.) One disadvantage of an SQL query is that you loose the ability to use your dBASE indexes. In particular, those indexes that use multiple fields or functions cannot be used.
A third approach is to use a temporary table for reports. Todd Kreuter, a dBVIPS member, explained this approach in the dBASE newsgroups. I am including a rather lengthy quotation here because it seems to make the point so well.
Ready to take on reports are you? The way I see it, you have three options for creating a report using information from multiple tables: Linked Tables, SQL Joined Tables, Flat Table.
Linked tables is my least favorite method, as I never got it working to my satisfaction. I've found that inorder for this method to work, you had to use reverse links where the detail table was the parent, and doing this prevented you from sorting the report on fields not included in the detail table (like customer name). I believe that this method would be effective if each report band had its own steamsource, then you could use normal parent child linked tables and control the report order by using existing index tags.
SQL joined tables creates a flat rowset (temp table) which you can sort on fields from the various tables. I normally use this method only when creating "I need it now" reports, as its a quick and easy setup. But for applications, my goal was to give the user as many options as possible for reporting, and incorporating those user options into an SQL statement proved to be to difficult (for me at least). My only other alternative was to use my own flat tables.
Flat tables, IMO [in my opinion], provides the most flexibility. Although it requires more work on the front end, it requires much less work on the back end (in the report designer) because you control the file structure and the field data. It only gets easier once you have a procedure for creating those flat tables.
My basic approach is all my report flat tables have standard fields and report specific fields. The standard fields allow me to give many faces to a single report, as those fields are used to incorporate user options (Grouping, sorting, filtering). I start with the table which contains the information which is to be reported and traverse through the rows pulling information from other tables as needed (using findKey) and use that information for appending to my flat table. I'm not concerned about the order the data goes into the table, because once the building process is done, I create a Report Tag whose expression is based on the users grouping and sorting options. The form I present to the user has three parts, report selection, report build, and report print. The report build contains those user options to build the report and of course a progress bar to display the building progress. The report print screen is accessed after a successful build and contains printing options (include group headers/footers, start new page, filtering, etc.) and the preview and print buttons.
We are not going to develop quite so elaborate of a printing system in the following pages. But we are going to explore the tools and procedures that can be used if you want to create an elaborate system. The foundation for creating flat tables is a custom query class named TMPQuery.cc. Todd created this custom query class to simplify the procedures for using temporary tables and shared it with the dBASE community. The remainder of this Session explores the use of this tool for creating reports.
The reporting system that we are going to develop is very similar to the one we developed in the last Session. It will begin with a GetDates form. When the user clicks the OK button, this form will call the Preview.wfm form and the report will render in the reportViewer.
So what's the difference? The GetDates form will create a temorary table based on user-defined parameters. When it calls the Preview form, the name of the temporary table is forwarded to the report in the params array.
To get started, open GetDates3.wfm in the form designer. Because we are going to create a temporary table in this form, we need to add two query objects: Dogs1 and Litters1. Records from these queries will be accessed after we create a temporary table.
Next we are going to need an onClick event handler for the OK pushbutton. The code for this event handler is going to be rather lengthy, so we'll break it up for discussion. Start by adding the following lines:
function KMYESBUTTON1_onClick local dStart, dStop set procedure to TmpQuery.cc additive form.qTmp = new TMPQuery() form.qTmp.useTempPath = false form.qTmp.dropOnClose = false form.qTmp.canOpen = class::QTMP_CANOPEN form.qTmp.active = true
These lines will create a new instance of TMPQuery, set a few properties and open the query. "useTempPath" and "dropOnClose" are custom properties of the TMPQuery class. The first one determines whether the temporary table should be created in the Windows temporary folder (true) or in the working folder (false). We have this set to use the working folder. The second property determines whether the temporary table will be deleted when the query closes. This property is set to false because we are in development mode and it useful to have copies of the tables. We will change this to true when the form is put into production mode.
Before continuing the procedure code we need to create the canOpen event handler. In this event handler we will use methods from TMPQuery.cc to create our table. You need to create a new method in the GetDates.wfm form with the name "QTmp_canOpen." Then add the following code:
function qTmp_canOpen
with (this.fields)
// fields from dogs.dbf
add("reg_no C16")
add("born D")
add("name C60")
// fields from litters.dbf
add("sire C16")
add("femi_live N3,0")
add("femi_dead N2,0")
add("masc_live N3,0")
add("masc_dead N2,0")
endwith
this.createTable()
return TMPQuery::canOpen()
This code is executed when the query is activated. It declares the fields that
will be added to the temporary table and creates the table. In the last line
(return TMPQuery::canOpen()) the TMPQuery class uses the temporary table name
to set the SQL property. (The programmer does not need to set the SQL property.)
We now have an empty table to work with.
Let's return to the pushbutton's onClick event handler. Now that qTmp is active,
we can add data to the table. For the current example, we will traverse the
litters1 query looking, in the dogs1 query, for those Dams who were born within
the user defined date range. The code to do this follows. Add it as a continuation
to the code already in the kmYesButton1_onClick event handler.
if not form.kmCustCheckboxIgnoreDates.value
dStart = form.kmCustSpinboxStart.value
dStop = form.kmCustSpinboxStop.value
cFilter = "Born => '" + dtoc(dStart) + "'" ;
+ " and Born =< '" + dtoc(dStop) + "'"
endif
form.dogs1.rowset.filter = cFilter
form.litters1.rowset.first()
do
// check whether the dam is born within
// the selected date range.
if form.dogs1.rowset.findKey( ;
form.litters1.rowset.fields['dam'].value )
form.appendRow() // user defined
endif
until not form.litters1.rowset.next()
// createIndex( name, expression )
form.qTmp.createIndex( "Index1","reg_no")
The first part of this code creates a filter string from the user inputted dates and sets that filter on the dogs1 query. The second part loops through the litters1 query. For each Dam, we check to see if the dog is in the date range, and, if it is, add a row to the temporary table.
I use the AppendRow() user defined method to add data to the temporary table. This method is called whenever we find a dog that needs to be added to our temporary table. You need to add another method to the GetDates form with the following code:
function appendRow
local r, fDogs,i,fLitters
local cFieldName,cLookFor
// create some code shortcuts
fLitters = form.litters1.rowset.fields
fDogs = form.dogs1.rowset.fields
r = form.qTmp.rowset
// place qTmp into append mode
r.beginAppend()
// loop through the fields array
// add fields from litters1 where
// fieldnames match
for i = 1 to r.fields.size
cFieldName = r.fields[i].fieldname
if type("fLitters[cFieldName]")=="O"
r.fields[cFieldName].value = ;
fLitters[cFieldName].value
endif
next
// Lookup Master Record
cLookFor = fLitters['Dam'].value
if form.Dogs1.rowset.findKey( cLookFor )
r.fields['reg_no'].value = fDogs['reg_no'].value
r.fields['born'].value = fDogs['born'].value
r.fields['name'].value = fDogs['name'].value
endif
r.save()
return
We have now added a row to the temporary table and populated it with data from both the litters table and the dogs table. Our loop in the pushbutton's onClick event handler will continue to add rows until the end of the litters1 rowset is reached. After all the rows are added to the temporary table we create an index using the createIndex() method in the TMPQuery class.
Like Todd said, using temporary tables takes more work on the front end, so, this might be a good time to pause and do some clean-up. If you have tested the GetDates3 form, your working folder will contain numerous instances of the temporary table and the index files. At this point it would be a good idea to remove these files. You can use the dBASE Navigator or Windows Explorer to delete all these files. The table names all begin with "_TMP" and you can safely delete all the tables with this name.
After all the temp tables are deleted, run GetDates3, enter a date range (or use the default), and click the OK button. This will create a new instance of the temporary file. You should verify that it contains the correct data. When all is well with the table, write down the table name so we can use it a bit later.
Now lets return to the kmYesButton_onClick method and finish the procedure. Thus far we have created a temporary table and populated it with data. The next task is to open the report in the Preview.wfm form. Add the following code:
aParams = new AssocArray() aParams[ "temptable" ] = form.qTmp.fullpath set procedure to preview.wfm additive fPreview = new PreviewForm() fPreview.bModal = true // these next two commands // must be in this sequence fPreview.params = aParams fPreview.viewer.fileName := "TempTableSample.rep" fPreview.Open() return
This code is a bit more familiar. Notice that we are still using the params array. In this case we are sending the name of the temporary table (this is so the report can know which table to use). Although we will not do it here, you might also want to send the start date and the stop date. Not because the report is going to set the filter (the temporary table already contains only filtered data), but rather to print the dates in the report's header.
This completes the work we need to do with the GetDates form. We have added three methods to the form. The main "work-horse" is the pushbutton onClick event handler. It creates a temporary table, populates the table with selected data, and calls the preview form. Now we must turn to the report.
The report that we will use for this tutorial is contained in TempTableSample.rep. One of the tricky parts when using temporary tables is how to design a report. After all, a temp table is deleted after it is used. The way I do this is to run my form (GetDate.wfm in this case), with the qTmp.dropOnClose property set to false. I will then make a copy of this table, generally with the same name as the report file, and use it design the report.
A bit earlier you wrote down the name of a temporary table. You should now make a copy of that file. Switch to the Command Window and type the following commands.
Use _tmp???? // substitute the actual numbers for the "????" copy to TempTableSample with Production use
Normally you would now begin creating your report with the data in this table. This tutorial, however, includes a partially completed report file. Like we have done in the previous Sessions, here we will add only the pieces that are unique to the current topic. So if you made a copy of the temporary table and used the filename indicated, you should be able to open TempTableSample.rep in the report designer. Do so now.
Another tricky part of this reporting techique is getting the report to recognize the correct table name. Like any class, when a new instance of a report object is created, the entire constructor code is executed. This includes activating the query object. The problem here is that the temporary table's name is being pass in the params array and that array is not evaluated until the report renders. This means that the report does not know the table name until after the constructor code executes.
There are a number of ways to deals with this issue. The one we will use here is to activate the query object twice. First when the constructor code executes the query object will be activated like it is in a normal report. The query's sql property will select data from TempTableSample.dbf. Then when the render event runs, we will deactivate the query and reactivate it using the temporary table name.
We will use the query's canOpen event handler to determine the SQL property to use (this is the query object in the report, not qTmp, which is in the GetDates form). You should create a canOpen event handler for the query object in the example report and enter the following code.
Function Query1_canOpen
if type( "this.parent.params" ) == "O"
this.sql = 'select * from "' + ;
this.parent.params['temptable'] + '.dbf"'
else
this.sql = 'select * from "TempTableSample.dbf"'
endif
return true
When the query is set active, this code will check to see if the report constrains a params object (it will only when opened by Preview.wfm). If it does not, TempTableSample.dbf is use for the data; if a params object does exit (it will have been created in the render event handlers in the custom report), then the temporary table is use for the data.
The other piece of code needed to make this report work is a custom "setParams" method. Recall from the last Session that the render event handler in the duflp custom report calls a method named "setParams." This is done to let the developer add custom code that uses information in the params array. In this report the information we need is not a filter condition but rather the name of temporary table.
Therefore, add a standalone method to the report with the name "setParams" and enter the following code.
Function setParams form.query1.active := false form.query1.active := true form.query1.rowset.indexName := "index1" return
At the point when this method runs, the query is already active, so we set its active property to false. Next we set it true again, but now the params array is an object of the report and the query's canOpen event handler will use the temporary table in the SQL property.
The other thing we do in the setParams method is to assign the indexName that we want to use.
Note: When you deactivate, then reactive a query object, you must reset any rowset events that you have. This includes such properties as indexName, masterRowset, and any lookup fields. It also includes event handlers link like onNavigate and canGetRow. In situations were I have more than one rowset property to set, I add them all to a custom method and call that method after the query is activated or put them in the query's onOpen event handler.
There you have it. A reporting system that gets parameters from a user, creates a temporary "flat table" based on these parameters, and renders a report in a reportViewer.
So what's the big deal you say? This report could have been created using a SQL joined rowset. Sure we could have created a query that selects records form litters and dogs and we could have set a filter much like we did in Session Seven. No doubt the report that we used in the above example is not very complex.
But there are many situations where reports are more complex. I have included in the working folder another example report which highlights what drew me to using temporary files.
My users like to use forms with "mover" objects. This is a form with two listboxes--one with a list of items that the user selects from, the other with a list of selected items. We display a list of employee names or a list of client names, and the user moves the desired names to the other listbox. My reporting system then prints reports for those selected names. Although it is possible to construct a filter for this type of selection, it is not very straightforward. On the other hand, it is quit easy to loop through the listbox, seek the individual record and append it to a temporary table.
An example of this can be found by running the form named ItemMover.wfm. This form uses the Mover.cc control from the dUFLP. It loads the left listbox with Dam names (and their reg_no, but this is not visible to the user). The user can move any dog to the right listbox, and click the OK button to print the report that shows the Dam's offspring.
The code for the OK button's onClick event handler is very similar to the code we used in the GetDate3 form. It creates a temporary table, populates the table with selected data, and calls the preview form. The temp table's name is sent to the report just like we did above. Finally, the report contains a setParams() method that make it possible for the report to use the correct temporary file.
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.