There are two kinds of calculated fields that can be added into user reports as data fields:
To add a calculated field to a user report, choose either of the two options from the top of the Available Data Fields list, and click
.
Custom SQL Fields
Custom SQL Fields enable you to create a new field, which is the result of concatenating two or more existing fields, or is the result of a mathematical calculation on numeric fields. This field type allows the user to utilize SQL commands like CASE, DATEDIFF and GETDATE().
To learn more about the different SQL functions that can be used, click here.
Add a SQL field into the user report by clicking on <Custom SQL Field> from the Available Data Fields list.
Once the SQL data field has been added, modify the field name, and formula by referencing other data fields.
Included data fields from the user report can be referenced in a Custom SQL Field by copying the Field value of the data field into the Formula field, and inserting them in between open "[" and close "]" square brackets.
Custom Calc Fields
Calculated fields can be utilized for simple functions such as concatenating included data fields in the user report, or configuring a simple calculation.
To learn more about the different Calc functions that can be used, click here.
Add a Calc field into the user report by clicking on <Custom Calc Field> from the Available Data Fields list. Once the Calc data field has been added, modify the field name, and formula by referencing other data fields.
Included data fields from the user report can be referenced in a Custom Calc Field by copying the Column Caption value of the data field into the Formula field, and inserting them in between open "[" and close "]" square brackets.
Things to Note:
Custom SQL fields can be used as standard filters on user reports.
Custom Calc fields cannot be used as standard filters on user reports.
It is best practice to add a field which is being referenced in a SQL field as a data field in the user report. However, a SQL field referencing another field which isn't included as a data field in the user report will still work.
Fields which are going to be referenced in a custom Calc field are required to be included as data fields in the user report.