Creating Reports With Visual FoxPro 

  • Article

 

McAlister Merchant
Microsoft Corporation

April 2001

Applies To: Microsoft Visual FoxPro 7.0

Summary: This paper shows how to create Microsoft Visual FoxPro reports. It discusses issues to consider beyond direct data access, as well as issues related to the needs for reports, and planning and creating both online and offline reports. You can use 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. (7 printed pages)

Contents

Introduction What Is Data? Planning a Report Creating a Single-table Report Creating a Multi-table Report

Introduction

Although Microsoft® Visual FoxPro® 7.0 data can be reported to the screen simply as a Browse window or a form, properly designed reports lend clarity, readability, and distributability to data that turns it from developer data to user information. Reports are great for display and distribution of the information available from data because they are easily repeated, duplicated, and shared.

It is easy to build simple reports in Visual FoxPro. Indeed, the very tools you use to create tables provide ways to immediately see all the data available. (For more information, see the "Creating Tables with Visual FoxPro" paper in MSDN Online.)

The Report designer or Report wizard are especially helpful if you want to create a report that displays in one view the relationships or the combined information between two or more tables. This paper uses the Report wizard to create two reports—a simple single-table report and a multiple-table report.

What Is Data?

Visual FoxPro deals with data in tables. Data is discrete and can be found in an ever-expanding range of types. Visual FoxPro can access most of it, but access is often not enough. To use data effectively, you must turn it into information that people can use—you must report it. This does not have to be a magical process. Usability can be as simple as a little pre-planning. Effectiveness can take more effort. Typically, you spend more time using or designing reports then you spend creating tables.

Planning a Report

With reports, planning is not only important, but is recurrent and, sometimes, recursive. Use a report when you want to obtain the same information repeatedly or when you want to encourage specific activity or aid particular processes.

An effective report is one that makes it possible for you to use information quickly, whether or not you use all the available data. The way to start report planning is to think about how that report will be used.

Often, information is more inspiring or thought provoking when it is formatted properly or organized carefully. For example, if you are preparing for a staff meeting, it might be helpful to create reports in which data is organized by staff responsibilities, as well as by time-based, geographic, or financial relationships. If you are working with your address book or contacts list, you might find it helpful to create reports organized by zip code (state), or birth month, phone area code, or hobby.

When you gather information for records in an address book or contact list, you enter all the information about one individual; however, you might create each record at a different time and place. Even if you create several records at one time, you still complete one record before you begin the next. Several records can have identical values, but without a report it can be difficult to know which ones or how many are identical. It can be important and useful to have a way to see these and other relationships.

Online or Printed Reports

If you are going to display data online or in print, there are some issues you should consider:

  • Scrolling: If your information cannot be displayed on one screen page, then consider using separate pages rather than a long scroll. In addition, consider a different visual organization of the data, such as labels, positioning, or the use of white space.
  • Labels: Labels influence both the eye (where users look for information) and the mind (what users expect to find).
  • Spacing and Positioning: This can be as important as font size for focus and clarity.
  • White space: Most people do not enjoy reading large blocks of text online, so good use of white space can make your report more effective.
  • Location: This is as important to screen real estate as it is to land. The top, center, right, and then left are most often the important screen areas.

If your report can access background information or relevant data directly rather than always displaying it, then the basic report is more effective (and you have more room for the most important information).

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:

  1. In the File menu, open the table.
  2. From 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:

  1. From the File menu, select Open.

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

  3. From the View menu, select Browse.

    This displays the selected table. At this time, in the IDE, you can change the column widths or locations, or you can make more significant changes from the Table menu.

  4. From the File menu, select Export.

  5. In the Export dialog box, select DelimitedText 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.

  6. Click the To button and specify the file name of your new file. Include the extension.

  7. 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, updateable, 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:

  1. From the File menu, choose New, and then select Report.
  2. In the New dialog box, click Wizard, 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:

  1. In the File menu, click New.

  2. In the New dialog box, select Report and click the Wizard button.

  3. In the Wizard selection dialog, double-click Report Wizard.

  4. In the Report wizard, select Free Tables in the Databases and Tables drop-down list, and select or locate the Labels table.

  5. Select any or all the available fields and continue the wizard, making the appropriate selections up to Step 5.

  6. 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.

  7. 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 it is much prettier.

  8. Click Finish.

For more information on the Report wizard, see "Report Wizard" and "One-To-Many Report Wizard" in the Visual FoxPro Help.

Visual FoxPro Report Designer

You can use the Report designer to create simple reports, called Quick Reports. The Report designer is also 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:

  1. In the File menu, click New.

  2. In the New dialog box, select Report, and click the New File button.

  3. In the Report menu, select Quick Report.

    If you have a table (such as Lables.dbf) open, Visual FoxPro assumes you want to 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. The details are up to you. With the Report designer, you have more control than with the Report wizard, but 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 create a report containing a calculation:

To perform this operation, 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. The final steps are to preview, print, and save the report.

  1. From the File menu, open the ...\samples\DATA\orders.dbf table.

  2. From the Window menu, open the Data Session dialog box, and click the Properties button for the orders table.

  3. 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.

  4. Open the Report Designer. In the File menu, select the New option. From the New dialog box, select Report, and then click New File.

  5. From the Report menu, select Quick Report, and then select the vertical layout.

    **Note   **For this example, you do not need all the fields, so you might want to remove the labels and fields following order_amt, as well as the labels and fields between emp_id and order_date. Move the fields together and resize the report bands.

  6. In the Report menu, choose Data Grouping.

  7. In the Data Grouping dialog box, click the Ellipsis button (...) under Group Expressions. In the Fields list in the Expression builder, double-click Orders.cust_id. Click OK in both dialog boxes.

  8. In the Report Designer, place a copy of the order_amt field in the Group Footer band for group totals, and place another copy in the Page Footer for the entire table.

  9. Copy, paste, and then drag the copied fields from the Detail band.

  10. For each copy of order_amt, double-click the field to open the Report Expression dialog box.

  11. Click the Calculations button.

  12. Select the Sum function from the Option Group.

    The Reset drop-down list should display the default selection, which is the Order.cust_id field. This means that the Sum function resets to zero each time the value of the cust_id field changes in the pass through the table. This is fine for the group sub totals, but you must change this selection for the order_amt field in the Page Footer band.

    After you select Sum in the Calculate Field dialog box of the order_amt field you placed in the Page Footer band, change the Reset value to End of Report.

  13. After setting the Calculation field of each order_amt, close the dialog boxes by clicking OK.

Use the print preview to see what you have created and to make sure that you are getting the information you expected. 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 frills and 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 Designer Band Dialog Boxes" in the Visual FoxPro Help.

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 Report wizard.

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

  1. In the File menu, click New.

  2. In the New dialog box, select Report, and click the Wizard button.

  3. In the Wizard selection dialog box, double-click One-To-Many Report Wizard.

  4. In the One-To-Many Report wizard, select the TESTDATA database in the Databases and Tables drop-down list, and select the Customer table.

  5. 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.

  6. 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 wanted a different relationship, you could chose 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.

  7. 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.

  8. 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.

  9. 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 Solutions sample application in the Visual FoxPro Help.

For more information on using reports, see "Using Expressions and Functions in Field Control in a Report" in the Visual FoxPro Help.

© Microsoft Corporation. All rights reserved.