Developing a Database Application:
The dBASE™ PLUS Tutorial

Ken Mayer, based on work by Michael Nuwer and Ken Mayer


Phase VI: Creating the Reports


  Back to the tutorial menu     Back to previous part of tutorial: Creating the Forms   Proceed to the next part of the tutorial: Creating the Menu 

Goals and Objectives

The goals and objectives of Phase VI of this tutorial are:

Additional Reading

What Is a Report?

A report is, in the case of many database applications, the whole purpose of the application. In "Introduction to Computer Science" type classes one of the many terms that a student learns is "Meaningful Output". While you can get meaningful output on the screen, you really need to be able to generate reports (even if they don't make it to paper).

The report engine in dBASE™ PLUS is unlike any report engine you have probably worked with. This is because the same concepts as have been in dBASE™ from the beginning (of the Windows product) also exist in the report engine and report designer. The primary part of this is "two-way-tools". What this means is that the designer generates dBL code, which you can modify yourself, and have the designer recognize it, and vice versa.

An Overview of the Report Engine

The report engine in dBASE™ PLUS is a bit daunting to someone starting out. So, before we get into the mechanics and details, let's take a look at things from a "higher level".

The report engine in dBASE™ PLUS is what is called a "banded report" engine, in that the processing of your data occurs through "bands". The bands are processed (when the report is rendered) in a specific sequence, as are the other controls of the report.

In addition, this engine is also what is called a "Single-Pass" report engine -- this means that in order to process the report it makes one pass through all objects (and in the process, through the table), rather than making multiple passes through the data and controls (which is what would be needed to, say, generate a "Page 1 of x" type output -- this can be done, but is more complex than what is covered here). Very few report engines do a "multiple pass" automatically, as the amount of processing involved is horrendous (and time consuming!).

We are going to start from the "inside" and work our way back out to the big picture.

The Bands
There are three primary "bands" in a report -- the detailBand, and attached to a group there are the headerBand and the footerBand.

The detailBand is where the details of your report will go. For a simple report, this is where all of the data from each row of a table is actually rendered (generated or printed). Your details are shown by placing what this document calls "Visible Controls" (Text, Image, etc.) onto the detailBand (used to display fields and so on).

The group object is designed to help you group your data in a way that makes sense. A very simple example is perhaps with a customer table -- you might want all the customers in each state grouped together. The group is how you do that. At the beginning of each group, you might want to note which state is being currently displayed -- this would be done with the group's headerBand. If you wanted to display a count of all the detail records that were displayed for that state, you might (probably) do that in the group's footerBand. (If you need to do a parent/child table report, the group's headerBand and/or footerBand would normally be used to display data from the parent table's rows, and the detailBand would be used to display the child table's rows.) In order to show the fields, you would also use "Visible Controls".

The report engine will automatically handle a lot of the processing for you -- if you have only a detailBand in the report, it will loop through your rowset and generate one detailBand for each row or record.

If you have a group or groups on the report, the report engine will examine each row in the detailBand and determine if it belongs in the current group, or if it belongs to the next group. It handles the necessary processing to do the group "break".

Stepping Up A Level - streamFrame and streamSource
In order to accomplish the above, the detailBand and group's headerBand and footerBands must be controlled in some fashion. There are two objects involved here -- the first is the visible one -- this is called a streamFrame. The streamFrame appears as a container on the report, and is used to control the layout of the report. Your detailBand and other bands are placed on a streamFrame (which is placed on a pageTemplate -- see below).

However, more importantly, in the background there is a streamSource object, which is linked to your query. The streamSource is what actually controls the output -- this is what looks at the data, and loops through it row by row, and tells the report engine what to print.

Stepping Up Yet Another Level - pageTemplate
When we step back a bit, and look at the larger picture, there is the pageTemplate -- which is used to lay out the report's page design. By this, I mean that you can set your report's margins, you can determine what you wish to print on each page of the report (commonly called headers and footers, although there are no such objects directly in this engine -- this is done using the "Visual Controls"), and so on.

The pageTemplate is exactly what it sounds like -- it is a template. When we get to examining the properties of the pageTemplate later in this document, we will see more.

The pageTemplate is also the object that visibly holds (or contains) the streamFrame, which in turn visibly holds (or contains) the detailBand and group header and footerBands.

Up To The Top Level - report
Finally, stepping all the way back and looking at the big picture, we have the report object itself. This object is what is used to control things like where the report is being sent to, what pages of the report to print, through the printer object (attached to the report object) whether the report is portrait or landscape, and all of that.

How The Report is Rendered (Generated)
Without getting into all of the details, the following is an explanation of how a report is rendered. The term "rendered" is really just another term for "generated", but we are using it here because the method of the report object that actually does the generation is called "render".

When you render a report, the following things happen:

Using the Custom Controls

Reports, just like forms, should be built using custom controls. The array of controls that would be used on a report is more limited than would be used on a form, but the same principles with respect to customization apply.

To create custom controls for a report, we could follow the same procedure as we did with forms. We could open a new report in the designer, place the standard controls we want to use, modify their properties, and finally save them as a custom component. However, to expedite the tutorial, we have provided a ready-made set of custom controls. In the final version of the tutorial you will see "CustomReportControls.cc" -- you can create it for your version of the tutorial by copying the code shown below and pasting it into the appropriate file in the "custom" folder -- in the Command Window:

               create command custom\CustomReportControls.cc
This file will contain the custom controls that we will use in the Tutorial reports. The controls are taken from a more robust set of report controls contained in the dUFLP. The file that contains these controls is: REPCNTL.CC. However, we have renamed them for the tutorial, and are only using some of the controls.

            class MyReportTitleText(parentObj) of TEXT(parentObj) custom
               with (this)
                  alignHorizontal        := 1	// Center
                  alignVertical          := 1	// Middle
                  fontItalic             := true
                  fontName               := "Arial"
                  fontSize               := 18
                  height                 := 0.3229
                  metric                 := 3	// Inches
                  text                   := "This is a Main Report Title"
                  transparent            := true
                  width                  := 3.5313
                  wrap                   := true
               endwith
            endclass
            
            class MyStandardText(parentObj) of TEXT(parentObj) custom
               with (this)
                  alignHorizontal        := 0 // Left
                  alignVertical          := 0 // Top
                  fontName               := "Arial"
                  fontSize               := 10
                  height                 := 0.18
                  metric                 := 3	// Inches
                  text                   := "MyStandardText"
                  transparent            := true
                  width                  := 1.1667
               endwith
            endclass
            
            class MyDateText(parentObj) of MyStandardText(parentObj) custom
               with (this)
                  alignHorizontal        := 1 // center
                  alignVertical          := 1 // Middle
                  text                   := {||"Date: "+date()}
               endwith
            endclass
            
            class MyFieldHeadingText(parentObj) of MyStandardText(parentObj) custom
               with (this)
                  height          := 0.18
                  width           := 0.75
                  alignHorizontal := 2 // Right
                  text            := "FieldHeading: "
                  fontBold        := true
               endwith
            endclass
            
            class MyFieldText(parentObj) of MyStandardText(parentObj) custom
               with (this)
                  width           := 1.2188
                  variableHeight  := true
                  // This needs to be modified for each instance/field
                  text            := {||"field codeblock"}
               endwith
            endclass
            
            // Start with a base shape class:
            class MyRectangleShape(parentObj) of SHAPE(parentObj) custom
               with (this)
                  width       := 2.9479
                  height      := 0.4063
                  metric      := 3	// Inches
                  colorNormal := "BLACK/WHITE"
                  penStyle    := 0 // Solid
                  penWidth    := 2
                  shapeStyle  := 1	// Rectangle
               endwith
            endclass
            
            class MyLine(parentObj) of LINE(parentObj) custom
               with (this)
                  left            := 0
                  right           := 4.4722
                  top             := 0
                  bottom          := this.top
                  fixed           := false
                  width           := 2
                  metric          := 3	// Inches
               endwith
            endclass

Once you have copied and pasted this code into the file, press <Ctrl>+W to save and exit the source code editor.

In addition to these controls, we will use a custom control that is included with dBASE™ PLUS. This file is REPORT.CC. To activate these two files, open the configuration program (configure.prg) and add the following lines:

            //  Setup custom controls for use the reports
            set procedure to :DT_Custom:CustomReportControls.cc
            set procedure to :ReportControls:report.cc

Save the program file. When you run this program, the report controls will be added to the dBASE™ PLUS Component Palette when working on reports. You should do that now.

Creating the Custom Report

A custom report works much like a custom form. You define the basics and then the reports that are derived from the custom report inherit any properties and/or code that is defined in the custom report.

To start out we should bring up a custom report on screen so we can start figuring out what we need. In the navigator click the "Reports" tab, and then double-click the "[New Custom Report]" icon.

Before doing anything else, if you have not already done this we will set the report designer's "snap to grid" off -- otherwise you will have a LOT of problems positioning controls on the report: Right click on the design surface. Select "Report Designer Properties". In the dialog, find the "Snap to Grid" checkbox, and uncheck it if it is checked ... click "OK" and you have done it. This will save you a lot of grief working in the report designer. Note that you only have to do this once.


In the designer, note the vertical line with the ruler on the left side? Try dragging it a bit to the right and you will see that the report designer has two panes. You may want to leave the left side opened up, or not. Some developers find the band pane on the left to be distracting, and frankly there are a lot visual cues in the main report pane. The screen captures here will leave it closed.

Note -- if you cannot find the Inspector or the Component Palette, you can get to them in one of several ways. The easiest is to use the "Window" menu in the dBASE™ PLUS menu at the top of the screen, and select the one you want. You can also right click on the design surface.

We are going to set some properties for our custom report that are very important to all reports in this application.

First, bring up the inspector (if it is not easy to see [under the design surface, for example], try <F11> -- you may need to press it twice, once to turn it off and the second to turn it back on), and click on the metric property. Set this to "3 - Inches" (if you are European or non-US, you may want to use Centimeters or Millimeters -- if you are really comfortable working with TWIPS, PIXELS or other metrics, that's ok, too). However, you should note that for the tutorial it is a good idea to use inches, as the custom controls (see above) are set to work with that metric. Once you have a report designed and all the custom controls on it, changing the metric for the report would be okay.

Click on the autoSort property and set it to false if necessary -- in current versions of dBASE™ this is set to false by default. Click on the title property and set it to "Tutorial Custom Report". (The title property appears in the title bar of the report preview, and when we get the application completed, in the report preview form that is we will discuss later.)

Next, in the inspector find the firstPageTemplate property and click on the "inspect" button. Set the following -- marginTop, marginLeft, marginRight, marginBottom to 0.5 (1/2" -- for you folk using other metrics, determine a good value ... if you need it, there's a routine in the dUFLP library called METRICS.CC that will convert different report metrics).

Select the STREAMFRAME1 object and click on the "inspect" button. Set the height to 8.94, the left to 0, the width to 7.5, and the top to 0.72. (For those not using inches for metrics, the idea is that the streamFrame is as wide as the pageTemplate, and leaves some room at the top and bottom, but not as much at the bottom.)

We just set a BUNCH of defaults which will be used for all of our reports.

We'll do a couple of other things before we leave the designer, but let's save it: press <Ctrl>+S, change the folder to the "custom" folder, and enter "base" as the file name. (This will be saved with .CRP as the extension, for Custom RePort.) The report's class name will be "BaseCReport".

Next we're going to place a set of custom text controls on the pageTemplate. Why on the pageTemplate? So that they will print at the top (and/or bottom) of every page of any report that is generated. Consider the pageTemplate to be the place to put something you want to print on every page (unless you are getting into group headers and such -- we'll get to those later. Don't use report headers or footers -- these print one time only for any given report!).

Select a "MyReportTitleText" control from the component palette (on the "Custom" tab). Drag it to the top of the pageTemplate (note -- the pageTemplate ends at the white grid of dots -- do not try to place the text control above that, or you will get a "negative" value for the top property and will never see this control). Change the text to read "Tutorial Custom Report".

Select a "MyDateText" and place it under the first. Both of these should be above the "streamFrame" which is shown on the surface as a sort of dash-line (and again, keep it on the grid).

Holding the <Ctrl> key, click on both controls, and select the "Layout" menu, select "Align" and "Center Horizontally in Window". This will line both text controls up, and they will be centered on the pageTemplate.

Use the scrollbar on the right and scroll down so you can see the bottom of the pageTemplate. (If you have the form designer surface off the bottom of your screen, you may need to use the scrollbar on the right side of the dBASE™ PLUS screen as well -- or you could reduce the height of the designer window.) Click on the "Paging" tab of the Component Palette, and drag a "pagenumber" control onto the surface and make sure it is under the streamFrame. Let's make sure it is centered, by using the "Layout" menu, select "Align" and "Center Horizontally in Window".

Because of the bug noted above with custom controls and the Report Designer, you will want to use the combobox in the Inspector, find the "pagenumber1" control, change the left property to 4, and the top property to 10. Now you can see the control. Widen it so that it shows the number, move it so it is on the pageTemplate (the white section at the bottom), and then align it as shown above.

Most of our reports will not use the report's headerBand or footerBand. Scroll back up to the top of the page, click on the green band, and in the Inspector note that this is (in the combobox at the top of the inspector):
form.reportGroup.headerBand
In the inspector, change the height to 0. This does not delete the band, but it will not display unless we change it. Repeat this for the footerBand (the purplish-bluish one). This will leave the "coral" band, which is the detailBand for the report, which we will work with extensively.

Your base custom report should look like this:


Save and exit (<Ctrl>+W) the designer.

Create the Supplier Information Report

In order to use the custom report we created, we need to do something similar to what we did with forms.

Double-click the "[New Report]" icon under the Reports tab (of the Navigator). This will bring up a blank report design surface. We need to make sure we are using the tutorial custom report, so go to the File menu and select "Set Custom Report Class". The dialog should look very much like the one for forms. Select the "custom" folder, and then "base.crp" for the filename, and click "OK". You should now see the text controls we placed on the custom report's design surface.

Click on the report title text control, and change the text to: "Suppliers". In the inspector, change the report's title property to "Suppliers" (Note: the report object appears as "form" in the combobox at the top of the inspector).

Save what you have done with <Ctrl>+S. Type "suppliers" as the report name.

Your report will look something like:


Now we need to get the data for this report. Click on the "Data Modules" tab on the Navigator, and drag the "supplier.dmd" onto the report surface. (We talked about the possibility of using the same data module in both a form and a report, well ... here we are!)

As soon as you do this, you will see four detailBands, because we have four records in the supplier table. We will only actually work on the first detailBand, but any changes we make there will affect the others.

At this point, just like in the form designer the Field Palette (if it is on screen) will show all the fields for the supplier table. However, just like when we built our forms we will not use the field palette, because it uses the stock controls. We want to use the custom controls that are available.

Working with the DetailBand
Go to the inspector, and click on the combobox. Under "streamSource1" you should see "detailBand". Select this. This will bring the detailBand to the inspector so we can manipulate it.

We're going to temporarily set the height property of the detailBand to 2 (for 2 inches). This is a fairly tall detailBand, and we will end up changing this back to the default of zero (which means it is a "variable height" detailBand) later. Type "2" and press <Enter>.

Let's place a text control on the design surface that will be a heading for a field. Using the "Custom" tab of the Component Palette, select a "MyFieldHeadingText" control and place it in the upper left of the first detailBand. Change the text to read: "Company:". In the inspector, change the fontBold property to true.

(Remember bug mentioned earlier, you will need to move this up and left and resize it. To find these in the Inspector, use the combobox, and the last item by a specific name, such as "MyFieldHeadingText3" or "MyFieldText3" will be the one you need to modify.)

Depending on your window sizes and such, you should see at least two instances of this text control -- this is because the report designer is "live" -- it works with the data from the table directly. If you could see it, you would see that there is one instance of this control for each row in the table.

Next, select "MyFieldText" and place it on the design surface next to (but not overlapping!) the "Company:" text. This is going to be something we do a lot. We want to reference a field in the table, and we need to replace the text that appears now "field codeblock" with the actual codeblock necessary to display the contents of the field. To do this, in the inspector find the text property of the object, and click on the tool button. A new dialog will appear, and we need to enter the following (overwriting what is currently there):

   form.streamSource1.rowset.fields["Company"].value

If you look, the "expression" radiobutton is selected (which it should be), which means that rather than display this as literal text (which is obviously not what we want to happen) it will be set as a codeblock -- this will be evaluated each time this text is rendered -- once for each row in the rowset (see "Beginning Reports" in the dBASE Knowlegebase for more details.).

Click the "OK" button for the dialog. You will see the name of the company appear. You will probably want to widen this. To be sure, scroll down in the designer until you see "Sports Mugs International" -- if the text "wraps" then you want to widen the text control so that it doesn't. (You may also want to check the height of the text control ... sometimes when a text control wraps, even after you have widened it the height remains where it was when it was wrapped.)

Your report should look like the following at this point:


An interesting phenomena is that you can only manipulate the first detailBand on a report (unless a control doesn't display any text in that detailBand -- we'll see this in a moment) ... this can be a bit disconcerting at first. We are going to place a variety of text controls on the report surface, using exactly the same steps we did above. When done you should see something like the image below.

To get the controls correct, try using this information:

 Column 1Column 2
Line ControlFieldControlField
1MyFieldText1  CompanyMyFieldText2  Supplier ID
2MyFieldText3  ContactMyFieldText4  Email
3MyFieldText5  PhoneMyFieldText6  Fax
4MyFieldText7  Street1  
5MyFieldText8  Street2  
6MyFieldText9  City, State, Postal  
7MyFieldText10  Country ID  

As noted above, the second street address is not always filled in for some rows. The problem is, as you can see, the area reserved for that text control is still "there". We need to suppress this blank row. To do that, we need to do the following:

Note that this will not appear to do anything in the designer. When run the report, however, all will be well.

Problem: if we reduce the height of the detailBand, we will see the details all running together. One good way to break things up is to place a "line" control across the top of the detailBand. Drag a "MyLine" control from the component palette to the surface and place it at the top of the detailBand, above the text controls. This will take a bit of adjustment to get it "just right". If necessary, change the top and bottom properties to the same values (in the inspector), so that the line is horizontal, rather than diagonal. (When working on the tutorial, the top and bottom properties are 1.25.) To make the line go across the detailBand from left to right, set the left property to 0, and set the right property to 7.5.

Finally, the detailBand is taking a lot of room. Let's reduce the height by setting the height property back to zero.

There are a lot of options we have not explored here -- we could set colors (but unless you have a color printer, they won't print), we could place images and other controls on the surface of the report, and so on. We could make the company name larger than the rest of the text.

Your report should look something like the following in the Report Designer:


Done With This Report
Save this and exit the report design surface (<Ctrl>+W). To see the report, simply double-click on it in the Navigator. This will preview the report ... when done close the preview with the 'x' button in the titlebar.

Create the Inventory Summary Report

This report will list the inventory by supplier showing the quantity on hand for each item in the inventory, and totaling the quantity on hand for each supplier. In order to do the totals we will be using an aggregate function and group header and footerBands. We will also be using two tables, the supplier (as a 'master' table) and the inventory (as a 'detail' table).

Before we get into creating the report we need a new data module, because the Inventory data module we created earlier doesn't have the correct layout for our needs. So, we need to go back and create a new data module for this report.

Creating the Datamodule
The data module that we are going to create for this report is subclassed from a data module we have already built and are using in one of our forms. The Supplier.dmd data module contains a query object for the supplier table. We will use this data module as the super class for our Inventory Report data module. This will save us from needing to reconstruct the Supplier query object and it's lookupSQL fields.

Create a new standard data module. Then select File | Inherit New | Datamodule. In the Select Custom Datamodule dialog, enter "*.dmd" in the file name field (or select "data module from the "files of type" combobox). One of the data module files in the dialog is "suppler.dmd". Select this file and click the open button (be sure you select the dmd file, and not the dmo file).

You will notice that the dBASEtutorial1 database object and supplier1 query object are now contained in this new data module. This is because both objects (and all their properties) are inherited from the super class.

All we need to do in order to complete this new data module is to add the inventory table and to set the relations between the two query objects.

Open the "dbasetutorial" database in the list on the left and drag the "Inventory" table to the design surface.

We need to set the relationship between the two tables. Set the indexName property for the Inventory1 rowset to "SUPPLIER ID". Then in the Supplier1 query object, click the "Supplier ID" field. Drag this field onto the Inventory1 query object.

The rowset property of this data module should be "Supplier1". Be sure it is set as the controlling rowset. (Press the <F12> key, and check -- it should be set for you.)

Now that we have this data module, save is as "InventoryRep.dmd" and exit.

Back to the Report
We need to create a new report (double-click the "[New Report]" icon), which should automatically inherit the custom report we set up earlier. (If it doesn't, then follow the standard steps to use it -- since right now we only have one custom report, that should be pretty easy.)

Change the title property of the report to "Inventory by Supplier" and then change the first text control to the same text.

Press <Ctrl>+S and enter "inventory" as the report name.

Drag the "InventoryRep.dmd" to the surface from the navigator (from the "Datamodules" tab of the navigator). You will see the number of detailBands now matches the number of rows in the Supplier table.

The image below is a guide for what the report will look like as you are designing it - it shows the detailBand as well as the group header and footer bands for one record.


Work on the detailBand

First let's get some info for the detailBand in here. Without an empty detailBand it will be difficult to do much else. The detailBand is going to contain some headings and we're going to lay the data out in columns.

Click on the detailBand (the pink-ish/coral band), and in the Inspector change the height to 1 (1 inch), we will change it after we are done working in the detailBand. This will give us some room to add controls and manipulate them without the designer moving things around as much "for" us.

Drag a "MyFieldHeadingText" control onto the detailBand. Set the fontBold property to true, and changing the text to "Item ID". Resize the width of the control as needed.

Place two more "MyFieldHeadingText" controls on the design surface, each should be next to the other. Set the fontBold property to true for both, and the text of the first one to "Description" and the text of the third one to "Quantity". For the "Description" heading, set the alignHorizontal property to "0 - Left". Resize as needed. (Note that you can get these to all line up by holding the <Ctrl> key and clicking the mouse on each so that they're all selected, and using either the toolbar alignment tools or the menu options.)

Place a "MyFieldText" control under the "Item ID" text. Change the codeblock to (Note: enter this without any line breaks -- much depends on the width of your browser -- it should be one line):

   this.form.inventoryrepdatamodule1.inventory1.rowset.fields["Item ID"].value

Because we're using a data module, we need to use syntax as shown above which is different from the previous report. If you follow the syntax (the dots are separators between object references), it makes sense, but it does take getting used to.

Now let's get the Description and QTY fields in there in a similar fashion. You will want to widen the Description field a bit, and this may mean moving the text controls around to make things "fit" and look good.

This is all we want for the detailBand. Problem is, do we really want the column headings to print for each row? Probably not. To solve this what we will do is use the canRender event for each of these, and check to see if the detailBand is the "first on the streamframe" -- there is a property of the detailBand that is "read only" that can be evaluated for this.

Click on the "Item ID" (heading) text control, and in the Inspector click on the "Events" tab. In the canRender event, click the "T" (for "Type") button and select "Codeblock", and then enter the following:

   {||this.parent.firstOnFrame}

Repeat this process for the "Description" and "Quantity" heading controls.

The firstOnFrame property is a read-only value that is either true or false. It is true the first time the parent (i.e. the detailBand) is rendered in this streamFrame. This can be useful to print something only once on a page, for when the firstOnFrame is false, canRender is also false and the control will not be printed.

In addition set the suppressIfBlank property to true for this control. Do the same for the other two headings.

Set the detailBand's height property to zero.

It should be noted that as we have just added these controls and modified their properties and event handlers that the designer has not evaluated them. If we were to close the report in the designer and then open it back up in the designer, we should see the report acting the way it will ultimately -- the headings will only appear once on the page, and so on.

Now we're ready for the fun part ... the group.

The Group
This is how we will break the data out by supplier, and in addition it is how we will set the aggregate calculation used to get the total quantity of mugs for that supplier.

On the "Report" tab of the component palette is a "group" object. Drag this to the streamFrame. All you should see to start with is "Header Text for Group1". We'll get to that. Go to the inspector, and select "group1". Select the groupBy property and in the combobox select "Company".

Click on the text control on the headerBand. Change the text to read "Supplier:", set the fontBold property to true, and the fontSize property to 12.

Next, drag a "MyFieldText" control to the headerBand next to the one we just modified. Set the text codeblock to read:

   form.streamsource1.rowset.fields["Company"].value

Note that in the detailBand we had to reference the data module object, here we are referencing the streamSource -- this is because the supplier table is the controlling table for the report, and there is a reference in the report's constructor code setting that table as the default rowset.

Change the fontBold property to true, the fontSize property to 12, and resize this control so that it fits. (You may want to use the alignment tools here.)

The report is starting to shape up nicely at this point.

The Group footerBand
Next we want to add an aggregate function in the footerBand of this group. The footerBand itself is not visible as it has a height of zero, and no controls on it. So the first step is to make the footerBand taller than the default height of "0", so we can deal with it. In the inspector, you will find "group1" (use the combobox at the top), and you should see footerBand right underneath it. Select this in the combobox, and set the height property to 1 (1 inch).

Select a "MyFieldHeadingText" control and place it in the footerBand, under the description. Change the text to read "Total mugs on hand for this supplier:". Widen the text control so there is room. Set the fontBold property to true.

Next comes the hard part -- well, not really ... we need to set the aggregate total for the group here. This sounds like it ought to be difficult, but it's not. Drag a "MyFieldText" control onto the footerBand (make sure you place it there and not in the detailband), and place it under the Quantity field. Set the text codeblock to read (Note: enter this without any line breaks -- it should be one line -- it will wrap in the dialog box, but ignore that -- don't try to fix it):

  this.parent.parent.agSum( {||this.parent.rowset.parent.parent.inventory1.rowset.fields[ "qty"].value})

Set the text's fontBold property to true, and you may want to tinker with the picture property (for the working example, I set this to "999", but then had to set the alignHorizontal to the right, and tinker with the size of the control). And finally, reduce the footerBand height by setting the height property back to zero.

In the designer, the report should look something like:


Save and exit (<Ctrl>+W) and run the report by double-clicking on it -- this will preview it, which is handy. It should look close to the way it appeared in the designer, although the column headings should only appear once, as we designed.


Create the Customer Invoice Report

The Customer Invoice report will be generated only from the invoice form (Invoice.wfm) (there is a print button on that form that we didn't actually add any code to -- now it's time). We are not going to be using the original "Invoice" data module due to the fact that the design of the report is a bit different than the design of the form. So, we are going to have to create a new data module for this report as well as the one we created for the previous report.

To make this work, we are going to get some information by passing it through the dBASE™ PLUS application object (_app). This information will be used to find the appropriate data.

Modify the PrintInvoice Button For the Invoice Form
We need to know which invoice id we are going to print (which is also the Invoice Number). So, we are going to first go back to the invoice form, and modify it a bit -- open "Invoice.wfm" in the form designer -- right click it in the Navigator and select "Design Form".

The part we will modify is the onClick event handler for the "PrintInvoice" button. Click on the "PrintInvoice" button, and in the Inspector, select the onClick event. Click the toolbutton, and enter the following code:

            function PRINTINVOICE_onClick()
               // check to see if we're at the endOfSet (i.e.,
               // no invoices), otherwise we'll do the print
               // routine:
               r = form.INVOICEDATAMODULE1.invoice1.rowset
               if not r.endOfSet
                  // store the primary key field needed
                  // in the _app object as custom property
                  _app.InvoiceID  = r.fields["Invoice ID"].value
                  // Call the report with the preview form ...
                  do preview.wfm with "Invoice.rep", true // modal!!
                  _app.InvoiceID  = NULL // cleanup
               else
                  msgbox( "There is nothing to Print!", "Can't do that!", 16 )
               endif
            return

Save and exit the form designer (<Ctrl>+W).

Now that we have done that, the form's code should be able to find the data we need when we actually generate the report. So, it's time to start creating the report.

Create the DataModule
Yes, we're going to create another data module. The reason we are not using the one used for the Invoice form is that the layout is different. We are going to use four different tables here (if we do a lookupSQL we only need three).

Start by creating a new data module and use the "Inherit New | Data Module" menu item to subclass this from the Base.cdm custom data module.

Now, open the "dBASETutorial" database in the treeview on the left of the designer, and drag the invoice table onto to the design surface.

Save the data module (making sure that you are in the main tutorial folder) as "InvoiceRep" before the next instruction.

Press the <F12> key, and before the "endclass" statement add:

   this.rowset = this.invoice1.rowset

Press <Ctrl>+W to close the source editor.

Click back on the Invoice query object, right-click and set the indexName (Set Order) to "Invoice ID".

Save the data module (<Ctrl>+S), making sure you are in the main folder for the tutorial (not the "custom" folder) with the filename of: InvoiceRep.dmd

Next we need to drag the Customer table onto the designer surface.

Set the indexName property to "Customer ID". And set a relation using the Invoice query's Customer ID field and the Customer query.

We are creating a one-to-one match here. There is only one Customer ID that will match this Invoice.

Set the lookupSQL property for the "State ID" and "Country ID" fields as we have done in other datamodules. (Click on the fields array and drill down to the fields, select the "State ID" field and drill down to it, select the lookupSQL property and enter "SELECT * FROM STATE" -- repeat for "Country ID" but for the COUNTRY table. We don't need the "ORDER BY" clause, because the report is not interactive so the order doesn't matter -- the lookupSQL property will find the appropriate match.)

Finally, we need to drag the LineItem table to the designer, and set it up. Set the indexName property to "Invoice ID". Set a relationship between the Invoice query's "Invoice ID" field and the LineItem query.

Finally, set the lookupSQL property for the "Item ID" field to:

   select inventory.'item id', description from inventory

The design surface of the data module should look something like (you can move the queries around as needed, I laid it out this way to make it the links between the tables more obvious):


Now that we have the data module designed, save it and exit the designer.

Create the Invoice Report
This report is going to take a bit more work than the previous ones, but it shouldn't be too bad.

To do this particular report we are going to have to work on it in three sections -- one for the customer data, one for the payment information, and the last for the detail data.

Click on the "Reports" tab of the Navigator. Double-click on the "[New Report]" icon in the Navigator, to start a new report. This new report should be subclassed from the base.crp custom report and therefore it should have a title and a date. Change the title of the report and the text at the top to "Invoice".

Save the report now, so we have a name and don't have to worry about it later. Press <Ctrl>+S and name the report "Invoice".

The following image is here to show the layout of the controls, you may want to reference it as we build this report.


Drag the new "InvoiceRep" data module over to the design surface.

The Field Palette (if it is displayed) should show three tabs with all three tables represented in the data module here. Once again we're not actually going to use the Field Palette, but we're using it for a reference (this is an easy way to be sure you are getting the field names correct).

Next, click on the date text, and change the text to be displayed by clicking on the tool button by the text property. All we want to do is change the word "Date:" to "Date Printed:". When you do this, and click the "OK" button, you will need to widen the text control a bit, so that the whole thing appears on the report as one line of text (you also want to select the "Layout" menu and "Center Horizontally in Window" while this text control has focus, to make sure it is centered).

Why did we do this? Because we will also be printing an "Invoice Date" momentarily, and we do not wish the invoice to be confusing to the customer, or employees of the company reading it.

Select the "group" object from the Component Palette (on the "Report" tab), and drag it to the streamFrame. Set the groupBy property to "Invoice Id". Drill down to the headerBand object, and set the height to 2.5 (2.5 inches). To make it easier to see how this will look when we print it, set the beginNewFrame property to true. (This ensures that each time the group changes, we start a new streamFrame, which in most reports means a new page.)

Delete the default text control yet (the one that appears in the group header with text: "Header Text for Group1"). Drag a "MyFieldText" control onto the design surface, and place it in the upper left corner of the band.

Change the text codeblock of this control to (include the quotes):

   "Invoice #: "+form.streamsource1.rowset.fields["Invoice ID"].value

Click the 'OK' button, and you should see the text in quotes followed by an invoice number. ("Invoice #: 1" is what should appear.)

Set the fontSize property to 12, to make this stand out a bit. You may need to widen the text control a touch.

Let's drag another "MyFieldText" control and put it under the first one (don't overlap them!). Change the text codeblock to:

   "Order Date: "+form.streamsource1.rowset.fields["Order Date"].value

You will need to widen the control, and then set the fontSize to 12 (which may mean further adjustments).

Customer Data
The next part of the report we need to put in is the customer information. This will also go in the group headerBand. (We're only printing one customer per invoice, right?)

Some developers like to uses rectangles in their reports to group some of the data. Experience has shown that rectangles, while looking great, cause some problems when actually printing (they often print offset from the text that is placed on them to the left and up, so that they do not look correct -- this is a known BUG). Instead, we will be using a shape object set to a rectangle.

Drag a "MyRectangleShape" from the custom tab of the component palette into the group headerBand under the text controls that are already there. Resize the rectangle a bit (we'll resize it again later).

An oddity of placing controls onto the surface of a rectangle (shape) control is that the Report Designer wants to place them in the upper left corner of the shape. This does mean you will have to keep moving the controls after you place them on the shape.

To show that the next part of the report deals with customer data, drag a "MyFieldHeadingText" control onto the rectangle, and set the text to "Customer", and set the fontBold property to true.

Next we're going to place text controls on the group headerband for the customer data. This is simply going to be the customer name and address, and in order to get the data to look right, we will use the canRender event to do so (it could be done using a codeblock, but I find codeblocks can get rather unwieldy for something as complex as this).

First, put a "MyFieldHeadingText" control on the rectangle, and change the text to "Name:".

Next, put a "MyFieldText" control on the surface (to the right of the heading), and set the canRender event as follows:

            function MYFIELDTEXT3_canRender()
               local f
               f = form.InvoiceRepDataModule1.customer1.rowset.fields
               this.text := new string( trim( f["first name"].value ) )
               this.text += " "
               this.text += new string( f["last name"].value )
            return true

Because this code is in the canRender event, it will not be evaluated at the moment. Widen it a bit as the chances are most combined first and last names will be wider than the text control defaults to.

To the right of of the name, place a "MyFieldHeadingText" control and change the text to "Address:".

And to the right of that, place another "MyFieldText" control, and set the canRender event to:

            function MYFIELDTEXT4_canRender()
               // This one's going to be a bit more complicated,
               // but it will solve some issues in getting
               // everything to "lay out" properly:
               local f
               f = form.InvoiceRepDataModule1.customer1.rowset.fields
               this.text := new string( f["street1"].value )
               if not empty( f["street2"].value )
                  this.text += "<br /> " + new string( f["street2"].value )
               endif
               if not empty( f["city"].value )
                  this.text += "<br /> " + ;
                               new string( trim( f["city"].value ) ) + ", " +;
                               f["state id"].value.rightTrim()  + "  " + ;
                               new string( f["postal"].value )
                  if not f["country id"].value.rightTrim() == "United States" 
                     this.text := this.text.rightTrim()
                     this.text += "   <b>"+f["country id"].value+"</b>"
                  endif
               endif      
            return true

(Note that using the "new string()" construct on fields that have a lookupSQL property will generate blank values -- not sure why this is, but don't use it in those circumstances!)

This is more complex than we've done before, but it is a useful way of dealing with fields that may be empty (rather than using the suppressIfBlank property for multiple text controls), and getting everything to line up properly (and may actually be a better way of doing it). The "<br />" characters are HTML "line breaks". There must be a space after them for them to work properly (this is a very minor bug in the HTML interpreter in dBASE™ PLUS). You will want to widen this quite a bit ... it may not look necessary, but trust me.

Next, adjust the shape object -- size it accordingly. Make sure you leave room for the width of the address field, and room for up to three "lines" to be displayed. If you are using inches for your report, the height of the shape in the example here is 0.84. The report should look something like the following:


Save and exit, and let's run it to see what it looks like in the report viewer. You may decide you need to change the width of the controls if you have a name or address that wraps funny.

Once you've examined it, close the reportView down and bring the report back into the designer.

Payment Data
The payment part of this report is going to get a bit tricky, so follow carefully. We will need to determine what we are going to render based on several things.

As we did above, place a "MyRectangleShape" on the group headerBand under the other. Try to line it up (left and width) to match the one above it, but leave a little space between the two.

Place a "MyFieldHeadingText" control on the new rectangle, change the text to "Payment", and set the fontBold property to true. Line this up with the "Customer" text control on the shape/rectangle above it.

Under that, place a "MyFieldText" control. We're going to spend a lot of time doing canRender event handlers from this point on (some of this is for field morphing, and some of it is because we simply need to display or not display the control based on specific values) ... go to the canRender event in the Inspector, click the "tool" button, and set the following code:

            function MYFIELDTEXT5_canRender()
               local f
               f = form.streamSource1.rowset.fields
               do case
                  case f["pay type"].value == "CH"
                       this.text := "Check: "
                       this.text += f["check number"].value
                  case f["pay type"].value == "VI"
                       this.text := "Visa: "
                       this.text += right( trim( f["card number"].value ), 4 )
                  case f["pay type"].value == "MC"
                       this.text := "MasterCard: "
                       this.text += right( trim( f["card number"].value ), 4 )
                  case f["pay type"].value == "DN"
                       this.text := "Discover/Nova: "
                       this.text += right( trim( f["card number"].value ), 4 )
                  otherwise
                       this.text := "Unknown payment type"
               endcase
            return true

You will need to widen the field (not obvious when examining it on the design surface) as you will need to be able to display a fairly lengthy amount of text, including the text "Discover/Nova" and the last four digits of the credit card number.

NOTE: The reason the code above is using right( trim( f["card number"].value ), 4 ) is because we do not wish to display the full credit card information on a printout. There are security concerns, and it is never a good idea to print out such information. Instead we are printing the last four digits of the card number only. This is fairly common in the modern world. The reason the trim() function is being used in the statement is because it is possible when entering the data that spaces might be added after the number, which means if we select the rightmost four characters and they include a space (or multiple spaces) we may not display all four digits we wanted to. This just ensures that we are getting the numeric values we need to display.

Next we need to display the card's expiration date and then the name that appears on the credit card. These will have simpler code because we only need to check to see if the customer paid by credit card, and if not, we won't display them (if the canRender event returns a value of 'false', then this text control will not be rendered, and therefore will not display on the report at all).

The first of these two text controls (MyFieldText) should be on the same "line" as the one we just set, but to the right, and the canRender event would look like:

            function MYFIELDTEXT6_canRender()
               local f
               f = form.streamSource1.rowset.fields
               if f["pay type"].value == "CH"
                  return false
               else
                  this.text := "Expiration: " +;
                              new string( f["Card Exp"].value )
               endif      
            return true

The next text control (name on card) would go under the other two, and have a canRender event like:

            function MYFIELDTEXT7_canRender()
               local f
               f = form.streamSource1.rowset.fields
               if f["pay type"].value == "CH"
                  return false
               else
                  this.text := "Name on card: " +;
                              new string( f["Card Name"].value )
               endif      
            return true

You will need to widen this up as well to give room for the "name on the card" as well as the text.

Adjust the shape/rectangle, and the text controls to line them up. If your report metric is set to Inches, the height in the sample shown for this rectangle is 0.75.

Set the group headerBand's height property to '0' (zero), so that it resizes itself appropriately.


This looks sort of nonsensical, as the text controls are displaying "field codeblock", but when we run this, we should see good information. Save and exit, and then run the report (from the Navigator), so we can see what it looks like so far.

You may need to widen some of the text controls, so be prepared to bring it back into the designer, change the controls, and run it again.

When done viewing it, bring it back into the designer so we can complete it.

The Line Items
We will not use a rectangle around the line items as the number of line items for any order is effectively unlimited. We will use a layout similar to what we did in the inventory report. The line items are the "detail" part of the report, so must be placed in the detailBand.

First, find the detailBand object in the inspector, and set the height to 1 (1 inch) so we have some room to maneuver.

Next place three "MyFieldHeadingText" controls on the design surface (in the detailBand) next to each other, and change the text to "Item ID", "Description" and "Qty" (you will want to move the "Qty" text over to the right a bit). Set the fontUnderline and fontBold properties to true for all three controls. Also set the alignHorizontal property for the "Description" text to "0 - Left".

The canRender event will be set like it was in the Inventory report, for each of these controls.

   {||this.parent.firstOnFrame}

Next, for each of these controls set the suppressIfBlank property to true (otherwise the space reserved for these to print will still be reserved in succeeding detailBands, which is not necessarily what we want).

We want to place three "MyFieldText" controls under these and set the text codeblocks properly. However, these are going to be placed on the report in a different sequence than you might expect -- this is because we are using the lookupRowset for the Item ID field in two places.

We need to place the "Description" text first because this will force the lookupSQL to evaluate properly, moving the rowset pointer to where it needs to be. If we do not put this one first, we will have the Item ID (the first column of this part of the report) actually showing the previous row's Item ID (for the first line item we would see the correct value, but for an invoice with multiple line items we would see an incorrect Item ID for all but the first).

Place a "MyFieldText" control under the "Description" heading, and set the codeblock to:

   form.InvoiceRepDataModule1.lineitem1.rowset.fields["Item ID"].value

Remember we are using a lookupSQL, so the value that will actually display is the value of the description field in the Inventory table that matches the "Item ID".

Next, set a "MyFieldText" control under the "Item ID" heading (to the left of the description), and set the codeblock to the following. This is more complicated than the previous, because of the lookupSQL we defined in the data module. We need to actually display the Item ID, which we will not get unless we do the following (which backs up to the lookupRowset and gets the first field in the rowset, which is the Item ID, rather than the description). This looks like (there should be no line breaks when you put this in -- if you use the codeblock dialog it will wrap but ignore that):

   form.InvoiceRepDataModule1.lineitem1.rowset.fields["Item ID"].lookupRowset.fields[1].value

The last text control is easier. Place one more "MyFieldText" control under the "Qty" heading, and set the codeblock to:

   form.InvoiceRepDataModule1.lineitem1.rowset.fields["Qty"].value

For the Qty field, also set the picture to "999" (without the quotes), and the alignHorizontal to "2 - Right".

Once you have all three of these set up play with the widths and alignments so that they line up in a nice clean way.

Set the detailBand's height property to zero.

In the designer, the report should look like the following. Note that a) you are seeing detailBands for each row in the LineItem table (we haven't limited the data yet to a single invoice), and b) the canRender event handlers have not been evaluated in the designer, so the column headings are displaying where they shouldn't in the final version of the report.


Totals
In order to get the totals to work we need to use the group's footerBand.

In the inspector find 'group1' and then the footerBand object. Set the height property to 1 (1 inch).

Place a "MyFieldHeadingText" control at the top of the footerBand, and set the text to read: "Total Mugs at $10.00 Each:". (You will need to widen it a bit.)

Next, place a "MyFieldText" control under the QTY field in the detailBand. The text codeblock for this will be using the aggregate SUM function (again this is one line):

   this.parent.parent.agSum({||this.parent.rowset.parent.parent.lineitem1.rowset.fields["qty"].value})

(An explanation of this syntax is given in the Inventory report we did earlier, if you need a breakdown.)

Set the picture property to display up to three digits (we might have a customer who wants over a hundred mugs for something). This is done by going to the picture property for this text control and setting the picture to "999". Notice that the value is left aligned now -- it's looks better for numeric values to be right aligned. Find the alignHorizontal property and set it to "2 - Right".

Line this up with the Qty column above it to make it look good. .

Finally we need an invoice total. Place a "MyFieldHeadingText" control under the "Total Mugs ..." control, set the text to "Invoice Total:" and set the fontBold property to true. Work with the width and position a bit until it's where you feel it belongs.

Next, place a "MyFieldText" control under the previous total. Set the text codeblock to the following:

  "$ "+(this.parent.parent.agSum( {||this.parent.rowset.parent.parent.lineitem1.rowset.fields[ "qty"].value}))*10.00

This will place a dollar sign and a space at the beginning, take the aggregate sum and multiply it by the cost of the item (we will use 10.00) times the number of items ordered. (If you need more explanation of the "this.parent.rowset ..." part of the above, see the previous report where we did the same thing but for the "inventory" table).

Set the fontBold to true, alignHorizontal to "2 - Right", and tinker with the width and how it lines up with the other controls.

In a "real world" application, you would probably need to deal with sales tax (at least for most states), with shipping cost, perhaps store the price-per-unit in the inventory table, and so on. We'll leave that to you to figure out based on what we've done so far.

Set the footerBand's height property back to zero, and we're done with the layout portion of this report!

In the designer right now your report should look like this:


Save the report and exit the designer. Let's run it one last time to make sure it looks ok. Scroll through the invoices (something you won't be able to do when we are completely done), and check the Invoice Total -- make sure it looks good for all Invoices (when I was testing I came across one where the value was too large for the width of the text control, so it wrapped ... widening the control fixed that). When done looking at it, close it down.

One thing noticed while examining the data is that none of the customers seem to have ordered multiple mugs in a single invoice. You may want to go to the Invoice form, add a new invoice for a customer, and add more than one line item for the invoice -- just to see how the invoice handles multiple line items.

One Last Thing -- Code To Find the Correct Invoice ...
The problem with this particular report is that the data is not limited to a specific invoice number. Indeed, if you were to print the invoice report, you would get one page for each invoice in the invoice table. This is not exactly optimal.

What we need to be able to do is find just the one invoice and limit the report to just that invoice, or we will have a bit of a problem -- since the purpose is to only print one invoice from the Invoice form.

We are going to need to set up some code, but not in the report -- we need this code in the dataModule.

Bring the InvoiceRep data module up in the designer.

Click on the Invoice query object in the data module, and in the inspector, click on the onOpen event, and the tool button. We are going to execute some code after the query opens to limit the range, so that we are dealing with only the invoice number requested. This is going to be done by checking to see if the _app.InvoiceID property exists and if it contains a valid value. If so, we will set a "range" using the rowset's setRange() method to limit the report to just that invoice. Otherwise, we want to just ignore all this.

The code should look like:

            function INVOICE1_onOpen()
               if type( "_app.InvoiceID" ) # "U" and ;
                  type( "_app.InvoiceID" ) # NULL
         
                  this.rowset.indexName := "Invoice ID"
                  this.rowset.setRange( _app.InvoiceID )
               endif
            return

Save and exit the source editor (<Ctrl>+W), then close the Data Module Designer.

STOP! Before you do the following, make sure you copy from the final version of the code the file "preview.wfm" into the working folder for the tutorial. If this is not here, the code in the invoice form will crash and burn. The preview form here was lifted from an earlier version of the dUFLP, and is completely self-contained. A more recent version has been updated and relies on some code outside of the preview form, so we are not using the most recent version of this form. For your own applications, you may want to use the newer version of the form.

One way to test this is to run the Invoice form (which is how the user will be actually running the report), which will set the value of the _app.InvoiceID property. Run the INVOICE form, and make sure you have a customer with an invoice ... click on the "print invoice" button, and the preview form will appear. You can print it from here, or not. Notice that selecting the next/previous page buttons have no effect -- the report is limited to this invoice.

Create the Customer Statements Report

The next report we will create is going to generate a customer account statement for a specified range of dates. This report will need to get data from the Customer, Invoice, and Lineitem tables. In addition we need to group the report by Customer and, within each customer, by invoice date. The report will be called from a dialog form in which the user enters a range of dates for the output.

The Datamodule
Like before, we need to create a new data module that inherits from base.cdm

Save this as statements.dmd.

Next, right click the mouse on the empty design surface and select "Add Query Object from .SQL File" from the popup menu. We need to create a new SQL file, so in the "Create Query Object" dialog, be sure the "Create new .SQL query file" option button is true and click "OK".


The SQL Builder will open, and the "Selected Database" dialog will have focus. It assumes the current folder, instead click the down arrow for the combobox, and find the database (it has a special icon by it) for "dBASETutorial" and select it, then click "OK".


We need three tables, the simplest way to add them is to double-click them in the order we need them in the query we are creating. Double-click Customer.dbf, Invoice.dbf, then Lineitem.dbf, which will add these tables to the query, then close the dialog.


The purpose of the SQL Builder is to create specific types of more complex SQL statements. With the SQL designer we will be able to set specific options and the Builder will write the SQL statement. This is very useful if your knowledge of SQL (not to mention the time you have to learn it) is limited.

We will use the SQL Builder to select the fields included in the query, to set the relationships (or joins in SQL) among the tables, and to sort the data.

To select the fields that are needed for our query (and that will be available for the report) we can place a check in the field's checkbox. However, in this case we will select all the fields in each table, so we only need to place a check in the table's checkbox (the checkbox at the very top of each table object next to the "*" -- the wildcard that basically says "all fields"). When you do this for the second and third tables you will get a warning message about duplicate column names, click "OK", and continue.

The second step in creating the SQL statement is to set the joins between the tables. This is similar to the way it is done in the Data Module Designer, except you have to be careful to set the links to the correct fields.

Select the "Customer ID" field in the Customer table by single-clicking on it and holding the mouse down, drag it to the Invoice table's "Customer ID" field (and let go of the mouse). This creates a "left inner join" between the two tables on that field. If you accidentally selected the wrong field to join to, right click the line and select "Remove".

Next we need to create a join between the invoice table and the lineitem table. Repeat the instructions above, but for the Invoice table's "Invoice ID" field, dragging it to the LineItem table's "Invoice ID" field. This also creates a "left inner join" between the two tables.


The last step in creating the SQL for our report is to set the sort order of the rowset. From the Customer table, select the "Last Name" field, and drag it to the grid at the bottom. Click the "Sort Type" and select "Ascending", note that the "Sort Order" will automatically be set to "1". The primary sort order of the report will be alphabetical by last name.

Next, from the Customer table select the "Customer ID" field and drag it to the grid. Set the Sort Order to "Ascending". The "Sort Order" will automatically be set to "2". This secondary sort order is needed in case there are duplicate last names.

Finally, from the Invoice table, select the "Order Date" field and drag it to the grid. This way the invoices for each customer will print in date order.


Save this file as "statements.sql" and close the SQL Builder.

This single query object contains the fields from all three tables. But otherwise it is a rowset like any other rowset that we have already worked with.

Before we move on to create the report, we will the lookupSQL property for three fields and create two calculated fields.

We will start by setting up the lookupSQL properties we need. In the Inspector add a lookupSQL for "State ID", "Country ID", and "Item ID". Right-click on the field, select "Field Properties", in the inspector change the lookupSQL to the appropriate SQL statement below:

The next thing we must do is create the calculated fields, one for the price of each line item and the other for the Item ID.

Why do we want a calculated field that duplicates the Item ID field? In the report we want to display the both the "Item ID" and the "Item Description". But the "Item ID" field will be attached to a lookupSQL -- which returns the "Item Description". In this report we are going to create a calculated field that contains the Item ID and display that field on the report.

You may notice that this approach is different than that taken in the Invoice Report. This is being done so that you can see some alternatives.

In the Data Module Designer surface, select the "statements1" query object. Right-click the mouse and select "Table and Query Properties" from the popup menu. We need to create an onOpen event handler for the query and enter the code for our calculated fields.

Recall that the base.cdm data module already has a method for creating a calculated field. To use this method enter the following in the Query's onOpen event handler (click the tool button in the inspector):

            class::calcField("Item Price",10)
            class::calcField("Item ID2",5)

The first line will create the calculated field for the item price and the second will create the field for the duplicated Item ID. Save this data module, close the Designer, and then reopen the data module (this will activate the new fields -- the onOpen event handler has not fired at this time, so the Designer does not actually know anything about these two new fields).

When you re-open the data module in the Designer, scroll the grid all the way to the right and notice the two new fields there.

Find the "Item Price" field in the query, right-click it, and select "Field Properties". We need to add a beforeGetValue event handler for field. The code for this will look like:

            function ItemPrice_beforeGetValue()
            return (this.parent['Qty'].value * 10.00)

(This is really the 'extended price' for the individual item, we are multiplying the quantity sold by the price of $10 ...)

The grid will not immediately refresh, but if you scroll up and down you will see values fill in.

Do the same for the field "Item ID2" and enter this code:

            function ItemID2_beforeGetValue()
            return (this.parent['Item ID'].lookuprowset.fields[1].value)

This one is a little tricky, but if you follow the object path (the dots are separators between the objects and the properties), for this calculated field what you are looking at is:

Save the data module, and exit the Data Module Designer.

The Report
The customer statements report will have some similarities to the Invoice Report, but it will also offer a glimpse of some additional techniques. To begin, create a new report that is derived from the custom report in base.crp.

Change the text of the title to "Statement of Account", and also change the title property of the report to the same text.

Then drag the statements.dmd data module from the Navigator onto the report design surface.

You may want to reference this image for the layout of the controls for this report:


We will build this report starting from the "inside", working our way out, so we will place the detailBand objects first, then the group bands, and finally the group objects.

Place four MyFieldText objects in the detailBand of the report. The first MyFieldText object will display the Item ID, but, since we are using a lookupSQL with that field, we will use the "Item ID2" calculated field. The next MyFieldText object will display the Item Description so you must use the "Item ID" field here. This field's data comes from a lookupSQL. The third object displays the quantity of mugs ordered and the fourth MyFieldText object displays the purchase price of the item. This is the calculated field "Item Price".

For these fields make the following changes to properties:

Do not place any heading text objects at this point as they are going to be put in the group headerBand.

Set the height for the detailBand to zero (0) in the Inspector.

Next we will place two group bands on the report surface. These will be nested groups, so we have to be careful how we do these.

Set the groupby property for the group1 band to "Customer ID" and for the second group to "Order Date". (Remember to set the height for the group headerBands to something large enough to work in.) You can delete the text objects that says "Heading Text for Group1" and "Heading Text for Group2".

Next place two MyRectangleShapes, one in each of the two group headers. This is done to ensure that text objects will follow the Rectangle objects in the Z-order.

In the group2 headerBand (the "inside" -- closest to the detailBand) you should place a MyHeadingFieldText, with the text "Date of Order:" and a MyFieldText object inside the Rectangle. The MyFieldText object will display the "Order Date" field. This is all that goes on/in the rectangle. So you can modify the height as needed.

The next thing we need to do with this headerBand is to create the column headings of the four detail columns. Place four MyHeadingFieldText objects and two MyLine objects in the header band. The text properties will be "Item ID", "Type of Mug", "Quantity", and "Ext. Price". You can also change the alignHorizontal properties as needed.

Move the lines above and below the heading text we just added. This may be tricky -- make sure the top and bottom properties match for each line (meaning for the first line the top and bottom must be the same, for the second line the two properties should be the same). Otherwise they will be diagonal lines, even just a tiny bit off and they will start to look weird.

Once you have the line objects set in the group2 headerBand, set the height of the headerBand to zero (0). If the bottom line appears to have disappeared, you might want to set the height property to a specific size. In the example here I have set this band's height property to 1.05 (inches). Since the headerBand is not adjustable in height, meaning we don't have any controls on it that might or might not display, etc., we can leave it at a fixed height.

Now we will move to the group1 headerBand. In the rectangle, place two MyFieldText objects, one above the other. The first will contain the customer's name and the second will contain the customer's address. The code for constructing these controls should be placed in the canRender event of the Text objects. In fact, we have already written the code for these objects in the Invoice report. You may wish to copy and paste the code for these two objects. Note that you will need to change the names of the data module and query objects. That is, change this line:

   f = form.InvoiceRepDataModule1.customer1.rowset.fields

to this:

   f = form.statementsDataModule1.statements1.rowset.fields

The group1 headerBand will also display the date range for the orders contained in the report. Place two more MyFieldText above the rectangle in the group1 headerBand. We are going to place these object now, but their text will be taken from the user's input. This means that we cannot finish this element until we create the dialog form that will call this report.

Set the height of the group1 headerBand to zero, and readjust the controls a bit (compare to the planned output image from above).

The final band that we need to place visual controls into is the group1 footerBand. To do this you must switch to the Inspector, and in the combobox at the top find the following object (you cannot see this object on the design surface so you cannot select it with the mouse):

   form.streamsource1.group1.footerband.

Set the height property to 1.00. You should now see the footerBand on the design surface.

Place a "MyLine" object toward the top of the footerBand (for the example, I set the top and bottom properties to 0.05), and set the left and right properties to the ones for the two lines in the group2 headerBand above.

Under this line, add a MyHeadingFieldText object, setting the text to "Statement Total:", and the fontBold property set to true. To the right of this, add a MyFieldText and use the sum total (given below) for the text property code block:

   this.parent.parent.agSum({||this.parent.rowset.fields["Item Price"].value})

Set these properties: fontBold to true, the picture to "999.99", and the alignHorizontal to "2 - Right". Line up these two text controls so the total lines up under the "Ext. Price" above it, and the heading text lines up appropriately.

Finally add a MyHeadingFieldText object under everything else on the band, with the text: "Thank You for shopping with us.". Set the fontBold property to true, set the alignHorizontal to "1 - Center", and then line it up. Set the top property to 0.75.

Set the height of this footerBand to zero (0).

We are not subtotaling each order in this report, however, if you want a subtotal for the order you should use the group2 footerband. (For the tutorial we are not doing this, and leave it as an exercise for the reader.)

One last element we need to add to the report is a forced page break. We want each customer's statement to print on a new page. This is done in the group1 footerBand's onRender event handler:

            function FOOTERBAND_onRender()
               if this.parent.parent.rowset.atLast() = false
                  this.parent.parent.beginNewFrame()
               endif
            return

That should do it for the report layout.

The GetDates Form
The next thing we need is a form that can be used to set a date range for the report. Currently the report will print all the records in the statements1 rowset, however, we normally want to set a range of dates for which to print the orders.

In the Invoice Report we stored the "Invoice ID" in a custom application property (_app."InvoiceID") just before the report was called (see the method "PRINTINVOICE_onClick" in the Invoice form). Then in the data module we used the rowset's setRange() method to "filter" the rowset for that ID. In the current report the setRange() method will not work because we do not have an index. In fact, since we used an SQL join to create our query, we do not have access to any of our indexes. This is one of the drawbacks of using a complex SQL statement in a report.

One thing we could do is use the rowset's filter property in place of the setRange() method. In this case, however, we are going to use the canGetRow event of the rowset.

To proceed we need to create a form that can be used to get a date range from a user. We will then store those dates in an application variable and call the report. When the Statements1 query opens, the canGetRow event will use the application variables to decide whether or not the row will be added to the rowset.

First we need a small dialog form to get the date range of orders that the user wants included in the report. To do this create a new form derived (subclassed) from dialogform.cfm.

Set the text of the title text on the form to "Desired Date Range", and use the same text for the form's text property. Save the form as "GetDates.wfm".

Place two MySpinbox controls onto the form. Our MySpinbox control is rather generic so we need to modify the copies of the control after it is placed on the form. In the Inspector find the mySpinbox value property and click the "Type" button (the icon at the far right of the entryfield). In the combobox locate and select "Date". Do this for both spinboxes. Also we need to remove the picture property for the two spinboxes.

Name the first Spinbox: StartDateSB, and the second Spinbox: EndDateSB.

Next we will enter code for the pushbuttons. Select "Pbsave" button (with the text "OK" on it) and then click the wrench icon for the onClick event in the Inspector. The custom dialogform that our current form is derived from already has an onClick event for this pushbutton so you will be prompted with a message that tells you this event is "linked to a method outside the form;" and asks whether "you want to override it?". In this case click "Yes". We are going to replace the onClick event handler in the custom form with new code.

We need to do three things in this event. First we need to store the dates that are entered in the spinboxes to the application variables; second, we need to call the report; and third we need to do some cleanup.

            function PBSAVE_onClick()
               //  store dates to app variables
               _app.Start = form.StartDateSB.value
               _app.Stop  = form.EndDateSB.value
         
               // call report
               set procedure to preview.wfm additive
               set procedure to statements.rep additive
               fPreview = new PreviewForm()
               // to open with ReadModal()
               fPreview.bModal = true 
               fPreview.viewer.fileName := "statements.rep" 
               fPreview.Open()
               
               // cleanup
               form.close()
            return

The Cancel button will simple store empty values to the application variables and close the form:

            function PBCANCEL_onClick()
               // override method in super class
               _app.Start = {}
               _app.Stop  = {}
               form.close()
            return

When your user runs this form, enters some dates, and pushes the OK button, the date values will be stored in two application properties. These properties will then be available or visible to other objects -- including our Statements data module. We are going to use this functionality to construct the statements1 rowset.

Save the form and close the Form Designer.

Open the Statements.dmd data module the Data Module Designer. Select the Statements1 query object. Right-click the mouse and select "Rowset Properties" from the popup. In the Inspector select the "Events" tab and click the tool button for the canGetRow event. When the Source Editor appears, enter the following code:

            function rowset_canGetRow()
               local bYes
               bYes = false
               if type("_app.start") # "U"
                  if (this.fields['order date'].value >= _app.start) and ;
                     (this.fields['order date'].value <= _app.stop)
                     bYes = true
                  endif
               else
                  // if there is no data range we will print
                  // all records.
                  bYes = true
               endif
            return bYes

With this code the only rows that will be added to the rowset are the ones that match the condition. This will restrict the orders that print to those within our date range.

Save the changes in the Source Editor, save the Data Module, and close the Data Module Designer.

One last thing is needed to finish this report. Open the Statements.rep report file in the designer. We need to add the codeblock for the two MyFieldText objects at the top of the group1 headerBand. These text objects will display the start date and the stop date, so for the left text object enter the following in the canRender event:

            function MYFIELDTEXT3_canRender()
               if type("_app.start") # "U"
                  this.text = "From: " + _app.start
               endif
            return true

And for the text object on the right enter:

            function MYFIELDTEXT4_canRender()
               if type("_app.stop") # "U"
                  this.text = "To: " + _app.stop
               endif
            return true

Save the report and close the Report Designer.

To test this report, run the getdates form and enter some dates. The sample data that we are using has order dates covering the range 12/01/2016 to 12/31/2016. You can run your first test with these dates. The next time you should narrow the range so that fewer orders print.

We are now done with the reports we needed to create, but we have one last thing to do.

Create the Customer Mailing Labels

Normally I recommend against using the wizard to generate reports, but we are going to do mailing labels, and the one thing that the wizard does really well is mailing labels (and it is easier to have the wizard lay it all out than to do it yourself!).

In the command window, type the following:

create label wizard

This ensures that the wizard will be used. Follow along carefully and you will find that the wizard will do all the work for you.

  1. Get the table
    • In the "Look In" combobox, select "dBASEtutorial" database
    • In the list of tables, click on "customer.dbf"

    • Click the "Next >" button.
  2. Design the layout
    • Click the "Quick Address" button.

    • Click the "Next >" button.
  3. Sort order
  4. Select the labels to use
  5. Run the darn thing

It appears there's a blank record in the table ... the first label is empty! I am actually unclear what is happening as the data does not have an empty record, but there we are.

If you find that the labels are not "quite right", you can bring them into the designer, and tinker with the layout.

One thing that is fairly obvious is that there is space between the name and the first address field. You can leave it that way, or you can shift the text controls up in the designer. (We'll leave it that way for now, but ...)

Note that the wizard for labels (and reports) does not recognize datamodules, and does not use custom controls. You could bring the source code up in the designer and do a "replace all" on "new TEXT" and change it to "new MyFieldText", but again we won't do this here.

Close the label preview window, and you're done with reports and labels!

To Avoid Problems Later ...

To avoid problems with the custom report class being used in your other projects it's a good idea to clear the references before we proceed. Follow these simple steps:

You may want to repeat this for a form as well, as we did work with custom forms in this Phase of the tutorial.


  Back to the tutorial menu     Back to previous part of tutorial: Creating the Forms   Proceed to the next part of the tutorial: Creating the Menu