Report Desk: User-Defined Formulas
Versions: Elliott V8.6 and Above
Background
Prior to this release, users had the ability to create a user-defined formula by dragging the [New Formula] node on the TreeView in the report designer to a line on the report. That one-step process was convenient and worked in most scenarios, but when a Sum (or Count or other aggregate) was needed for that formula, it was not available.
To fix this issue, we have separated the creating and applying of user-defined formulas:
- First, a user-defined formula is developed independent of where it will be applied and it will then be available under the [Formula] node in the TreeView, along with formulas defined in the SQL template.
- Then, the formula can be dragged-and-dropped onto multiple locations of the report -- for example, once on a Body line and then again on a Footer line with a Sum aggregate.
Creating a New Formula
To create a new formula for the design, expand the [Formula] node of the TreeView and click on the [New Formula] node:
This brings up a dialog where you can define the new formula:
In this case, we want to compare this year's discounts given to last year's discounts given. After you enter the fields, press the OK button to add the formula to the TreeView:
Note that the name of the formula has had an embedded Space character removed. That is because the formula name must be a valid SQL name. Now, you can drag the new formula to a line on your report:
After dragging it to the Body line, we changed the Format and Align properties to "N2" and "Right," respectively, to be appropriate for an amount.
If we want to, we could also drag the new formula to the Footer line, change the format and alignment, and assign the Sum aggregate:
.
Now, when the report is run, the [DiscountChange] formula is used to show each vendor's change in discount and the total of all changes in discount.
Editing a User-Defined Formula
To modify the properties of the formula after creating it, you can right-click on the formula in the TreeView and select the Edit context menu:
Make any desired changes, including changing the name, and the changes will be applied throughout the design.
Deleting a User-Defined Formula
Similarly, if you right click on the formula in the TreeView, you can select the Delete context menu item to delete the formula. However, you must remove any usage of the formula in the design before you can delete it. If you have not deleted all usage, you will get a message similar to this, telling you where to look for occurrences of the formula in the design:
Template or User-Defined Formula?
The list of formulas in the TreeView will contain all SQL template formulas and all user-defined formulas -- but which are which?
- When you right-click on a SQL template formula, the context menu will start with View.
- When you right-click on a user-defined formula, the context menu will start with Edit.
Viewing a Template Formula
If you right-click on a SQL Template formula in the TreeView, you will get a context menu that will allow you to view, but not change, its formula:
JEG
EL860RD, UDRDesign