How To Add SQL SELECT to the Data Environment of a Report

• Microsoft Visual FoxPro for Windows, versions 3.0, 5.0

SUMMARY

In FoxPro version 2.6x, many developers used the SQL SELECT statement to create a cursor before running a report. This can be done automatically in Visual FoxPro by adding a SELECT statement to the data environment of the REPORT FORM. This article explains how.

MORE INFORMATION

To automatically query a table each time you run a report, you must first create a view within a database container (.DBC file). Then add the view to the data environment section of the report.

Step-by-Step Procedure

1. If you have an existing .DBC file, use the OPEN DATABASE command to open it. If you do not have an existing .DBC, use the CREATE DATABASE command to create it.

2. You can add a view to the .DBC file interactively by using the view designer, or programatically by using the CREATE SQL VIEW command.

   a. To add a view interactively:

         1) If the database designer is not open on the screen, type MODIFY
            DATABASE in the Command window, and press ENTER.
   
         2) Click the right mouse button, and choose New Local view if you
            want to have your view based on a FoxPro table. Choose New Remote
            View if you want to have your view based on an ODBC datasource.
   
         3) Use the View Wizard or the View Designer to select fields and
            specify selection criteria for your records.
   
      b. To add a view programatically issue the CREATE SQL command. For
         example the following command includes all customers who have placed
         orders:
   
         CREATE SQL VIEW myview AS SELECT * FROM customer WHERE ;
            customer.cust_id IN (SELECT orders.cust_id from orders)
   
      Regardless of which method you use to create the view, you should see
      the view within the database designer. The view is stored as part of
      the .DBC file. It is not stored as a separate file on disk.
   
   

3. Create a new report. From the View menu, choose Data Environment.

4. Click the Data Environment window to make it active, and click the right mouse button.

5. Choose Add from the menu. On the right side of the Add Table or View dialog, choose Views (instead of tables). Select the desired view from the list of views that appears.

6. Add any needed fields to the report, and run it. The records included in the report will be based on the selection criteria specified in your view.