Walkthrough: Creating Reports with Visual FoxPro

Article
07/09/2007

You can design and create reports to provide clarity and readability for data, transforming it from developer data to user information. Reports are ideal for displaying and distributing data because you can easily repeat, duplicate, and share them.

This walkthrough shows how to create Visual FoxPro reports using the Report wizard to quickly get a report of any single or related tables. If you want to customize your report, or add non-data elements, use the Report designer.

Creating a Single-table Report
Creating a Multi-table Report

For more information, see Working with Reports.

Creating a Single-table Report

If you only want to look at what you have, use the Browse window on any table.

To access the Browse window

From the File menu, open the table.
On the View menu, select Browse.

If you want to print or mail a report quickly, you can export the file as text, and then print or mail the new text file. The Export dialog box options make it possible for you to select portions of the table to convert to text. (Remember to check the paper orientation for wide tables.)

To report raw data from a table as text quickly

On the File menu, click Open.
In the Open dialog box, select Table in the Files of type drop-down list, locate the table you want, and click OK.
On the View menu, click Browse.

This displays the selected table. At this time, in the IDE, you can change the column widths or locations, or you can make lasting changes from the Table menu.
On the File menu, click Export.
In the Export dialog box, select Delimited Text from the Type drop-down list.

If you were going to use the table in another application, such as Microsoft Excel, you could select the application from the list, and the table would be converted to the proper format and saved with the correct extension.
Click To, and enter the file name of your new file, including the extension.
In the Field separator drop-down list, select the delimiter you want in the new file, and then click OK.

Visual FoxPro Report Wizard

If you find you must have formal presentations, summaries, or calculations, or you must use the same information repeatedly, then use the Report wizard or the Report designer to generate reusable, updatable, modifiable reports.

The Report wizard prompts you with choices that make it possible for you to create a well-formatted report of one or more tables quickly. In many circumstances, these quick reports work very well.

To use the Report wizard

On the File menu, click New.
In the New dialog box, select Report.
Click the Wizard button, and select the type of report you want to create.

In the wizard, you can preview a report before you save it. In addition, you can save it and then open it in the Report designer to use the Visual FoxPro IDE to add to your report design.

To experiment with the Report wizard, use the Labels.dbf table, which ships with Visual FoxPro. Use the following procedures to work with simple, one-table reports

To create a simple Report using the Labels.dbf table

On the File menu, click New.
In the New dialog box, select Report and then click the Wizard button.
In the Wizard selection dialog box, double-click Report Wizard.
In the Report wizard, select Free Tables in the Databases and tables drop-down list box, and select or locate the Labels table.
Select any or all of the available fields, and continue the wizard, making the appropriate selections up to Step 5.
In Step 5, choose a report style and orientation.

In addition, you can click Summary options to access five functions — Sum, Avg, Count, Min, and Max — for fields displayed in your report. The selected functions are added automatically with appropriate labeling and positioning.
In Step 6, click the Preview button to see the report your selections would create if you click Finish. You can print the preview without creating a report file.

The Report wizard creates labels and selects fonts, font sizes, indentation, and other styling and formatting characteristics to help turn the table data into report information. Often, creating reports with the wizard is as fast as creating the ad hoc Browse window copy, and the results often look much nicer.
Click Finish.

For more information, see Report Wizard and One-To-Many Report Wizard.

Visual FoxPro Report Designer

You can use the Report designer to create simple reports, called Quick Reports. In addition, the Report designer is a powerful tool for creating formatted reports. The main difference between using the Report wizard and using the Report designer is that the designer provides greater control of the positioning of data and other content.

To create Quick Reports with the Report designer

On the File menu, click New.
In the New dialog box, select Report, and click the New File button.
On the Report menu, click Quick Report.

If you have a table open, Visual FoxPro will use it as the source for your report. If no table is open, Visual FoxPro displays the Open dialog box, so you can choose a table.

The Report designer populates the Detail and Page Footer bands. Then, the details are up to you. You have more control than with the Report wizard; you also have more responsibility. If you want to generate information, such as sums, averages, or counts, use Data Grouping on the Report menu. Your specification for a data group can be as simple as the name of the index field selected from the Expression builder.

To include a calculation in your report, open an ordered table (indexed on cust_id), create a Quick Report as basis, specify data grouping, and then specify how and where in the report the calculation is displayed. Finally, preview, print, and save the report.

To create a report containing a calculation

On the File menu, open the ..\samples\Data\orders.dbf table.
On the Window menu, click Data Session, and click Properties for the orders table. In the Work Area Properties dialog box, choose Orders.cust_id as the Index order, click OK, and then close the Data Session dialog box.
On the File menu, click New. In the New dialog box, select Report, and then click New File.
On the Report menu, click Quick Report, and then select vertical layout. Click the Fields button, select the fields you want on the report, and click OK. Click OK in the Quick Report dialog box.

Note

For this example, you need the Order_ID, Cust_ID, and Order_amt fields.
On the Report menu, click Data Grouping. In the Data Grouping dialog box, Click the Add button and in the Fields list in the Expression Builder, double-click Orders.cust_id. Click OK.
In the Detail band, make a copy of the order_amt field, drag the copy to the Group Footer band for group totals, and open the Field Properties. On the Calculate tab, select the Sum function from the Calculate Type drop-down list box.

Note

The Reset based on drop-down list box displays the default selection, Order.cust_id field. This setting means that the Sum function resets to zero each time the value of the cust_id field changes in the pass through the table.
In the Detail band, make a copy of the order_amt field, drag the copy to the Page Footer band for the entire table, and open the Field Properties. On the Calculate tab, select the Sum function from the Calculate Type drop-down list box and change the Reset based on value to Report.
On the Report menu, click Print Preview to see what you have created. Save the report if you are satisfied with it. The last page displays the total for all orders — information not specifically included in the table.

While the Report designer is open, you have access to tools and other designers, so you can review the current table or modify the data environment by selecting other tables or indexes. In addition, you can add functionality to the report through the Report Controls toolbar and the Report menu.

For more information on the Report designer, see Report Designer and Report Band Properties Dialog Box.

Creating a Multi-table Report

In addition to single-table data, Visual FoxPro reports can display data from several related tables and can perform calculations and produce summaries.

This procedure uses two tables, customer and orders, from the testdata.dbc database in the Samples folder.

To create a report using the Testdata database that ships with Visual FoxPro

On the File menu, click New.
In the New dialog box, select Report, and click the Wizard button.
In the Wizard selection dialog box, double-click One-To-Many Report Wizard.
In the One-To-Many Report wizard, select the TESTDATA database in the Databases and Tables drop-down list, and select the Customer table.
In Step 1 of the wizard, highlight the Customer table, and select the cust_id and Customer fields from the Available Fields drop-down list.
In Step 2, highlight the Orders table, and select the cust_id, to_city, and order_amt fields from the Available Fields drop-down list.

In Step 3, you see the way the two tables are related — customer.cust_id to orders.cust_id; in other words, the tables are indexed on matching fields.

If you want a different relationship, you could choose from the drop-down lists for the parent and child tables. If you change the specified relationship, you would also have to make sure you had chosen the correct fields in steps 1 and 2.
In Step 5, choose a report style and orientation.

In addition, you can click Summary options to access five functions — SUM, AVG, COUNT, MIN, and MAX — for fields displayed in your report. The selected functions are added automatically with appropriate labeling and positioning.
In Step 6, click Preview to see the report your selections would create if you click Finish. You can print the preview without creating a report file.
Click Finish.

You can create the same report in the Report designer, but to specify the database and the tables, you must establish the data environment of the report. You then use that data environment in a Quick Report or through drag-and-drop in the IDE to create your report.

For samples of various kinds of report you can create, see the Solution Samples.

For more information on using reports, see How to: Specify Expressions in Field Controls.