I can’t count the number of times that people have asked this question: „how do I show <page ##> of <total pages> in a report?“ Although this is standard practice for a word processor, for the reporting engine it is quite a bit more difficult, because the FRX doesn’t know how much data will actually be merged into its bands when you do a report run. The only way to do this seemingly simple thing is to have the REPORT FORM run through one iteration of the report „in its head“, save the resulting final _PAGENO, and then output the report for real.
Fortunately, you don’t have to print the report twice to get this information! I used to teach people to do this with the REPORT FORM ... NOCONSOLE keyword, but this form of the REPORT FORM command has proven to be unreliable with regard to pagination in VFP. Instead, you run the REPORT FORM, unseen, to a small window (the window does not have to be standard page size). This is quite fast; VFP doesn’t waste nearly as much time updating this small window as it does when you write to _SCREEN or file.
You make a REPORT FORM command go to a window simply by ACTIVATEing that WINDOW, even NOSHOW as you’ll see below. Although most people think that the REPORT FORM echos to SCREEN unless told not to, the real „home“ of the screen output is not SCREEN but WOUTPUT(), the current window of output, just like @... SAYs and other screen writing in Fox. Therefore its location is quite controllable.
The relevant code looks like this (you’ll find it in the T_O_C.PRG framing code that runs the two report forms in this summary report example):
DEFINE WINDOW x FROM 1,1 TO 20,20
ACTI WINDOW x NOSHOW
REPORT FORM t_o_c_1 ALL
* the first run should get us the
* number of pages and summary cursor...
* then:
PUBLIC rptNumPages
rptNumPages = _PAGENO
RELEASE WINDOW x
ACTIVATE SCREEN
This portion of T_O_C.PRG is the initial, unseen run. The variable declared after its REPORT FORM statement, rptNumPages is, of course the one that we will use in the actual report run to show the number of pages in the report. The expression showing the result in the report will look something like this (I’ll include the rest of the „pagecount“ elements in this expression for clarity):
„Page „+ALLTR(STR(_PAGENO))+ „ „+
IIF(TYPE(„rptNumPages“) = „N“ AND
rptNumPages > 0,“of „+ALLTR(STR(rptNumPages)),““)
After the unseen report run for total page numbers, the expression above prints properly in the second run of the report. The rest of T_O_C.PRG executes this second run and the starts another report, which contains information that we gathered in the first one, using methods I’ll show you now. Because we actually run T_O_C_1.FRX twice, you’ll see that I have to be careful not to gather this information twice. Not only could it spoil calculations in some cases, it would also take extra time for nothing.
I am also careful to RELEASE rptNumPages after I’m finished with it. You notice that made this variable PUBLIC, to make sure that all elements of the report can „see„ it. I mentioned earlier that variables created by the FRX itself are public, and they are, without exception. This is as good a time as any to emphasize that you must make quite sure that you use a different naming convention for report variables than for any other variables in your applications, therefore. This applies equally to variables you mark „release after report“ and those that are not released.
To return to the „main event“ of this report, the figure below shows you a group event dialog that uses the trick with which I gather the table of contents information:
A Group Header band calls a DE.RemoveObject() method
The group header entry event calls the Data Environment’s RemoveObject( ) method, using that public reference we talked about earlier.
Why does the group header call a RemoveObject( ) method? Well, the real question is, why not stash code in there? Can you think of another reason for which you’ll need the DE.RemoveObject() method?? At any event, here is the straightforward code I’m using in that method. The outer IF/ENDIF construct simply makes sure that I don’t add records to the „table of contents“ cursor when I’m on the second report run:
DataEnvironment.RemoveObject( )
LPARAMETERS cObjectName
NODEFAULT
IF TYPE(„rptNumPages“) # „N“ OR ;
rptNumPages = 0
LOCAL lcAlias, lcToken
lcAlias = ALIAS()
IF NOT USED(„t_o_c“)
* first page! -- you could do this beforehand
* if you prefer
CREATE CURSOR t_o_c (chapter c(40), ;
startpage c(10))
SELECT (lcAlias)
ENDIF
lcToken = EVAL(lcAlias+“.Token“)
INSERT INTO t_o_c VALUES ;
(PADR(IIF(ISALPHA(lcToken), ;
„Tokens beginning with ‘“+LEFT(lcToken,1)+“’“,
;
„Miscellaneous Tokens“),40,“.“), ;
PADL(ALLTR(STR(_Pageno)),10,“.“))
* I’ll store the STR() version of _
* PageNo since I“m going
* to use it in a report anyway and
* otherwise would have to convert it later
ENDIF
RETURN „“
You see the results in the next figure. I will talk about the concept of „explainable“ documents in my other session, because the more ways you can display information and help the user navigate information levels, the more successful your report will be. But even this simple example shows you how you can gather information not shown in the current report run, to good advantage, while you run in a report. Consider picking up indexing information and _PAGENOs, along with a table of contents, for example, or storing report variable sums re-set on group into a cursor, for use in a chart after the report run finishes.
The balance of this section will offer a few more tricks that manage runtime REPORT FORM command execution in unusual ways.
T_O_C_2.FRX is a simple report to show the results we gathered into the T_O_C cursor during REPORT FORM T_O_C_1 processing.
A table of contents, total page number, and index information are perfect examples of material that must be collected during a report run. Their content is dependent on the current output target (size of paper, etc). But, very often, you can collect information before you run a report that can materially improve the speed of REPORT FORM execution.
This idea is really an extension of what we’ve been doing with reporting time since we started to work with SQL queries in Fox. Massaging information from numerous related files into flat cursors that held appropriate data in the proper format for output before beginning the report often makes the report easier, and faster, to execute.
Because the Fox reporting engine is a „one pass“ engine, people often have difficulty showing summary results in the group header. It’s easy to show them in a group footer, since you will have been able to accumulate those results while the reporting engine moved through the records of the group, but when you’re in the group header your report variables have not yet been calculated.
„Percent of total“ calculations is one common reason why people need the results of the entire group before they start printing the group. In this scenario, each detail line must show what percent of a total amount they represent to the group as a whole. To achieve this result, many people use a UDF called in the group header, to scan through all the detail records of the group. They calculate a total and then move the record pointer(s) back to where they should be, for the start of the group, before the FRX begins moving through detail band instances.
Instead, consider a cursor GROUPed BY your group control break condition, which you create before you begin your REPORT FORM execution, and containing any summary calculations you need for each group. (If you have more than one significant grouping level, you can use one cursor for each level.) Here’s an example from the examples in my other session notes (the group break is on Juggler.Name in this case):
SELECT Jugglers.Name, ;
SUM(Jugglers.Points) AS SumPoints ;
FROM Jugglers ;
GROUP BY Jugglers.Name ;
INTO CURSOR Group1
* order the grouping info
INDEX ON Name TAG Name
SELECT Detail
SET RELATION TO Name INTO Group1
As you see, once the summary cursor is created, a normal Xbase relation suffices to make sure the appropriate total information for each group is available as you move through the groups of the report.
There is one extremely good reason to manipulate record pointers within a report: when you need to stop it in a hurry. The following technique, which you will find in your source code as INTERRPT.PRG, can be used to end a report prematurely:
WAIT WINDOW NOWAIT „Interrupting report!“
IF NOT EMPTY(ALIAS())
GO BOTTOM
ENDIF
RETURN
Syntax like this can be attached to an ON ERROR routine before the report run. It is often extremely difficult to get your normal error handler to behave itself during a report run – and, in addition, a simple handler like this may work better in situations where you are incorporating reports of an unknown origin into your application. (Perhaps you are simply reading the names of all available FRXs in a directory into a picklist, or using another data-driven method to make reports available, as we’ll discuss below.)
You can also set a flag or report variable value in response to some error conditions, either specific to a report or general, and then check the value of this variable in a report event. Should you find that the „interrupt flag“ was set, you can then use the syntax above to quickly bring the report to a conclusion. The grand totals won’t be accurate, of course, but at least you won’t have wasted two reams of expensive letterhead!
I really hope that the sessions are not entirely taken up with questions in this category, but from past experience I have to admit that „where you go with your report“ is probably the knottiest part of VFP/Fox GUI reporting. So here we go...
Printers, and printer management, and Windows control of printer management, create a large percentage of the problems. The most common problems revolve around the incompatibility of particular reports with the current printer on the user’s machine, or the incompatibility of the report format as a whole with certain non-standard page sizes or other printing requirements.
If you have non-standard page size requirements, I think the best thing to do is to avoid the VFP Report Writer altogether, frankly. It is still possible to drive the FoxPro DOS report engine through the VFP REPORT FORM command, if you have 2.x DOS FRXs with no Windows platform objects in them. It is even possible to use a special character-format PREVIEW with DOS FRXs in VFP, but this seems to be a tremendous performance hit and I don’t recommend it. If you are using a DOS report, you might as well REPORT TO FILE and MODIFY COMMAND <filename> to preview the report, as we’ll do using VFP reports in this section.
Microsoft recommends a number of other techniques you will want to investigate in the Knowledge Base for doing raw, DOS-type printing under Windows 95. If you have a huge investment in reports created with @... SAY or ?/?? techniques that you can’t easily port to VFP FRXs, you will want to investigate the following articles:
I also suggest you get to know the SET PRINTER FONT command to set your output to a default font. If you are getting double-spaced output, for example, this would be a sign that VFP is trying to „think“ in a font that is too large for your linewidth, as either VFP or your printer calculates it.