This topic focuses on the advanced functions and features available when using Calculated Columns to enhance your reports. The ability to type formulas, functions or statements directly into the Formula field allows a knowledgeable user to extend the basic controls and commands available from the Calculated Columns' user interface.
Description of Available columns
|
Warning: This topic is intended for users with knowledge of SQL, SQL syntax and Dovico's database structure.
|
General rules:
Calculated columns can be created for string, numeric and date fields.
Field names must be encapsulated by square brackets [field_name].
Text/string must be encapsulated by single quotation marks 'string of text'.
Any calculated columns you create are report specific and cannot easily be shared. If the reuse of a formula is required, you have the option to use your browser's copy and paste functions.
You can type your formula or SQL statement directly into the Formula window. You are encouraged to the use the drop-down list for field names to ensure accuracy and consistency.
You cannot directly type the name of a data field in the formula box unless that data field is defined for the report type (i.e. Displayed in Columns drop-down list).
Divide by Zero: It is up to you to make sure that your formula does not result in a 'Divide by 0' or 'Null' return. To prevent Divide by 0 results, you can wrap your formula in a CASE statement such as shown in the following example: Calculate '% Work Completed' by dividing Hours Worked by Budgeted Hours when some items have a budget of 0 hours: CASE [Budgeted Hours] WHEN 0 THEN 0 ELSE [Hours Worked]/[Budgeted Hours]*100 END
Your formula is validated against a limited data set. Errors may occur later when the report is run against the full database.
The software performs general syntax checks for any mistakes.
Error messages displayed are general in nature and it is up to the user to determine the root cause for any error.
Operators
"+", "-", "*", "/", "(", ")", "=", ">", "<", "!", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
Standard operation order rules are followed.
Functions
"CASE", "WHEN", "THEN", "ELSE", "END", "AND", "OR", "FLOOR", "CEILING", "ROUND", "SQRT", "PI", "ABS", "CONCAT", "LTRIM", "RTRIM", "SUBSTRING", "LEN", "DATEPART", "YEAR", "QUARTER", "MONTH", "DAYOFYEAR", "DAY", "WEEK", "WEEKDAY", "HOUR", "MINUTE", "DATEDIFF", "CAST", "CHAR", "VARCHAR", "AS", "INT", "FLOAT", "BIGINT", "DATETIME", "DATENAME", "RIGHT", "LEFT", "DECIMAL", "DATEADD", "CHARINDEX", "LOWER", "REPLACE", "UPPER", "DATE", "GETDATE", "LIKE"
Argument
"MAX" - used to specify the maximum size of a 'varchar' data type.