Creating Your Own Formulas

You may wish to add your own formulas to a report. For example, suppose you pay commissions of 5% of the sales amount. You could output a report showing sales amounts to a Microsoft Excel document and then add a formula that calculates 5% of the amount, but it would be easier and more convenient to do it in Stonefield Query. Although you can do this by using the Advanced Report Designer or customizing the data dictionary, it's much easier to do it by defining your own formulas.

To create your own formulas, click the Formula button in Step 2 of the report wizards or choose Formulas from the Tools menu and click Add in the Formulas dialog to bring up the Formula Editor.

The Formula Editor has the following options:

Data group: the module to select a table from. Only those modules installed on your system and you have rights to appear. Choose "All" to select any tables for the formula, regardless of module.
Table: the table the formula appears in. This is normally filled in automatically after you've entered the formula, but you can change it if necessary.
Name: the name to give the formula, such as "Commission Amount."
Heading: the default column heading for the formula. This is normally the same as Name but could be different, such as an abbreviation ("Comm. Amt."). You can specify multiple lines for the heading by pressing Enter where a new line should start.
Formula: the formula for the calculation. You can type the formula if you wish, but it's much easier to use the Expression Builder instead; click the button beside the formula box to bring up the Expression Builder. Note that formulas are displayed using real table and field names, another reason why it's easier to edit them using the Expression Builder. Here's an example of an expression that calculates 5% of the sales amount:
```
Transaction.Amount * 0.05
```
Send formula to database engine: turn this on if the database engine evaluates the formula or off if Stonefield Query requests any fields used in the formula from the database engine and then evaluates the formula itself. This should be turned off if you use any Stonefield Query expressions or functions that the database engine doesn't understand, such as the functions listed in the Function Reference.
Preview: click this button, which is only available after you've entered a valid formula, to see the results of your formula for the first ten records in the table. This helps you determine if you've used the correct formula.
Comments: enter any comments you want displayed in the Comments page of the Field Properties dialog.
Type: the data type for the formula's result. This is normally filled in automatically after you've entered the formula, but you can change it if necessary.
Width: the size of the formula's result. This is normally filled in automatically after you've entered the formula, but you can change it if necessary.
Dec: the number of decimal places in the formula's result. This is normally filled in automatically after you've entered the formula, but you can change it if necessary.
Order: the order of the formula in the table. Normally you won't change this value but if you want the formula to appear somewhere other than at the end of the list of fields when fields are displayed in table order, click the Order link to display the following dialog:
To move a field up or down in the list, click the button in front of its name and drag up or down.
Note that this option is only available when you edit an existing formula rather than add a new one.
Show 1000 separator: if this option is turned on, the data in the field is formatted with the thousands separator defined in the Regional Settings applet of the Windows Control Panel. This option is only available for numeric values.
Display zero as blank: if this option is turned on, a blank is displayed rather than a zero value. This option is only available for numeric values.
Display $ (the appropriate currency symbol for your system is displayed in place of "$"): turning this option on displays the currency symbol defined in the Regional Settings applet of the Windows Control Panel in front of the field's value. This option is only available for numeric values.
Display date only: turning this option on displays only the date portion of the field's value. This option is only available for date/time values.
Display empty date as blank: if this option is turned on, a blank is displayed rather than " / / " for blank dates. This option is only available for date/time values.
Decimal places: the number of decimals to display for numeric values.
Custom format: turn this option on if you want to specify your own custom format, and enter the format in the next box.
- For numeric values, use "9" as a placeholder for digits, "," to indicate where the thousand separators should go, and "." to indicate where the decimal separator should go. Use "$" as the first character to specify that a currency symbol should be displayed. For example, 12345.6789 is displayed as $12,345.68 if you use "$999,999,999.99" as the format. You can also add "Z" to the start of the format to display zero as blank.
- For date/time values, use "Z" to display a blank date/time as blank rather than " / / ". You can also use "D" to display the date and time of a date/time value (although this can be turned off in the Field Properties dialog). If this isn't specified, Stonefield Query displays only the date portion of a date/time value by default.
Sample: shows a sample of how date and numeric values are formatted.

The OK button is only enabled after you have entered a formula and the Table, Name, Type, and Width settings are filled in. Click OK to save the formula and close the Formula Editor. Note that any formula you create is automatically added to the report so it appears in Selected Fields list.

To see all of the formulas you've defined, choose Formulas from the Tools menu.

This dialog shows a list of the formulas you've defined at the left and information about the selected formula at the right. To add a new formula, click Add. To edit the selected formula, click Edit. Click Copy to copy the selected formula to a new one and edit that formula. The Remove button removes the selected formula; you can also press the Delete key to remove the formula.