...
At the end of the article is a link that explains all the supported functions that can be used while creating calculated fields.
| Table of Contents |
|---|
Navigation & Creation
Go to My Area → My Calculated Fields from the top navigation bar to see a list of all the calculated fields created by the logged in user, if any. This page allows the user to edit, copy and delete existing calculated fields.
The page shows other details like category (if one has been assigned), last modified date, description, the expression used to create the calculated field and if it’s valid
Click on ‘Create New Field' button to open the create calculated field page. This page also opens up for the Edit calculated field button
Fields and Functions list support drag and drop operation. Pick the required field/function and drop it over the ‘Drag and drop a field or function’ area
To enter a constant value instead of a field or function, click the link ‘Enter a constant value’ to open a text box for entering the value
Name and Description fields are mandatory
Category can be assigned for ease of searching and filtering
Validate button helps in ensuring that the final expression is correct and there is no error in the field
Clicking Save will save the calculated field
‘Back to calculated fields list’ will take the user back to the initial screen
To start building a calculated field, most of the time, it is recommended to start by adding a function to the drag and drop area
dragging and dropping a function will automatically load the placeholders for parameters
function parameter placeholders can be used to add another function or a field or a constant value
For example, in the image below, the ‘ADD’ function is placed at the initial drag and drop area which automatically loads ‘value1’ and ‘value2’ parameter placeholders
‘value1’ contains a field and ‘value2' contains a constant (using the 'Enter a constant value’)
For functions with variable length parameters, another parameter placeholder can be added by using the ‘Add parameter’ link at the bottom (value3 in image below has been added using that link)
For parameter placeholders added using the ‘Add parameter’ link, a ‘Delete’ button is provided on the right in case the parameter is not required anymore
For any non-empty place holder or constant value a ‘Clear’ link is provided using which the user can delete / remove the field / function / constant value. If the ‘Clear’ link is used, the parameter becomes an empty placeholder again
Color coding used for Fields and Functions. There are 5 type of values you will see in the Fields or Functions list
For fields, the color represents the field type
For functions, the color represents the return type
For parameter placeholders, same color coding is used
ANY type of parameter, indicates, it can take all types (Numeric, String, Date or Boolean) of fields or functions
Numeric - RED
String - GREEN
Date - BLUE
Boolean - ORANGE
ANY - GREY
Validation and Save: Once you build your calculated field expression, it can be validated using the ‘Validate’button
Any errors will be highlighted with exclamation mark icon against the item
Mouse over on the icon will show the cause of error
Save will also trigger validation
For example: ADD function requires all parameters should be of type Numeric. In the below screen shot, value2 parameter has an error as it has a String field, value3 parameter has an error as it has String constant and value4 parameter has an error as it is empty
Using Calculated Fields in a Report
Once a calculated field is created, it is available under Calculated section under Fields pane in the report designer interface. You can add the calculated field just like any other field and apply available field settings (Label, Sort order, Format, Filters, etc). Please have a look at the screenshot below
...
Note: If you have not created any calculated fields, then Calculated section will not appear in the Fields pane
Sample Reports
Below are few sample reports created using calculated fields to enhance or classify the existing data
...