Using Report Writer

If you're like me, and I know I am, you were horrified the first time you realized there's no "print query" button in FoxPro.

Since then, you've probably copied your queries to a spreadsheet, then to a word processor, or found some other way to get your results on paper. If you found your way into FoxPro's Report Writer, there was no way to guess how it worked, and you gave up.

Truth is, once you learn a little about Report Writer, you may forgive its daunting first impression.

Not only can you print the same query you see in the browse window, you can improve on it by adding titles, changing your column headers to English, setting off groups with summary information, even calculating new fields and using functions to determine what's printed under what circumstances.

As an introduction to Report Writer, let's make a report on night home burglaries in the Phoenix area using the retas.dbf file in the NICAR ftp site (ftp://nicar.org/pub/data/retas.dbf). Run this query to get the population and the number of night, day, and total home burglaries for the year:

SELECT agency, pop, burgrnt + burgrd + burgru; AS homeburg, burgrnt AS night,; burgrd AS day; FROM Retas; WHERE msa = "662"

To make this query work in a full Return A Supplement file, you'd have to sum the fields and group by agency because it would be broken down by month. This sample data includes only one month.

Now, choose Run Wizards Report, and pick Report Wizard from the Wizards menu.

(Experiment with the other Report Wizards also when you have appropriate data: multi-column reports will help you fit long, skinny queries on a single page; Group/Total is good for multiple groupings where you want to set off and summarize the groups)

In the dialog boxes that follow, pick "Query" as the table to use, Ledger style, add all fields, and horizontal layout. Enter "Night burglaries" as the title. Hit the "Preview" button, click on the preview page to zoom in, and you'll see that without much effort you've recreated the look of your query browse window, and you even have a nifty title at the top.

If you're satisfied, you could just close the preview window and "save and print" the report. To sample some of the real power of Report Writer, choose "Modify with Design Tool" and "Finish."

If you've ever used a graphics program or FoxPro's screen builder, you'll have an easier time with the report design screen. Everything you see is an "object" that can be created, dragged, resized or manipulated with the tools in the toolbar.

To see what I mean, click on the date field just under the title to select it - you'll see sizing handles appear around it. Hit delete and it disappears. Click the text tool - the capital A - and then click in the same area, and type "Phoenix metropolitan area, January 1993." Now, click on the arrow tool - the "object picker" - and select the text you just typed. Choose Object Font. Experiment with different font sizes and types for the title.

If it's not lined up right, drag it into place. Use the text tool to change the column headings to English and change the font if you wish. Note that everything in the top Title Bar prints once, at the top of the first page, while the Page Header band prints under the title and atop any further pages. The Detail band prints for every record in your query. To make more room in a band, drag the buttons to the left of the band names. Notice that space is always added at the bottom - can't directly add space at the top. To work around this, add room at the bottom, then double-click on the bar under the band, which will select all the objects inside. You can then drag them en masse to the bottom, giving you room at the top.

To cut down on white space or save room, you can shorten the detail fields by dragging the sizing handles. Try shortening the Agency field by about half.

To make sure you're not truncating anything, double-click the field box to bring up the Report Expression dialog. Make sure the "Relative to Top-Field can stretch" option is checked (the Report Wizard should have automatically done this for you), and preview again. You should see the agency field shortened, and the few long names are wrapped to another line.

If you want some practice in dragging objects, follow up by resizing and dragging the other fields and headings to the left to take advantage of the space. The Report Snap to Grid option should be checked to help you line things up - you may also use the arrow keys to move the selected object in small increments.

The Ledger format we chose puts a grid around the fields: you can drag these grid bars to accommodate the new sizes - or delete them. To draw these yourself, use the Rectangle tool to put a box around all the detail fields, then the Line tool (it looks like two intersecting lines) to draw vertical cell lines between fields.

The Field tool - a little box with ab inside - can be used to add a whole new field to the report. Let's try adding one that calculates the percentage of night burglaries among those in which the time is known.

Click on the Field tool, then in the Detail band where you want to place the new field. That brings up the Report Expression dialog box. Type this into the Expression box: night/(night+day)*100

Click OK to close the dialog, and you'll see your new field. Use the Text tool to type a percent sign just to the right of it. Choose Report Page Preview to see the results.

Now, to print the report, you have to run the query again, with this additional tag at the bottom

INTO CURSOR Query REPORT FORM query.frx TO PRINTER NOCONSOLE

substituting the name of the frx file where you saved the report. If you used RQBE to make the original query, you set the output to Report/Label and check Options to bring up a dialog box where you can browse to the frx file you made and print the report.

You might instinctively try File Print from the design screen, but if you do that, you'll print the code rather than the report. Remember, you're not designing a printout, you're designing a template for a printout. I guess this is meant for businesses who want a standard report form for an ever-changing database.

As you experiment with Report Writer's capabilities, here are a few of pitfalls to watch out for:

You can make reports from joined database files rather than starting from a query. But watch out - the Report functions are not as sophisticated as SQL SELECT. You have to index and set the order of the linked (child) table to the joined field or you could get incorrect data.

When you use functions to define a print field, as we did in the exercise, you can't mix data types. The whole field has to be character or numeric, or you get an error. So if you wanted to add the percent sign directly to the field above with +"%", it wouldn't work unless you first converted the number to a parsed character string with STR(ALLTRIM(numeric_function)

If your data won't fit onto one page horizontally, and the space-gaining tricks above don't make enough room, you may pine for Excel's "Fit to Page" print option. Alternatively, you can set a smaller font size for your detail and header bands by double-clicking the bands to select everything, then choosing Object Font.

Tim Henderson can be reached by e-mail at thenders@gannett.infi.net