Custom SQL Report Functions

Custom SQL functions can be used to manipulate report data in advanced ways. This is most useful in situations where your data requirements are too complex to be achieved with the standard criteria. Please note that this requires a basic understanding of SQL.

An extensive list of custom SQL functions can be found on the List of Custom Report Functions page.

Create a Custom Function

1. Create or open a report and go to the Design tab.
2. The custom function will require a field of the same type as the intended results (e.g. if you are calculating dates, use a date field). It does not matter which particular field is used, as the results of the field will be replaced by the function. You can in fact add duplicate fields to a report (as above with STARTDATE), as the one with the function will have different results.
3. Select the field and click Edit.
4. Set the Output type to Custom. It is important that the type is Custom and not Expression.
5. The function can then be typed directly into the Function box (as above). Please note that this should be written in SQL, as the field will be replaced by whatever is entered here.
6. Click OK.

Example Custom Functions

The two example functions below can be used to calculate dates:

To find the difference between two dates:

DATEDIFF (DateType, TABLE.FIELD, TABLE.FIELD)

The DATEDIFF function finds the time difference between two dates, in days, months, weeks, etc. The type of time difference is the DateType and the two TABLE.FIELD variables are the dates. The dates can be taken from a field or entered as static dates (e.g. '01-AUG-2011'). If static dates are entered then they must be surrounded by single quotes.

For example, to find the number of days between the start of the contract year and a learner’s start date:

DATEDIFF (day, '01-AUG-2011', TRAINEEPOT.STARTDATE)

To add certain amount of time to a date:

DATEADD (DateType, Amount, TABLE.FIELD)

The DATEADD function adds a certain amount of time (e.g. days, months, weeks, etc) to a date. The type of time difference is the DateType, the number of days, months, weeks, etc, to add is the Amount and the date to add to is the TABLE.FIELD. The dates can be taken from a field or entered as static dates (e.g. '01-AUG-2012'). If static dates are entered then they must be surrounded by single quotes.

For example, to add 12 weeks to a planned visit date:

DATEADD (week, 12, VISITS.PLANDATE)

Available date types:

• year
• quarter
• month
• dayofyear
• day
• week
• hour
• minute
• second
• millisecond
• microsecond
• nanosecond

Additional functions can be found here.

Custom Function Notes

• If you are importing a report from Maytas 3 Report Generator / MList, the function will need to be rewritten and the output type will need to be changed.
• To use the current date, use GETDATE() as the date value within the function.

• If you would like to find out the months on programme using a pre-determined number of days, use the following function (with 31 days as an example):

  DATEDIFF (day, TRAINEEPOT.STARTDATE, GETDATE ()) /31

  

• If an error message is displayed saying Query execution failed for dataset 'DataSet_Main', this means there is a problem with the SQL in the report. Check the function to ensure the output type is Custom and that the function has been written correctly.