Report Desk: User-Defined Formulas

Report Desk: User-Defined Formulas

Release Date: 1/16/2024
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


    • Related Articles

    • Developing a New Elliott V8.6 Report Desk User Defined Report (UDR)

      'Release Date: 6/20/2018 Version: 8.6 & Up This document is written for licensed Elliott Software developers. It is not relevant for regular Elliott users. Overview With proper licensing, Elliott developers can follow these three steps to create a ...

    • Report Desk: Customizing Reports

      Release Date: 4/28/23 Version: 8.6 Customizing Reports Once a user has been given rights to modify Elliott Report Desk User Design Reports (UDR), they will have access to two additional buttons on the report parameter screens. SQL: This button will ...

    • Report Desk: Running & Designing Reports Basics

      Release Date: 03/24/2021 Revised: 08/30/2021 Version: 8.6 & Up Report Desk is a new report-developing platform for Elliott Business Software. It is based on the PSQL relational engine accessing the Elliott database, which has the potential to make ...

    • Feature - Reorder Advice/User Defined CSV Export Introduction

      Release Date: 08/07/2020 Version: 8.0 & Up Introduction The User-Defined CSV Export is a specialized report writing tool and CSV creator for inventory information to allow you to analyze an Excel spreadsheet. This program allows access to ...

    • Report Desk: Input Constants

      Release Date: TBA Version: 8.6 Background Occasionally a report will need the user to enter a parameter input value that does not get used in a WHERE phrase, but may be used in a CASE statement in a function. These input parameters are referred to as ...