Use custom formula (fx) fields

One of the more advanced elements of Query Builder functionality is the ability for users to create their own custom formula (fx) fields using a limited number of SQL functions. This functionality is in addition to the predefined queries provided in Enhanced Reporting.

Use this section to help you to create formulas. The examples in this section have been tested but may need adjusting slightly for use with your own data. If you need more help, contact your account manager, who can arrange more personalised training with a IRIS Cascade consultant.

The fx function in IRIS Cascade HR uses 20 T-SQL scalar functions. Help on other functions is available on the internet.

You can paste any of the formulas in this article into a formula field.

Rules for writing formulas

The following rules apply to all formulas:

  • All field names that are referenced in the formula must exist in the query

  • All field names must be enclosed in square brackets, for example, [Known As]

  • T-SQL functions use round brackets, for example, UPPER()

  • Field names are not case sensitive but must be spelled correctly. For example, [cont. service date] and [CONT. SERVICE DATE] will work, but [cont service date] will not work

  • Any text to be displayed must be enclosed in single (not double) straight quotation marks

Take care when writing formulas and building queries. If any part of the formula or query breaks these rules, you will see a Formula Error message in every formula field in your query, regardless of the actual number of errors. This is a default message that gives no information about what is wrong, so you will need to check all aspects of the query and formula to resolve the error(s).

Creating formulas

To access the formula function in Query Builder, select the fx icon on the right-hand side of the Query Builder screen. This adds a large white text box to the screen.

To change the name of the text box/formula, double-click the text Formula 1 at the left-hand side, type the new name, and then press ENTER. This name is also used in any reports created from this query.

Formula Helper

The Formula Helper (‘I want to’) drop-down menu lists common formulas for you to use.

The table below provides more information about the formulas that are included in this dropdown menu by default. The menu in your system may include further options that have been created and saved for re-use.

Re-using formulas

You can save the formulas that you write for future use, by selecting Re-use.

If you create a formula, re-name it in Query Builder, and then save it, the formula becomes available to select from the dropdown ‘I want to’ menu for the user who created it.

For example:

  1. Create a formula to display all employees who have an FTE of 1 as Full Time and all those with an FTE less than 1 as Part Time.

  2. Select Re-use.

  3. Save the formula in a query called FTPT Query.

  4. Select the formula from the ‘I want to’ drop-down menu to add it to the formula box in any new formula.

Formula

Code added to formula

Action/example

Reference a field from a query

[Field Name]

Extracts the data from one field and copy it into the formula. This is used in every formula.

Extract part of a date value

DATEPART(datepart, date)

Extracts part of a date value only.

For example, if you replace datepart inside the brackets with d, the formula will return the day; if you replace datepart with m, the formula will return the month.

Find the difference between two dates

DATEDIFF(datepart, startdate, enddate)

Example: the number of months an employee was employed.

Add or subtract from a date

DATEADD(datepart, number, date)

 

Conditionally display a value based on a test

CASE WHEN expression THEN value1 ELSE value2 END

Similar function to IF in Microsoft Excel (Learn more about the IF function in Microsoft Excel).

Group a numeric value into bands

AUTOBAND:[Field Name]

Provides a rudimentary banding of any numeric field, for example, Bradford Factor or Length of Service (years).

This can only be used for numeric fields; the banding cannot be changed, and the results cannot be grouped manually.

Replace instances of some text within a field

REPLACE([Field Name], texttofind, texttoreplace)

 

Make some text uppercase

UPPER([Field Name])

 

Make some text lowercase

LOWER([Field Name])

 

Other useful formulas

Function

Formula

Create a new field holding all the default Query Builder fields

[firstname]+' '+[surname]+' '+[employee id]

Add a space in the results

+ ‘ ‘ +

Add brackets

+ ‘ (‘ +

Add a comma after text

+ ‘, ‘+

Employee’s name, with current position in brackets

[employee name] + ' (' + [position] + ') '

Employee’s surname in upper case, comma, space, employee’s first name

UPPER([surname])+','+' '+[firstname]

As above, but all text lower case

LOWER([surname])+','+' '+[firstname]

Employee’s surname, employee’s first name, employee ID number in brackets

UPPER([surname])+','+' '+[firstname] + ' (' + [employee id] + ')'

Change blank result in a number field (for example, Bradford Factor score) to a zero

ISNULL([Bradford Factor], '0')

Extract part of a date: employee’s year of birth

YEAR([date of birth])

Extract part of a date: employee’s month of birth

MONTH([date of birth])

Extract part of a date: employee’s date of birth

DAY([date of birth])

Extract part of a date and display as text

DATENAME(year,[date of birth])

DATENAME(month,[date of birth])

DATENAME(day,[date of birth])

Replace a word with another word

REPLACE()

Type:

  • Text to be replaced in first set of single quotes

  • New text in second set of singles quotes

Examples:

  • REPLACE([status], 'current', 'At Work')

  • REPLACE([status], 'left', 'Left IRIS Cascade on')

Remove a word (i.e. replace a word with nothing)

Example:

REPLACE([Position],’Senior’,”)

Return today’s date

GETDATE()

Change a field’s data type, for example, change a number field to a text field

CAST()

Birthdays and date fields

The Main screen includes a field called Days To Birthday, which you can use to find who has a birthday today by using the query shown in the screenshot below.

If you need advance notice, change the zero to the number of days’ notice you need: for example, change the zero to 5 to be notified five days before the birthday.

The examples below use the [date of birth] field, but you can also apply the formula to any other date field (for example, Retirement Date, Probation Date).

Function

Formula

Additional actions

Find any employee with their birthday today

CASE

WHEN DAY([date of birth]) = DAY(GETDATE())

AND

MONTH([date of birth]) = MONTH(GETDATE())

THEN 'Yes' ELSE 'No'

END

Set criteria for the formula field as EQUAL to YES

Find any employee with their birthday in seven days’ time

CASE

WHEN DAY([date of birth]) = DAY(dateadd(d, 7, GETDATE()))

AND

MONTH([date of birth]) = MONTH(dateadd(d, 7, GETDATE()))

THEN 'Yes' ELSE 'No'

END

Set criteria for the formula field

Find any employee with their birthday in the coming month

CASE

WHEN DATEPART(month,[date of birth] ) = DATEPART(month,GETDATE()) + 1

THEN 1

ELSE 0

END

 

Show results of the above in day and month format

(second formula field)

CASE

WHEN DATEPART(day,[date of birth]) <10 THEN '0' + DATENAME(day,[date of birth]) + ' ' + DATENAME(month,[date of birth])

ELSE

DATENAME(day,[date of birth]) + ' ' + DATENAME(month,[date of birth])

END

 

Further examples

Function

Formula

Identify employees who are full time or part time more clearly

CASE

WHEN [FTE] <1

THEN 'Part time employee'

ELSE 'Full time employee'

END

Length of service banding

CASE

WHEN [los years] < 5 THEN 'less than 5 years'

WHEN [los years] < 10 THEN '5 to 10 years'

WHEN [los years] < 20 THEN '10 to 20 years'

ELSE 'Over 20 years'

END

Grouping Bradford Factor scores (taking into account blank scores for employees with no absence)

CASE

WHEN ISNULL([bradford factor], '0') <= 100 THEN '0 to 100'

WHEN ISNULL([bradford factor], '0') <= 200 THEN '101 to 200'

WHEN ISNULL([bradford factor], '0') <= 300 THEN '201 to 300'

WHEN ISNULL([bradford factor], '0') <= 400 THEN '301 to 400'

ELSE '401 and above'

END

Report on entitlement in hours/minutes/days

CASE

WHEN [Show Holiday in Hours and Minutes] = 'YES'

THEN CAST([Remaining]/60 AS NVARCHAR(5)) + ' hrs ' + CAST([Remaining]%60 AS NVARCHAR(5)) + ' mins '

ELSE CAST([Remaining Days] AS NVARCHAR(5)) + ' days '

END

Divide basic pay by FTE to give FTE salary

(If you see a Formula Error message, an employee has an FTE of 0.0 (zero).

[Basic Pay] / [FTE]

Display results as money to two decimal places

CAST([basic pay]/[FTE] AS MONEY)

Display results in correct format

CONVERT(varchar, CAST([basic pay] / [FTE] AS MONEY),1)

Add currency sign to results

'£' + CONVERT(varchar, CAST([basic pay] / [FTE] AS MONEY),1)

Further step

'FTE' + ' - ' + '£' + CONVERT(varchar, CAST([basic pay] / [FTE] AS MONEY),1)

Change how time format data is displayed in Microsoft Excel (in time format rather than date, with apostrophe at beginning of data)

(new formula field for each field in query that needs to be corrected)

'''' + [Totalhoursworked]

'''' + [Excessordeficit]

'''' + [Lost]
Related Topics Link IconRelated topics: