Yellowfin Quick How to – Custom Function: ‘What If’ scenario and Collaborative BI

Posted by: Yellowfin Team

Today, I want to share with you my recipe for creating a delectable Yellowfin cocktail – using Custom Functions, Parameters and Collaborative BI, to help your HR department with its decision-making process. Please feel free to experiment, and add your own ingredients, to make this cocktail drinkable (applicable) in other areas of your business.

Now, Imagine you work for an HR department. Your task is to find salary increase rates (%) for your company employees:

You can increase salaries by a greater percentage for seasoned/loyal employees, and slightly less for new employees
The overall salary increase should fit your assigned budget, and should not exceed $20000 per year

You have data saved in MS SQL Server as follows:

(Please note: You can use MS Excel spreadsheets as well as other types of Databases – just be aware of differences in SQL dialect when it comes to custom functions creation).

Next, assume that that your salary increases will be based on the following formula:

If an employee has worked for your company for more than X-years, than his increase will be Y percent. Employees that have worked at your company X-years or less, will receive a salary increase of Z percent.

Since we do not know the exact number for ‘Y ‘or ‘Z’ yet, we will be using Parameters to pass different values into our formula. More information on Parameters usage in Yellowfin can be found on our Wiki Pages >

We also need to know how many years (X) an employee has worked for a company up to the current date, which we can calculate by using the following SQL statement:

DATEDIFF (YY, EmploymentStartDate, GETDATE())

So, if we put everything together, our formula might look like this:

CASE WHEN DATEDIFF(YY, EmploymentStartDate, GETDATE())> X
THEN Sallary+(Sallary/100)*Y
ELSE Sallary+(Sallary/100)*Z END as IncreasedSallary

Since our formula has some date-time manipulations, we probably won’t be able to get away with using a simple Calculated Filed in Yellowfin. Instead, we’ll use a Custom Function. More detailed information on Custom Functions can be found on our Wiki Pages >

Now, let’s call:

X – YearsOfEmploymentCutOff
Y – IncreaseRateSeasonedEmployees
Z – IncreaseRateNewEmployees

In this instance, our custom function will look like the one below:

<!– Sallary WhatIf Scenario, Sql Server –>
<function>
<name>SallaryIncreaseCalculations, SQL Server</name>
<argument>
<index>1</index>
<name>EmploymentStartDate</name>
<datatype>datetime</datatype>
</argument>
<argument>
<index>2</index>
<name>YearsOfEmploymentCutOff</name>
<datatype>numeric</datatype>
</argument>
<argument>
<index>3</index>
<name>CurrentSallary</name>
<datatype>numeric</datatype>
</argument>
<argument>
<index>4</index>
<name>IncreaseRateSeasonedEmployees</name>
<datatype>numeric</datatype>
</argument>
<argument>
<index>5</index>
<name>IncreaseRateNewEmployees</name>
<datatype>numeric</datatype>
</argument>

<sql>
<![CDATA[
CASE WHEN DATEDIFF(year, $1, GETDATE())> $2
THEN $3+($3/100)*$4
ELSE $3+($3/100)*$5 END
]]>
</sql>
<groupby> CASE WHEN DATEDIFF(year, $1, GETDATE())> $2
THEN $3+($3/100)*$4
ELSE $3+($3/100)*$5 END</groupby>
<database>SQLServer</database>
<return>numeric</return>
</function>

Next, save this function in custom-functions.xml file, and re-start Yellowfin. Now, you’ll be able to see this function available among other Formula Templates:

Next, create all necessary parameters, and define all arguments for your function. Once complete, it’s ready to be used on your report.

Go to the Report Builder, and use your parameters as filters:

Now, you can pass different Parameters and see the resultant increase in overall salary expenditure, enabling you to ascertain whether it fits your budget.

As soon as you find the right combination of parameters, you can then create a Snapshot >

Now all you have to do is share it with your HR Manager, who in turn can experiment with parameters, and come up with their own combination. Together, you’ll be able to make a decision on which combination makes the most sense, suits your budget, and is most beneficial for your business.