Reporting with dBASE Plus:
A Tutorial
Michael Nuwer
October 28, 2002
|
Reporting with dBASE Plus:
|
|
Calculated and Lookup Fields |
The goals for this Session are:
There are at least three methods for creating a calculated field in a report. Ken Mayer discusses these in "Beginning Reports" and they are reviewed here.
Using Codeblocks. First, a text control on a report can display a calculation by evaluating a codeblock. The codeblock is evaluated for each row as the report renders and you can concatenate strings or perform arithmetic in this codeblock.
Using the Text Object's canRender Event. When the calculations are a bit more complex, and the codeblock cannot handle them, you can use the canRender event handler for an individual text control. The canRender event fires before the text object prints. You can thereby do a calculation and assign the result to the text property of the object.
Using the Query. Finally, you can use a query object to crete a calcuated field. In this case you would create a new field in the query, and it would contain the results of some calculation. This is done by using the onOpen event of the query object.
A common question is how we can use aggregate summaries on a calculated field. The short answer is that you must use a calculated field created in the query for the aggregate summaries to work. Using a codeblock or the canRender event handler will not work here.
Lets look at an example. Open the report named CalcField1.rep in the report designer. This report is a listing of dogs group by their Dam. Assume that we want to add a calculation for the number of years since the dog's birth and then sum those for each Dam (that is, sum them for each group).
You should begin by adding a calculated field to the onOpen event of the query. The code might look like this:
function dogs1_onOpen
local oField
oField=new Field()
oField.FieldName := "Calc_Age"
this.rowset.fields.add(oField)
oField.BeforeGetValue := ;
{; Return (date() - this.parent["born"].value)/365.25 }
return
Next add a text field to the detailBand so that the age of each dog can be displayed and add a codeblock for for the "Calc_age" field.
It is strongly suggested that you use controls from the dUFLP, the ones found in "RepCntl.cc". This file should be in the working folder (it is also in the :dUFLP: alias if you have that installed). The controls are on the "Custom" tab of the Component Palette. Use the "kmFieldText" control for this report. If for some reason the report controls are not on the custom tab, switch to the command window and type:
set procedure to repcntl.cc additive
In the footerBand another text field needs to be added (another kmFieldText control). This one will display the aggregate summary for the ages. The codeblock for this field will look similar to this (remember to type this all in one line):
this.parent.parent.agSum({;
||this.parent.rowset.fields["Calc_Age"].value
That's it for this report. You should be able to run the report and see an age for each dog and a sum of the ages for each Dam.
Another use of a calculated field is for generating groups in a report, which will give you more flexibility as to how you can group your data. You can, for example, create a calculated field based on two or more fields and use it to create a group in your report. An example might be were we want to group a report by both the Dam and Sire. A Dam can have more than one litter with the same Sire and can have litters with more than one Sire.
Here we will create a report group based on a substring of a field. Begin by opening CalcField2.rep in the report designer. This report is a listing of dogs, where the dog's reg_no is used to group the records. This does not make a lot of sense because each dog is in it's own group. However, assume we would like to be able to group the dogs by the first three characters of their registration number. To do this we need to add a calculated field to the onOpen event of the query. We can use the following code.
function dogs1_onOpen
local oField
oField=new Field()
oField.FieldName := "Calc_Key"
this.rowset.fields.add(oField)
oField.BeforeGetValue := ;
{; Return substr(this.parent["reg_no"].value,1,3) }
return
In order for groups to work correctly, we also need to set an index. For this report you should use "reg_no" as the indexName property. This will sort the records by all sixteen characters in the field and, therefore, the first three characters will also be sorted correctly.
The only other thing we need to do is to set the "groupby" property for the report. This report already has a "Group1" object, so use the Inspector, locate the "group1" object, and set it to the calculated field.
That should complete this report.
Lookup fields can be used in reports just as they are used in forms. In fact, in reports lookups can be very useful because a report is generally only reading data from tables. In this example we are going to create two lookup rowsets and use them in a report. Moreover, we will see that the lookupRowset can contain calculated fields that will display in the report.
Open lookup1.rep in the report designer. This report is based on the Litters.dbf table. It displays the litter date, the Dam and Sire. However, the Litters table contains only the Registration Numbers of the dogs and it would be nice to display their names.
We can do this with a lookup rowset. We will create two rowset objects that contain the dog's name and assign these rowsets to the fields, in the Litters table, that contain the dog's registration numbers.
Although we might be able to use a Field's LookupSQL property, here we will use the lookupRowset property. To do this we must first create a rowset. So, begin by adding the Dogs.dbf table to the report. We will need to modify the SQL property of the query to get this to work. Rather then selecting all the fields in the dogs table, enter the following select statement in the SQL property:
select reg_no, name from "DOGS.DBF" where sex="M" order by reg_no
This statement will select only two fields from the Dogs table and only those rows that contain Male dogs.
Next we will assign this query to the field in the Litters query that contains the registration number of the Sire. In the inspector select the Litters1 query object, drill down to the fields array and then select the field named Sire. One of the properties is lookupRowset. Set it to the rowset you just added. The report should now display the name of the dog rather than it's registration number.
The reason we are using the lookupRowset property is that it has some advantages over the LookupSQL property. The main advantage is that we can add calculated fields to the lookup table. To see this, lets add a second lookup rowset to this report. It will be used to display the name and registration number of the Dam.
Like before, add the Dogs.dbf table to the report. That's right, there are going to be two instances of the dogs table. One should be named Dogs1 and the other Dogs2.
Now enter the following statement in the SQL property of the Dogs2 query:
select reg_no, name ||" ("||reg_no||")" as CalcName from ;
"Dogs.dbf" where sex="F" order by reg_no
In this statement, the second field is a concatenation of the name and reg_no with the latter enclosed in parenthesis. The field name is CalcName.
The final task is to assign the rowset of this query to the field named Dam in the Litters query. Use the Inspector and drill down to the Dam field. Then assign Dogs2 to the lookupRowset property.
This report is now complete.
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.