List of Custom Report Functions

A large number of functions can be performed on data in reports to calculate and display the precise information you require. Each of the functions described below can be implemented in a report in Report Generator as follows:

  1. In Report Generator, go to the Design tab (at the bottom of the screen).

  2. For any of the below functions, a field is required as a placeholder. In effect, the results of the function will be displayed instead of the data from the field (the data can be used in a function, however).

    Drag any field onto the design area to be used as a placeholder.

  3. Select the field and change the Output type to Custom.
  4. Enter the function into the Function box.
  5. Click Apply to save changes.

If you are familiar with SQL scripts, you can see the affect the function has on the script behind the report by going to the SQL tab at the bottom of the screen. The functions work as a sub-select within the main query.

Please note that if a field is used in a function, generally that field’s table must be joined in the report for it to work. For example, if you are performing a function on traineepot.startdate, the traineepot table must be joined. The exception is when the field is selected from a table within the function (these cases are pointed out in the function description).

Function Name Example Details
ABS 
abs(-1)
 Returns the absolute value of a number. The example would return 1 rather than -1. A field can be used instead of a number to give the absolute value of the field.
F_AGEINYEARS 
Maytas3.F_AGEINYEARS(trainee.dob,traineepot.startdate)
 Returns the age between two dates. The example would return the age at start – i.e. the difference in years between a learner’s date of birth and their start date.
DATEADD

Example 1 

dateadd(day,42,traineepot.startdate)

Example 2 

dateadd(month,6,dateadd(year,1,traineepot.startdate))

Adds a specified period of time to a date. The functions takes three parameters: the time period (e.g. day, week, month), the amount to add and the date to add it to.

Example 1 shows 42 days being added to a learner’s start date, which would give their 6 week threshold.

Example 2 shows nested dateadds – the inner function adds 1 year to the start date. The outer function uses this as the date it is adding to, and adds 6 months to it. The result is adding 18 months to the start date. Please note that this example is intended to demonstrate nested functions, and that the same result could be achieved with a single dateadd function simply adding 18 months to the start date.

A list of the available time periods can be found here.

F_WEEKSBETWEEN 
Maytas3.f_weeksbetween
(
	traineepot.startdate,
	traineepot.terminationdate,
	'01-AUG-2012',
	'31-JUL-2013'
)

This takes four date parameters – typically the learner start date and termination date, and then a period start and end date. The function takes the latest of the start date and the period start and the earliest of the termination date and period end, and shows the difference in weeks between them.

In the example, if the learner’s start date was 01/07/2012 and the termination date was 01/09/2012, the result would be the difference in weeks between 01/08/2012 (the period start date, as it is later than the learner start date) and 01/09/2012 (as the termination date is earlier than the period end date).
CHAR 
char(65)
 Converts an ASCII value to a character. The example would convert the ASCII code 65 to the character A.
SUBSTRING

Example 1 

substring('abcdef',1,3)

Example 2 

substring(trainee.lastname,1,1)

Returns a substring of text. The function takes the starting text, the start character of the substring and the length of the substring. Example 1 would return abc, as the substring starts on character 1 and is 3 characters long.

A field can be used instead of a text, as shown in Example 2, which returns the first character of a learner’s surname.
ROW_NUMBER 
(row_number() over(order by trainee.lastname))
 This provides the row number based on a specified ordering. The ordering field should be entered after order by (trainee.lastname in this example). Please note that the field specified for the ordering does not sort the data – it simply shows what the row number would be if the report was ordered by that field.
F_CROWMILESDISTANCE 
Maytas3.f_crowmilesdistance(025339,134177,972774,338047)

Calculates the direct distance between two grid references. The grid references are provided as:

Ref1 North, Ref1 East, Ref2 North, Ref2 East

The example calculates the distance between Land’s End and John O’ Groats.
Currency 
'£' + cast(cast(isnull(claimdetails.unitvalue,0) as numeric(15,2)) as varchar)
 This will convert a numeric field into a UK currency format. The field used in the example is claimdetails.unitvalue – this can be replaced with any numeric field.
GETDATE() getdate() This returns the current date. By default it will include the time as well. To show only the date, enter dd/MM/yyyy in the Format box (just above the Function box).
DATEDIFF

Example 1 

datediff(month, traineepot.startdate, traineepot.fundingexpirydate04)

Example 2 

datediff(month, getdate(), traineepot.fundingexpirydate04)

This shows the difference in time between two dates. Example 1 shows the difference in months between a learner’s start date and planned end date. Example 2 uses the getdate() function detailed above to show how many months of funding a learner has remaining from today.

A list of the available time periods can be found here.
DATEPART (day of week) 
datepart(dw,traineepot.startdate)
 Returns the day number of the week (Sunday = 1, Monday = 2, etc). The example shows the day number of a learner’s start date.
DATENAME (day of week) 
datename(dw,traineepot.startdate)
 Returns the day of the week for the provided date (Monday, Tuesday, etc). The example shows the day of a learner’s start date.
Integer division 
(3 / 2)
 Returns an integer division of two numbers, meaning the result is rounded down to the nearest integer. The example would return 1. Numeric fields can be used in place of the numbers.
DATEPART (day / month / year) 
datepart(month,traineepot.startdate)

Returns the specified time period from the provided date. The example shows the month of a learner’s start date.

A list of the available time periods can be found here.
CASE 
case when traineepot.startdate >= '01-AUG-2012'
then 'X'
else 'Y'
end
 Allows you to perform a logical if-then-else statement. The example checks if the learner start date is on or after 1st Aug 2012 – if it is, the result is X, otherwise the result is Y.
LEN 
len(trainee.lastname)
 Returns the length of the data in characters. The example shows the length of a learner’s surname.
CAST as NUMERIC 
cast(1.9876 as numeric(15,3))

Rounds a number or numeric field to a specified number of decimal places. The example would round 1.9876 to 3 decimal places – i.e. 1.988.

The 15 used in this example represents the maximum length in characters that the rounded number can be. However, if the result is shorter than this, it will not add trailing zeroes – it will appear as normal. It is therefore recommended to leave this as a high value.
Format number 
RIGHT('00000000'+ CONVERT(VARCHAR,123),8)
 Formats a number with leading zeroes. In the example, 123 is the number to be formatted (this could be a numeric field instead), and 8 is the total length of the formatted number. The example would therefore result in 00000123.
CAST as INT cast(1.2 as int) Returns the integer part of a number or numeric field. The example would return 1.
Check if null 
case when traineepot.terminationdate is null
then 'True'
when rtrim(traineepot.terminationdate) = ''
then 'True'
else 'False'
end

Checks if a field is empty (i.e. a blank string) or null and returns ‘True’ if it is and ‘False’ if it isn’t. The example checks if a learner’s termination date is empty or null.

Note that there are two ‘when’ statements for the field – the first one checks if the field is null and the second checks if it is blank. The second check uses the rtrim function to remove any empty spaces from the string so that it can be compared against a blank string.

Also please note that the results of True and False can be changed to whatever results you wish to show (e.g. Live and Terminated for the example).
Lookup 
(
	select top 1 longstring from lookup
	where tablename = 'TRAINEEPOT'
	and fieldname = 'GAINEDNVQ'
	and shortstring = traineepot.gainednvq
)
 Returns the lookup value of the specified table and field. The example retrieves the lookup for the ILR main aim Outcome.
Lookup (scheme-specific) 
(
	select top 1 longstring from lookup
	where tablename = 'TRAINEEPOT'
	and fieldname = 'GAINEDNVQ'
	and shortstring = traineepot.gainednvq
	and (schemeid = traineepot.schemeid
	or schemeid = -1)
)
 Returns the scheme-specific lookup value of the specified table and field. The example retrieves the lookup for the ILR main aim Outcome.
LOWER 
lower(trainee.lastname)
 Returns text in lower case. The example shows a learner’s surname in lower case.
Month name

Example 1 

datename(month, traineepot.startdate)

Example 2 

convert(char(3), traineepot.startdate, 0)
 Returns the month name for a given date. Example 1 will return the full month name (e.g. January, February), whereas example 2 will return a 3-character abbreviation (e.g. Jan, Feb). Both examples show the month name of a learner’s start date.
PAD

Example 1 

Maytas3.pad('mystring',3,'left')

Example 2 

Maytas3.pad('mystring',4,'right')

Adds whitespace of a specified length to either the left or the right of a string. Example 1 will add 3 spaces to the left of 'mystring', resulting in:

'   mystring'

Example 2 will add 4 spaces to the right of 'mystring', resulting in:

'mystring    '

A field can be used in place of the string.
PADZERO 
Maytas3.padzero('mystring',3,'left')

The same as above, only instead of whitespace it pads with zeroes. Therefore the example would result in:

'000mystring'
PRETTY 
Maytas3.pretty('mySTRING')
 Formats a string or field so that the first character is upper case and the rest is lower case. The example would change 'mySTRING' to 'Mystring'.
SQRT 
sqrt(9)
 Returns the square root of the provided number or field. The example would return 3, the square root of 9.
CAST as varchar 
cast(traineepot.pot as varchar)
 Converts a field or number to a string. The example would change a learner’s POT number to a string format.
CAST as int 
cast('1' as int)
 Converts a field or string to an integer. The example would change the string of 1 to integer format. Please note that the content of the provided field or string must be convertible to an integer, otherwise an error will occur. For example, ‘1a’ is not convertible to an integer.
Current time 
convert(varchar(8),getdate(),108)
 Returns the current time.
LTRIM / RTRIM 
ltrim(rtrim(trainee.lastname))
 LTRIM removes any whitespace to the left of a string, and RTRIM removes any whitespace to the right of a string. In the example, both LRTRIM and RTRIM are used to remove any spaces from either side of the learner’s surname.
Find data type 
(
	select data_type
	from information_schema.columns
	where table_name = 'TRAINEE'
	and column_name = 'LASTNAME'
)

Returns the data type of the specified field. The example checks the type of trainee.lastname, which is a varchar (i.e. a string). Any field can be checked by replacing TRAINEE and LASTNAME with the relevant table and fieldname.

Please note that this does not require the relevant table to be joined in the report.
UPPER 
upper(trainee.lastname)
 Converts a field or string to upper case. The example converts a learner’s surname to upper case.