Creating and Using Calculated Fields

Calculated fields are values that are not directly stored in the database, but are calculated from database values. WordPage allows you to define as many calculated fields as you wish. These formulas are stored in your document and show up in the field list just like ordinary database fields.

To create a new calculated field click the “Add” button. If the currently selected field in the field list is a calculated field the “Edit” and “Delete” buttons are enabled. Clicking the Add or Edit buttons brings up the Calculated Field Editor discussed below. Click the Delete button to remove a calculation from the list of saved calculations.

The Calculated Field Editor

To make it easier to create and edit calculated fields WordPage uses the “Calculated Field Editor.” The example above defines a field called “Amount” that is based on the UnitPrice multiplied by the Quantity. The “Sum” function is called an aggregate. Aggregates will be described in a later section.

“Name” is the field name that will show up in the main field list and be used in the document. The name must be unique and contain no spaces. If the name is not valid or already exists the “OK” button is disabled.

“Calculation” is the expression that defines the calculated field. Any expression that is acceptable to the database that is associated with this datasource is allowed. You may click the “Test” button to be sure the calculation expression is valid in the current database. You may edit the calculation directly or double click items in the field list or function list to insert them into the calculation.

The field list, “Match”, and “Type” items behave like the main field list discussed earlier. Double click an item in the field list to insert a field into the calculation at the current cursor location.

The Function List and Aggregate Calculations

The function list shows the available aggregate functions. Double clicking an item inserts it into the calculation by surrounding the current calculation with the requested function.

What is an Aggregate Function?

Aggregates are used by databases to define calculations that use multiple rows to produce their values. For example, most databases that tracks sales information maintain order information at a very detailed level. Each item on each order is kept separate in the database. To report sales by salesperson or region it is necessary to get the total of all of the details that make up all of the orders for each salesperson or region. The example shown above illustrates using the “Sum” function to achieve this effect.

Note that the same aggregate calculation is used whether the total will be per salesperson or region. The aggregate calculation is based on all of the rows meeting whatever filter criteria are used in the query. SQL databases describe the roll-up with a “group by” clause in the “select” statement. WordPage generates this “group by” automatically. The non-aggregate fields that are retrieved determine the level of roll-up performed by the aggregate calculation. If you retrieve “salesperson” then the aggregate will produce the amount for each sales person. If you retrieve “salesperson” and “orderdate” the result will be one row for each sales person for each date on which orders exist.