Calculated fields allow you to manipulate the information in the database and make it more friendly or getting specific calculations in the source itself without having to do it in the report.
The calculated fields in Advanced Reporting use the DomEL Syntax: Advanced Reporting 101: The DomEL Syntax used by Calculated Fields
Two examples of custom fields you will create the most:
Dates: date fields in CSM are stored as Epoch format. They are an integer field with the number of seconds past 1970-01-01 00:00:00. To make it become a real date you need to create a calculated field with:
To create a calculated field you need to go to the Domain Designer: Advanced Reporting 101: Accessing the Domain Designer
Go to the Calculated Field tab:
Configure the three fields as per your need:
Field Name is the name of your new field. Just keep in mind you can't use spaces and some special characters can be a problem.
Type is the type of your new field. Types are self explanatory.
Expression is the expression you want to use to calculate your new field. Here you can have math expressions, concatenations, use SQL Functions, etc. It is mainly SQL code but there is also the possibility to use DomEL Syntax
When you are building the expression you can type in the tables and fields references or you can double click a field to add it to the expression automatically.
When you save your calculated field:
- If it uses only one table's fields it will be created inside that table.
- If it uses multiple tables' fields it will be created inside the join tree where those tables are.
- If it uses multiple tables and they are not joined... well, you can't do this
- If it doesn't use any existent field it will be created as a constant in the Constant tree
Let's say you need to create these fields:
Ticket created date in date format
Ticket Requester as Last Name, Name
These are the configurations you need:
Field Name | Type | Expression |
---|
date_created | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VAPP_ITEM.CREATED_DATE) |
affected_user | String | concat(dbo_VAPP_ITEM.person2_last_name,',', dbo_VAPP_ITEM.person2_first_name) |
You can simply copy and paste the expression above to the Expression field
date_created:
affected_user:
Once you click Save Field it will be saved in the VAPP_ITEM table because the origin fields are all from there.
Next you just need to make this fields visible using the Display tab: link to be added