How to add the SQL SELECT to the data environment of the REPORT FORM in FoxPro 3.0 and later versions

MORE INFORMATION

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