Adding Formula Fields to a Query

In addition to the table columns, you can also add formula fields to a query.

To add a formula field to a query:

In the Query Editor, click Add Formula Fields on the toolbar or click Menu > Column > Add Formula Fields. The Add Formula Field dialog appears.

Add Formula Fields dialog

Choose the required formula in the Formulas box and then click Add. The formula will then be added to the SELECT columns in the SQL of the query and placed into the criteria panel in the lower part of the Query Editor. You can add filter criteria based on the formula column the same as on any other columns in the query.

Repeat the above step until you have added all the required formula fields, and then click Close. The formulas are added to the current query.

After a formula field has been added to a query, if you want to replace it with another one, you can double-click its name in the criteria panel, and then choose the required field in the Replace Formula dialog.

Notes:

When you add a formula field, if the table that contains the field doesn't exist in the query, this table will be automatically added to the query.
You are not able to add or edit a formula in the Add Formula Field dialog.
The processing is very different from adding a computed column even though the data looks the same. The computed column is calculated by the database engine before the data is returned. The formula value is calculated by JReport after the data is returned so is less efficient.