This report shows an invoice report that uses a view to combine information from multiple tables, a function to collect data from the user, and a one-to-many report layout. When the report is run, the view runs first and prompts the user for a date range using a function called Datepick.prg. After the user provides the dates, the view runs and selects the records to display in the report.

The Record SourceВ В В The view, INVOICE, used in this report resides in the TESTDATA database provided with the Solution project. This view combines data from four tables, CUSTOMER, ORDERS, ORDITEMS, and PRODUCTS. To filter the tables for records within a date range, the view has two filters each with a parameter, dStart_Date and dEnd_Date.

The Data EnvironmentВ В В The view is included in the data environment. To store values collected for the view and to restore the path setting, the BeforeOpenTables method of the data environment declares three public variables: dStart_date, dEnd_Date, and cOldPath. The first two match the parameters specified in the view. The third, cOldPath, saves the current path setting so the path can be restored in the Destroy method. The path is then changed so the report is able to find the function, which prompts the user for a range of dates. The final command runs the function.

The Destroy method of the data environment has public variables for the start and end dates, path setting to return the default path to what it was before the report was run, and release statements for all three of the public variables.

The Datepick.prgВ В В This function defines and runs a form class called frmDATEPICK. The form class includes four combo boxes that display and collect values to use in the public variables, dStart_Date and dEnd_Date. The four variables collected are nFromMonth, nFromYear, nToMonth, and nToYear. These variables store the user input and are used to determine the value of dStart_Date and dEnd_Date. The function also checks to make sure the user entered values for each element of the date and verifies that the end date is after the start date.

The Report LayoutВ В В In the report layout, a Group Header band holds the controls for the customer information, the Detail band has the controls for the order items, and the Group Footer band holds the controls that display the calculated values for the invoice.

To format the address appropriately, an expression in the control concatenates several fields with appropriate punctuation. To calculate values in the Group Footer band, two report variables, nSubtotal and nDiscount, store values for the subtotal and the percentage discount.