Date: Fri, 29 Mar 2024 06:18:47 -0600 (MDT) Message-ID: <1198864211.2883.1711714727255@confluence-external-wiki> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_2882_1924838015.1711714727255" ------=_Part_2882_1924838015.1711714727255 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
The XML schema allows for the custom defined SQL functions to have param= eters embedded so that numeric or column values can be assigned when the co= lumn is inserted into a report.
To add new XML functions into Yellowfin:
Yell=
owfin/appserver/webapps/ROOT/WEB-INF/
See Calculated Fields<= /a> for more information on using Custom Functions within Yellowfin.= p>
Parent Object |
Object |
Description |
---|---|---|
|
|
Most senior hierarchical object, encapsulates=
entire document. Only has one child type, |
|
|
This defines a unique function, and encapsula=
tes the data fields needed to define it. Child objects include |
|
|
Name defines the display name of the custom f= unction within the application. It is the primary identifier for each of th= e custom function, therefore the names of each function must be unique. Eac= h function can only have one name only. |
|
|
Argument defines an argument (or parameter) t=
hat can be inserted into the custom SQL at report design time. Arguments ar=
e defined by 3 attributes, |
|
|
The index parameter of argument uniquely iden= tifies 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 =E2=80=9C1=E2=80= =9D then the argument will be replaced in the SQL statement for every insta= nce of =E2=80=9C$1=E2=80=9D. |
|
|
The name parameter of argument is the display= name for the argument in the application. |
|
|
The datatype parameter of argument defines th= e datatype of the argument. This allows the application to only allow compa= tible columns and values to be entered into this argument. Datatype must be= one of the following:
|
|
|
SQL defines the actual database SQL statement= to be made for this custom function. The SQL in this field will be inserte= d into a parent SQL statement as a single column, so a full SELECT FROM WHE= RE is not required and therefore the SQL must be compatible with single col= umn syntax. See the example below for a simple CASE WHEN ELSE END example o= f a single column custom function. This SQL can also contain variables wher= e arguments should be inserted. =E2=80=9C$1=E2=80=9D will be replaced with = the column or data value assigned to the argument with index 1. |
|
|
The aggregate parameter defines which columns= are aggregated within the custom function. This tells the application to n= ot place these columns in the GROUP BY clause when generating the report SQ= L. The value of the aggregate parameter can also be a argument variable, fo= r instance =E2=80=9C$1=E2=80=9D for the argument with index 1. |
|
|
The group by parameter defines which columns = should be inserted into the GROUP BY clause when the application is generat= ing the report SQL. The value of the group by parameter can also be a argum= ent variable, for instance =E2=80=9C$1=E2=80=9D for the argument with index= 1. |
|
|
This specifies which database this function s= hould be available for. If none are specified it will be shown for all. The= re should be one object per database. Examples are: SQLServer, PostgreSQL, = OpenEdge, Progress, Oracle, DB2, Access, Notes, ODBC, HSQL, or MySQL. = td> |
|
|
The return function defines the data type of = the information that is returned by the entire custom function. This must b= e one of the following:
|
<= ?xml version=3D"1.0" encoding=3D"ISO-8859-1"?> <custom-functions> <!-- functions are stored by name, so names must be unique, even across = different databases --> =20 <!-- ratio function --> <function> <name>Ratio</name> <argument> <index>1</index> <name>Numerator</name> <datatype>numeric</datatype> <!-- numeric, text, datet= ime --> </argument> <argument> <index>2</index> <name>Denominator</name> <datatype>numeric</datatype> <!-- numeric, text, datet= ime --> </argument> <sql> <![CDATA[ CASE WHEN SUM($2) !=3D 0 THEN SUM($1) / SUM($2) ELSE NULL END ]]> </sql> <aggregate>$1</aggregate> <aggregate>$2</aggregate> <database>SQLServer</database> <!-- Available for what D= Bs? SQLServer, PostgreSQL, OpenEdge, Progress, Oracle, DB2, Access, Notes, = ODBC, HSQL, MySQL --> <database>HSQL</database> <return>numeric</return> <!-- numeric, text, datetime --= > </function> </custom-functions>