Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
THE VISUAL FOXPRO REPORT WRITER

Purpose for this Article
During the past several years I have written many applications in Visual FoxPro and as you all know the application is not finished until you get those darn reports for the client completed. There are times mentioned on the forum, that some wish the VFP report writer was more flexible. I can tell you there have not been too many times I have had to resort to using another report writer. This article deals with common every day issues that arise when using the Visual FoxPro report writer. Many of these questions have been asked on the forum and answered, but thought an article on the report writer would help. For the most part, the information contained in this article is information I had obtained from other sources and retained for my own use with reports.

Can I Provide the User Access to the Report Writer at Runtime?
For the most part, this is no more difficult than executing a MODIFY REPORT command from within an executable. Too often, however, the report designer will appear correctly, but the related toolbars are nowhere to be seen. The solution turns out to be quite simple. To understand it, keep in mind that the current toolbar status - that is, the visibility and location of the toolbars, and whether they are docked or floating - is stored in the VFP resource file. The resource files by default, are named FOXUSER.DBF and FOXUSER.FPT and can be found in the directory from which you launched the application. So here are the steps needed to ensure that the report designer toolbars are always visible at run-time:


- Launch Visual FoxPro. Close any toolbars that you don't want the user to see at run-time, such as the standard toolbar.
- Open the report designer. Go to View/Toolbars and manually open the two main report designer toolbars (Report Designer and Report Controls). Move these to a suitable location on the screen. If you wish, you can also open the Layout and Color Palette toolbars at this point, but this is not essential as the user will be able to open these from the Report Designer toolbar.
- Using Windows Explorer, visit the directory from which you launched VFP. Look for FOXUSER.DBF and FOXUSER.FPT, and make them read-only. To make a file read-only, right-click on it, select Properties, and enable the Read-Only checkbox.
- Quit the report designer and close VFP.
- Copy the two Foxuser files to the directory on the user's system from which your application will be launched.
- Launch the application on the user's system. When you open the report designer from within the application, you should see the toolbars just as you left them.

From now on, the user will be able to move the toolbars around, dock and undock them, and even close them. But because the resource file is read-only, these settings won't be saved when the application exits, so the toolbars will always revert to their initial visible status when the application starts.

How Do I Maximize the Preview Window at Runtime?
This question comes up frequently in the forum so here so the way this is done:
KEYBOARD "{ctrl+f10}" 
REPORT FORM MyReport PREVIEW 

This will insert Ctrl-F10 into the keyboard buffer just before the Preview window opens. This in turn maximizes the window.

How Do I Create a Custom Preview Window for Reports?
This is quite simple to do, see code example below:
DEFINE WINDOW rptview FROM 2,1 TO 30,80 ; 
SYSTEM TITLE 'Report Preview' ; 
NOCLOSE FLOAT GROW ZOOM ICON FILE 'printer.ico'
KEYBOARD '{CTRL+F10}' && Optional to force window full screen 
REPORT FORM Rolodex PREVIEW WINDOW rptview 
RELEASE WINDOW rptview


How Do I Block Errors if a User Tries to Print a Report with no Printer Drivers Installed on their Computer?
One or more run-time errors will be generated whenever the application tries to perform a printer-related task. The errors will typically be "Printer not ready", "Error loading printer driver", or both. They will occur when the application tries to print a report, and also when you execute SET PRINTER, GETPRINTER() and several similar commands and functions. You can, however, successfully preview a report. To prevent these run-time errors, test the value returned from APRINTERS() before performing a printer-related task. If this value is zero, it means that no printer drivers are installed.

In view of the previous answer, is it possible to disable or remove the Print button in the Report Preview toolbar?
To remove a button from any of VFP's built-in toolbars, open the toolbar in the development environment, hold down the Alt key, and drag the button off the toolbar. This setting is saved in the resource files (FOXUSER.DBF and FOXUSER.FPT), so you would have to copy these files to the user's system in order to enforce this setting.

How Do I Include Page Numbers in my Report, in the Form of, "page x of y", where y is the total page count?


The only way to do this is to run the report twice, like this:
nTotalPages = 0 
REPORT FORM MyReport NOCONSOLE
nTotalPages = _pageno
REPORT FROM MyReport TO PRINT NOCONSOLE

The first pass won't actually print anything (because there is no TO PRINT clause), but it will place the total page count in _pageno. To print "page x of y", the report should contain an expression such as the following:
"Page " + ALLTRIM(STR(_pageno)) + " of " + ALLTRIM(STR(nTotalPages))


I am using the Microsoft Web Browser control to display HTML pages. How can I print these pages?
The Web Browser control does not have a Print method. You can, however, use its ExecWB method to print the current document. In general, this method will execute any command which is normally available in the browser's user interface. Your code will look something like this:
THISFORM.WebBrowser.ExecWB(6,1)

The first parameter, 6, tells the method that you want to print. With the second parameter set to 1, the browser will display a Print dialog (like the one in Internet Explorer). To inhibit this dialog, set the second parameter to 2.

How can I select the upper tray of a printer for the first page of the report and the lower tray for the remaining pages?
Create two versions of the report. Use the Page Setup dialog within the report designer to establish the paper source: upper tray in one version, lower tray in the other. Then issue REPORT FORM twice, using the RANGE clause to select the page numbers:
REPORT FORM MyReport_Ver1 RANGE 1,1 NOCONSOLE 
REPORT FORM MyReport_Ver2 RANGE 2 NOCONSOLE 

This will print page 1 of MyReport_Ver1 and page 2 to the last page of MyReport_Ver2.

How do I Display a Report in the Preview window, but still open the Print dialog when the user clicks on the Printer button in the toolbar?
Try this:
REPORT FORM MyReport TO PRINTER PROMPT PREVIEW


I would like to use the report designer to generate invoices. Some of these will extend over multiple pages. How can I arrange for the invoice total always to appear at the very bottom of the last page? If I place the total in the summary band, it gets printed just below the last line of detail. If I place it in the page footer band, it is printed on every page.
Place the total in the page footer band, below any other fields or labels that are already there. In the report expression dialog, click on the Print When button, then type this expression in the Print Only When Expression Is True box:
RECNO()=RECCOUNT() 

This will cause the totals to be printed only after the last record in the file has been processed. This assumes that you are using a single table or cursor as the input to the report, and that it is not indexed. Because of this, you are better to use the SQL-SELECT command to prep the data into a cursor and use the ORDER BY clause to set the ordering first, then use the cursor as the source for the report.

How do I let Users Choose Printer options in VFP Reports?
You probably know that, when designing a Visual FoxPro report, you can select the page size, orientation and paper source from the Print Setup dialog. This is the dialog you reach when you choose File / Page Setup and then click on the Print Setup button within the report designer.
 
But there's a snag. If you make any changes to the print settings in this way, your users will not be able to override them at run time. For example, if you set the paper source to "Upper tray", the report will always try to select that tray. You cannot use the REPORT FORM ... PROMPT command to override it at run time, because the PROMPT dialog does not include a setting for the paper source. (But you can use the PROMPT dialog to select a different printer.)
 
Nor will SYS(1037) help. Normally, that function displays the Page Setup dialog and lets the user change the settings within it. The changes will then affect all printed output for the current session. But if you changed any of the default page settings within the report designer, the SYS(1037) dialog will have no effect on that report. You cannot even use it to send the report to a different printer.

Fortunately, there is a workaround. It involves a little gentle hacking of the report file. As you might know, the internal format of a VFP report file - that is, the FRX file - is the same as that of a DBF. It follows that you can open the file just as if it was a FoxPro table. You can also browse it and edit it. Now, you don't need us to tell you that editing a report file in this way is hazardous. If you changed the wrong field to the wrong value, you could ruin your carefully designed layout. So take care with what follows - and be sure you have a backup before you start. The first step is to get rid of the printer settings which were saved in the report designer. To do so, open and browse the report from the Command window. For example, if the report is called Customer, type the following:
USE Customer.frx
BROWSE

In the Browse window, look for a record where the Objtype field is set to 1 and the Objcode to 53 (it is usually the first record). In this record, look for the Expr, Tag and Tag2 fields. These are memo fields which contain the settings you made in the report designer's Print Setup dialog. Tag and Tag2 contain binary values. Expr contains plain text, which will look something like this:

DRIVER=WINSPOOL
DEVICE=HP LaserJet 6P/6MP - Enhanced
OUTPUT=LPT1:
ORIENTATION=0
PAPERSIZE=9
PAPERLENGTH=2794
PAPERWIDTH=2159
COPIES=1
DEFAULTSOURCE=4
PRINTQUALITY=-4
COLOR=2

Now, go ahead and delete the contents of these three memo fields. This will put all the saved settings back to their defaults. If you want to let the user pick the page size, paper source or orientation at run time, you can now call SYS(1037) to do the job. But what if you want to let the user change some of the settings but not others? Or you want to store non-default settings in the report, but you want to let the user override them in certain circumstances? Calling SYS(1037) won't help, because it is all-or-nothing. You can't use it to let the user change some settings but prevent them from changing others. In any case, the function won't work if you have made any changes at all to the settings within the report designer - even if they are different from the ones you want to let the user adjust.
Here's what you do. Let's suppose that you always want the report to be printed in landscape orientation on A5 paper, but you want to let the user toggle the paper source between "Manual feed" and "Lower bin". Start by selecting the orientation and page size in the Print Setup dialog in the report designer. While you're there, set the paper source to whatever you want the default to be. Save the report as usual. In your application, give the user a mechanism for toggling the paper source. This could be a command button, a check box, a menu command or whatever. When the user makes the choice, execute code similar to the following:
USE Customer.frx
LOCATE FOR Objtype = 1 AND Objcode = 53
REPLACE Tag WITH "", Tag2 WITH "" 
IF "DEFAULTSOURCE=2" $ Expr 
   lcNew = "DEFAULTSOURCE=4"
   lcOld = "DEFAULTSOURCE=2"
ELSE 
   lcNew = "DEFAULTSOURCE=2" 
   lcOld = "DEFAULTSOURCE=4" 
ENDIF 
REPLACE expr WITH STRTRAN(Expr,lcOld,lcNew)
USE

The next time the report is printed, it will select whichever paper source the user chose. Here's what the code does.
First, it opens the report file as a table and locates the record containing the settings. It then deletes the binary values of the settings. That's no problem - the report will run fine without them.
Next, it checks the existing setting of the paper source. If it is 2, it changes it to 4 and vice versa. As you might guess, this setting is specified by the DEFAULTSOURCE value: 4 means "Manual feed", 2 is "Lower bin". Finally, the Expr field is updated with the changed value and the file is closed.
How did we know what values to use for DEFAULTSOURCE? From the help screen for the PrtInfo() function. The PrtInfo() function, which returns the settings for the current printer, uses the same values as the Expr field. So if you want to understand the contents of the field, that's where to look.
When you distribute your application, be sure to exclude the FRX file, and its associated FRT file, from the build. In other words, do not bind these two files into the EXE file. If you did, the above code would not be able to update the files and your user would see a run-time error. Instead, just copy the files in, or distribute them with your installation setup.

I will be writing other articles when I have the time on the Visual FoxPro report writer. Other articles will include:
- Using UDF() in reports
- Understanding memory variables in reports
- Using Crystal Reports from within Visual FoxPro

I rely heavily on the Visual FoxPro report writer, but at times just have to use another 3rd party report writer. This is not often, but when I do, Crystal Reports is the one I use. So in one of my articles this will be covered. Happy report writing from Foxite!

Download code
You can download a related sample program here. The sample is using the RichText control to save RTF fonts into a VFP memo field and print it using MS Word automation. The download is a zipfile. Its size is 11.199 bytes.

ABOUT THE AUTHOR: PETE SASS

Pete Sass Pete has worked in the computer world since the late 70's. He loves the outdoors and lives in a small town called Marathon, in the north of Canada.

FEEDBACK

Irshad noorudheen @ 11/22/2006 4:52:53 PM
i need to know how i can print a perticular page from the report preview.

and also i experienced that if i use for command
ex: report form myreport to prievew for name = test1.value .... its not orking
only for numerical values its working
is ther any way that i can use a charecter data type to specify a particular page

thank you

burt @ 2/20/2007 9:41:40 AM
how can i sort or even search by(for example Table 'member',Field 'memnum')from a form to the report???what could the code be?
please do send the code to my email burt_sijo@yahoo.com

Tori @ 11/28/2007 4:59:45 PM
I have the same question as Burt above. When using the report writer, how can I search for the appropriate code for corresponding fields on a form? Any help that you can send me send to mpc-collections@byu.edu.

Joe Bonetti @ 12/16/2007 5:55:23 PM
Check this report writer at: http://www.dfarber.com

John Sturdy @ 1/28/2008 8:24:32 PM
Thx Pete

I found the information really useful. Several of your tips like hacking the printer settings and recno = reccount() were exactly what I was looking for.

oliver @ 3/2/2008 3:47:49 PM
I am making a journal of accounting entries/transactions. how do i insert the recapitualtion of all entries/accounts used in the journal at the end of the report/journal? using vfp9.0. tnx

irfan habib @ 3/21/2008 7:21:12 AM
thanks for ur support , and very easy examples ! i need ur help regarding report

i design my report in visual foxpro report writer, my report run smooth and quickly

but i need this report on ms-word with complete actual formating.


i am very thankfull for ur support

Lin Outten @ 7/30/2008 9:24:51 PM
Thank you. Your information is excellent. I tried to download the sample program, but had trouble opening the samples reports. We are using foxpro 6.0 and I am trying to view some sample reports for 9.0 to make a comparison. Do you have any suggestions.

Dennielle @ 8/18/2008 9:22:18 PM
This could be my last resort!!! lol

I am trying to generate a report. When i do this, I would like the entire report to show up (width wise). Everytime I run the report I only get half the page, how would I get the entire width of the page to automatically show up so that I don't have to maximize the window everytime? I hope this makes sense.

Hector Martinez @ 10/18/2008 12:21:08 AM
I´m trying printing one additional copy of my report. Actual report are printing in half letter papel length, I want additional copy of the same data in the other half letter of page for completing the letter page.

I´m using visual foxpro 6.0 report form working in an XP windows systems.

Have you any ideas about solving this situation?
Thanks.

AL @ 11/19/2008 6:02:05 PM
Hi:



My name is Al, I got you contact info from the internet while trying to search for a resolution to a problem I am experiencing. I am trying to print a report that prints to an okidata ML 421 9 pin printer. I have 2 issues that I can't resolve myself.



1) The report prints to letter size 8.5 x 11 perforated sheets of paper. The actual page size is 8.5 x 6, I am having difficulty controlling the page advances the printer. The report is treating it as a true 8.5 x 11 sheet of paper, whenever the report overflows a page full page advance occurs.



2) The report prints very slowly, should I use courier 10cpi as my default font. I think the printer should go to UTl setting but for some reason the printer goes to NLQ and prints slowly.



Any suggestions would be greatly appreciated.....





Thanks

Al

Hasan Khan @ 12/14/2009 11:10:39 AM
how to send my report into pdf file

VIJAY KANT SHARMA @ 1/7/2010 6:23:33 PM
SAME PROBLEM
´m trying printing one additional copy of my report. Actual report are printing in half letter papel length, I want additional copy of the same data in the other half letter of page for completing the letter page.

I´m using visual foxpro 6.0 report form working in an XP windows systems.

Have you any ideas about solving this situation?

iraj @ 5/3/2010 7:52:09 AM
hi
every thing ok when i can change my lab .prg from dos to win xp sp2 with vfp9
but when i began to report .........
my report was 14 report and they can be reported all or one ....
but now i have one report with 14 optional band with heder and footter
i use 8.5 hight tractor feed page for epson lq300
one problem is end of page has to be preinted but i have 6 line white portion not printed and it is not good for report because with page heder i have long white portion in my detaile report from one end page to another page header with stick pages. i change hight of details heders but problem remainse.
thanks

Anubrata @ 7/12/2010 9:49:57 AM
I have to print max.7 records each per page in a Invoice (generated with VFP 6 Report) and have to print summary only at last page. Pls help me in this regard.

Mujeeb Ahmed @ 8/24/2010 1:42:27 PM
How can I print a report in landscape format



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: