Executing a SQL Statement During a Report Run

There may be times when you want a report to retrieve additional data from the database 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 database. (Of course, this requires knowledge of both the SQL language and the structure of your database.)

If the SQL statement needs the value of a field in the current record of the report's result set, specify it like this:

?(ResultSet + '.*FieldName*')

where FieldName is the name of the field as it appears in the result set. 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 = ?(ResultSet + '.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.)

Note that RunSQL can cause performance degradation of your reports as discussed in this blog post :arrowupperright:. Consider using a grouping formula instead.