How To Use Yellowfin Custom Functions

A Custom Function is a feature in Yellowfin calculated fields that can be used to define a calculation in advance and use that formula by simply specifying arguments.

Using Custom Functions, you can define calculations that cannot be created in calculated fields, or define frequently used calculations to save time when creating calculated fields.

In this blog, we will take some time to introduce Yellowfin Custom Functions that can be used in calculated fields, how they work, and how to create and leverage them in reports.

How Custom Functions Work

Custom Functions allow you to define calculations that use database functions. You can use arithmetic functions to calculate and obtain the metrics needed for analysis, or use string functions to create the axes needed for aggregation, however, note that the functions available depend on the type of database you are using.

Several Custom Functions are already defined in custom-functions.xml. Let's check the contents of Ratio as an example to see how the existing Custom Functions are defined.

In the figure above, a function is defined in lines 9-34. You can ensure the explanation of the individual tags in the Yellowfin Wiki, but we can see that Numerator and Denominator are declared as arguments to the function. The ratio calculation is enclosed in <sql> tags. Since ‘<’ and ‘>’ are sometimes used as comparison operators in formulas, they are enclosed in a CDATA section.

As for the content of the calculation, it does not seem to be a difficult calculation. A calculation for a ratio that avoids a division by zero error that occurs when the denominator of the division is zero. The Ratio calculation can be created with a simple formula in the calculation field, but it takes a lot of time and effort to create a CASE statement each time the ratio is calculated. With a Custom Function, you can create a calculated field for the ratio calculation by simply setting the fields to be passed as the argument numerator and denominator, which leads you to save time in report creation.

The ability to define calculations that cannot be created with simple formulas is one of the attractive points of Custom Functions, but another strong point is that they save time and effort in report creation by defining frequently used calculations in advance, such as Ratio as an example above.

How to Create a Custom Function

Using a sample PostgreSQL database as the data source and referring to the Yellowfin wiki and PostgreSQL documentation, we will create a Custom Function that can be used with PostgreSQL.

In this case, we will create a Custom Function that calculates the total per group using PostgreSQL's window function.

If you want to get a total per group, you usually use a subquery, but this Custom Function allows you to calculate the total per group without using a subquery. It may be difficult to use subqueries if you have no knowledge of SQL, so many people may feel that using subqueries raises the hurdle of creating reports. If you don’t have to use subqueries, that hurdle will be lowered considerably.

If you add the above to custom-functions.xml and restart Yellowfin, you will be able to use a Custom Function to calculate the ‘Total per group’.

You can display the amount per title and the total amount per category by displaying them side by side with the category, title, and amount of the DVD.

From here, you could further create a calculated field that uses the amount and the total amount per category to calculate the sales composition ratio per category. Or perhaps a Custom Function could be created to calculate the percentage by group.

A Custom Function to calculate the percentage by group might look something like this.

Conclusion

Yellowfin has a feature called Custom Functions that can be easily used by defining complex calculations in advance. Although knowledge of SQL is required to define a Custom Function, by having a person with knowledge of SQL define it in advance, even a person without knowledge of SQL can perform complex calculations. By utilizing Custom Functions, we may be able to broaden the range of report writers.