Executing a SQL Statement During a Report Run

There may be times when you want a report to retrieve additional data from the company during the report run (as opposed to during the data retrieval stage). For example, one way to display the total sales for each customer is to include in the report the customer name and a custom calculation that retrieves the total sales for a specific customer.

To do this, turn on the Advanced layout setting in Step 5 of the Quick Report Wizard and click Edit. Add a new field to the report using the Field button in the toolbar. For the field's expression, use the built-in RunSQL function, passing it a SQL statement that retrieves the desired value from the company. (Of course, this requires knowledge of both the SQL language and the structure of your company.) If the SQL statement needs the value of a field in the current record of the report's result set, prefix the field name with "?". Surround the SQL statement with double quotes.

For example, the following expression retrieves the sum of the Quantity field multiplied by the UnitPrice field (which gives the total sales) from the OrderDetails table for the current customer only ("?CustomerID" means use the value of the CustomerID field from the current record in the report's result set):

RunSQL("select sum(Quantity * UnitPrice)
  from OrderDetails
  join Orders on OrderDetails.OrderID = Orders.OrderID
  where Orders.CustomerID = ?CustomerID")

(Note that while this expression appears over several lines for easy of reading, it should be entered on one line in the Field Properties dialog.)