Custom Functions

<custom-function>

Most senior hierarchical object, encapsulates entire document. Only has one allow child type, <function>.

<custom-function>

<function>

This defines a unique function, and encapsulates the data fields needed to define it. Child objects include <name>, <argument>, <sql>, <return> and <aggregate>.

<function>

<name>

Name defines the display name of the custom function within the application. It is the primary identifier for each of the custom function, therefore the names of each function must be unique. Each function can only have one name only.

<function>

<argument>

Argument defines an argument (or parameter) that can be inserted into the custom SQL at report design time. Arguments are defined by 3 attributes, <index>, <name> and <datatype>.

<argument>

<index>

The index parameter of argument uniquely identifies the argument in the context of the function and must be an integral positive value. The index is used for inserting the argument at the defined location within the custom SQL statement. If the index is "1" then the argument will be replaced in the SQL statement for every instance of "$1".

<argument>

<name>

The name parameter of argument is the display name for the argument in the application.

<argument>

<datatype>

The datatype parameter of argument defines the datatype of the argument. This allows the application to only allow compatible columns and values to be entered into this argument. Datatype must be one of "numeric", "text" or "datetime".

<function>

<sql>

Sql defines the actual database SQL statement to be made for this custom function. The SQL in this field will be inserted into a parent SQL statement as a single column, so a full SELECT FROM WHERE is not required and therefore the SQL must be compatible with single column syntax. See the example below for a simple CASE WHEN ELSE END example of a single column custom function. This SQL can also contain variables where arguments should be inserted. "$1" will be replaced with the column or data value assigned to the argument with index 1.

<function>

<aggregate>

The aggregate parameter defines which columns are aggregated within the custom function. This tells the application to not place these columns in the GROUP BY clause when generating the report SQL. The value of the aggregate parameter can also be a argument variable, for instance "$1" for the argument with index 1.

<function>

<groupby>

The group by parameter defines which columns should be inserted into the GROUP BY clause when the application is generating the report SQL. The value of the groupby parameter can also be a argument variable, for instance "$1" for the argument with index 1.

<function>

<return>

The return function defines the data type of the information that is returned by the entire custom function. This must be one of "numeric", "text" or "datetime".