Below is a list of some of the characters and functions that are supported:
* multiply symbol in the formula.
/ divide symbol in the formula.
+plus symbol in the formula. Also used for addition or for concatenating two fields.
- subtraction symbol in the formula.
( open parenthesis symbol to the formula.
) close parenthesis symbol to the formula.
Quotes - single quotation marks around highlighted text. Any text added to the formula field by you must be enclosed by single quote marks.
CAST - Used when concatenating a text and a numeric field). CAST will convert a numeric value to a text value or a text value to a numeric value.
Concatenating a text field and a numeric field using the CAST function
Example: combining [Entry: Amount] & [Entry: Currency] fields to produce a new field that takes the format “[Entry: Amount] [Entry: Currency] fields”, such as “3000.00 USD”.
First add the Amount and Currency fields from the Available fields list to the report: [Entry: Amount] & [Entry: Currency]
Add a Custom SQL Field to the report. Modify its caption and sequence fields as appropriate.
The formula field should read: “CAST([Entry: Amount] AS NVARCHAR) + ' ' + [Entry: Currency]”
If you do not want to see the new concatenated field AND the [Entry: Amount] & [Entry: Currency] fields on the report, check the Hide checkbox next to the [Entry: Amount] & [Entry: Currency] fields.
Note - You should use NVARCHAR in your cast when your resulting field will be a string
CASE
CASE is an “IF THEN” style logic operation to your calculated field, in the format of:
CASE WHEN True_Or_False_expression THEN result_expression
[…n] ELSE else_result_expression END
Each section of this statement is described below:
WHEN True_Or_False_expression is an expression that returns either TRUE or FALSE when evaluated. Any valid SQL Server expression is allowed.
THEN result_expression is the expression that is returned when the WHEN True_Or_False expression evaluates to TRUE. Any valid SQL Server expression is allowed.
[…n] is a placeholder indicating that multiple “WHEN…THEN“ clauses can be used.
You will have to type these additional clauses yourself into the calculated field builder screen, since only one “WHEN…THEN” clause is provided when you click the CASE Statement button.
ELSE else_result_expression is the expression that is returned if none of the WHEN… THEN operations evaluate to TRUE.
If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. Any valid SQL Server expression is allowed.
Example:
CASE
WHEN taxable = 0 THEN ‘None’
WHEN taxable < 10000 THEN 'Low'
ELSE ‘High'
END
Note - Don’t forget to place the word END on the end of the expression. It is required to make the SQL field work correctly.
COALESCE
COALESCE can be used when the need is to compare a number of data fields and the desired result is to return the first field that is not NULL.
For example, the desired returned value for the calculated field is Assignment End Date, if that is blank then Assignment Extension Date, is that is blank then Assignment Scheduled End Date.
To return the desired result the Calculated Field would be written using the COALESCE function as follows:
COALESCE([ASSIGNMENT!ACTUAL_END_DATE], [ASSIGNMENT!CURRENT_EXTENSION_END_DATE], [ASSIGNMENT!SCHEDULED_END_DATE])
DATEDIFF
The DATEDIFF statement is a function that returns the difference between two date fields, in days. The format of the statement is:
DATEDIFF (day, startdate, enddate)
Each section of this statement is described below:
day is a constant value indicating that the function should return the result of the calculation in days.
startdate is the beginning date for the calculation. startdate is an expression or field that returns a date value, or a character string in a date format.
enddate is the ending date for the calculation. enddate is an expression or field that returns a date value, or a character string in a date format.
Note - Use the function “GetDate()” to get today’s date for use in a formula
To Utilize a ‘Date Difference’ To Display String Values:
From the information provided in ‘Date Difference’ (please see above), the following calculation enables the report to display VarValues while analyzing date differences.
Ex. 'Actual Close Date Past 30 Days' SQL Calculation:
CASE WHEN DATEDIFF(day,[Property Closing Detail: Actual Close Date], GETDATE()) < 30 THEN 'No' WHEN DATEDIFF(day,[Property Closing Detail: Actual Close Date], GETDATE()) > 30 THEN 'Yes' END