The report writer allows you to enter basic and complex formulas in a field on the report. While efforts have been made to reduce the need for using formulas by providing common formulas as field choices (for instance LastFirstMI in the Personal page area) sometimes it may be necessary to branch out at write your own formulas.
For instance, if you wanted the Employee’s name to read ‘First Initial’ ‘Last Name’ instead we would need a formula.
You can use both basic math functions in the fields and some more complicated Functions as well.
Multiple functions can be utilized within a single field. In addition, these functions and formulas can be nested within each other.
Special Formulas
The report writer supports two special formulas that are used to pass the report start and end date parameters that are chosen from the report properties when the report is run into fields, filters and formulas inside the report writer.
[StartDate] – This special formulas is replaced with the report begin date when the report is run.
Example:
The following example is a filtering example that would configure the report to display the employees hired within the report start and end dates
Employment.hireDate >= [StartDate]
Employment.hireDate <= [EndDate]
[EndDate] – This special formula is replaced with the report end date when the report is run.
String/Text Functions
+ - Adds two strings together
Example:
‘Alliance’ + ‘Payroll’
Returns: AlliancePayroll
Personal.city + ‘ ‘ + Personal.addressState
Returns: The employee’s city a space and then the employee’s state e.g. Houston TX
Left – Returns the left n characters from a string.
Format: Left(string, number of characters)
Example:
Left(‘Payroll’,3)
Returns: ‘Pay’
Right – Return the right n characters from a string.
Format: Right(string, number of characters)
Example:
Right(‘Payroll’,’3)
Return: ‘oll’
Lower – Converts the string to all lower case characters.
Format: Lower(string)
Example:
Lower(‘Payroll’)
Returns: ‘payroll’
Upper – Converts the string to all upper case characters
Format: Upper(string)
Example:
Upper(‘Payroll’)
Returns: ‘PAYROLL’
Replace – Replaces all occurrences of a specified string value with another string value
Format: Replace(stringToSearch, stringToFind, replacementString)
Examples:
Replace(‘123-45-6789’,’-‘,’’)
Returns: ‘123456789’
Replace(‘Payroll’,’roll’,’ Me’)
Returns: ‘Pay Me’
Numerical Functions
Basic Math Operators – The system supports the standard numerical operators: + - * / are for addition, subtraction multiplication and division respectively.
Round – Rounds a number to a specified number of decimal places. If the number of decimal places to round is given as a negative number the number is rounded on the left side of the decimal point.
Format: Round(numberToRound,numberOfDecimalPlaces)
Examples:
Round(124.995,2)
Returns: 126.00
Round(184.34,0)
Returns: 184
Round(184.34,-1)
Returns: 180
Abs – Returns the absolute (positive) value of a given number.
Fomat: Abs(numberToRound)
Example:
Round(-773.00)
Returns: 773
Date and Time Functions
GetDate() – Returns the current date. This isn’t that useful by itself but when combined with other date functions it can became a very valuable tool
Example:
GetDate()
This returns the current date (with time)
Subtraction – You can subtract two dates this will give you the number of days between the two dates.
Example:
GetDate() - Employe.hireDate
This gives the result of the number of days since the employee’s hire date.
DateAdd – Adds a time period to a date.
Format: DATEADD (datepart , number, date ) Date Part Can be one of: year,quarter,month,day,week Examples: DATEADD(month, 1, '08/30/2006') This would give the result '9/30/2006' DATEADD(week,30,Employment.hireDate) This would give the result of the date 30 weeks after the employee’s hire date.
DateDiff – Gives the number of time boundaries that occur between two dates.
Format: DATEDIFF (datepart, startdate, enddate )
Date Part Can be one of: year,quarter,month,day,week Example: DATEDIFF(week,Employment.hireDate,getDate()) This would give the result of the number of weeks in between now and the employee’s hire date
Month – Returns the month number of any date.
Format: MONTH(date)
Example:
MONTH(Personal.birthDate)
This would give the result of the month in which the employee was born.
Day – Returns the day of the month of any date
Format: DAY(date)
Example:
DAY(Employment.hireDate)
This would give the result of the day of the month the employee was hired.
Year – Returns the year of any date
Format: YEAR(date)
Example :
YEAR(‘6/12/1982’)
This would give the result of 1982.
Utility Functions
Coalesce – Returns the first non-blank item in a list of items
Format: Coalesce(item1,item2,item3…,itemN)
Example:
Coalesce(Employment.adjSeniorityDate,Employment.rehireDate,Employment.hireDate)
Returns the first item in the list that is not blank.
Str – Converts a number to a string. This is useful if you want to concatenate a string and a number together using the + operator.
Format: Str(numericValue)
Example:
Str(Rates.rate) + ‘ per hour’
Returns the employees rate with the text ‘per hour’ after it.
IsNull – Replaces NULL values with the specified replacement value.
Format: IsNull(fieldToCheck,ReplacementValue)
Example:
IsNull(Deductions.dcode,’No Deduction)
Returns ‘No Deduction’ if the deduction code is blank.
Case - Evaluates a list of conditions and returns one of multiple possible result expressions.
Format: Case When [Expression] then [True Result] else [False Result] end
Examples:
Case When Employment.HireDate < ‘1/1/2001’ then ‘Hired Before 2001’ else ‘Hired After 2001’ end
Returns ‘Hired Before 2001’ for employees with a hire date before 1/1/2001 and ‘Hired After 2001’
For employees hired on or after ‘1/1/2001’.
case when Pay Settings.autopay = 'Salary' then Rates.Salary * 24 else Rates.Rate * 2080 end
Calculates annual salary using 2080 hours times the base rate for hourly employees and 24 times the salary for salary employees.