View Calculated Fields

Overview

With Yellowfin's view builder you can create derived columns based on the fields sourced from the database. These calculations can include simple mathematical calculations or more complex SQL statements.

You can create a number of different types of calculated columns including:

1. Pre-defined filters,
2. User Parameters,
3. Manually Created Calculations,
4. Custom Built Functions, or
5. Date Hierarchy Formatters.

These options will create very different field types as described below.

Create New Column

You can create new calculated fields within your view as required.

1. To create a new field open the Calculated Fields folder in the unattached folders list. Here you will see sub folders for formula builder, freehand SQL, Formula templates, or Date Hierarchy. These are different ways that you can create calculations.
   Select how you wish to create the field by either selecting to create a new calculated formula, or type in an SQL statement, or use an existing function as defined in the custom function XML file (See Using Custom Functions).
2. Choose the type you want and drag it into the report folders like you would a normal field. This will open the field edit section. Define the metadata for the field as usual.
3. Advance to the formula entry by clicking the Formula tab at the top of the dialog, unless you chose the Date Hierarchy, this is set on the first tab.

Formula Builder

The formula builder assists you to write valid formulas for your view. Calculated fields written in this way can be used as normal fields within a report - i.e. all functions can be applied to them such as aggregations etc. This is not possible with SQL calculated fields.

1. By selecting the Create New Formula option the formula generation dialog will open.
2. The formula builder will allow you to generate a valid formula that will be returned by this column. Highlighted buttons will help guide you to create a valid formula.
   
3. Test your formula by clicking the test formula link.

Case Statements

More complex calculations can be created using the case statement. The case statement allows you to create new values in columns based on business logic. For example IF age is less than 20 then print "Young".

1. To insert a CASE statement, click the CASE button. The When button will open a popup dialog. It operates similar to the formula builder, only allowing formula objects to be inserted where they are valid.
   
2. Once the CASE statement expressions have been created (Make sure you have included the END FUNCTION), clicking the OK link will return the generated expression to the formula builder.
3. On Save the formula builder will ask you to define the data type of the calculated column. It will query the database with the formula to populate the available types with formats that the formula is compatible with.
4. Once the calculated column is saved it will be added to the list of available columns in the fields view.
5. The column can then be treated as a normal field within the Report Data page.

SQL Editor

If you have SQL skills you may wish to write your SQL directly into the SQL edit box. In this case select the 'Enter SQL' option from the formula tab. This will open the SQL edit box.
Insert the SQL you wish to create your column.

• You should enter an SQL SELECT fragment, not including the SELECT keyword or any FROM or WHERE clauses.
• Any columns referenced must exist in this view, and aggregate functions (AVG, MAX, MIN, SUM and COUNT) should not be used.

Formula Templates / Custom Functions

Custom functions are functions that are hand designed and that are stored in XML format on the Yellowfin server. These functions usually consist of advanced SQL functions that cannot be easily generated by the formula builder. These functions are configured by your system administrator.

1. Select the type of calculated field as Formula, and select the formula you wish to use from the drop down menu.
2. Functions defined in the XML file will require you to enter a number of parameters (or arguments). Parameters can be chosen from a list of columns where the data type matches the data type of the give parameter.
   
3. Assign a value by clicking each Argument and assigning a value to it. The value can either be a fixed data value or a column reference.
   
4. Once values for each of the arguments has been assigned, click the save button to save the column to the list.

Creating New Custom Functions

Custom functions are a configurable item within your own installation of Yellowfin. To add new custom XML functions into the application contact your system administrator or see Custom Functions for more information.

Date Hierarchy Fields

These templates allow you to convert dates and timestamps into various formats, which can then be used to build detailed date drill down hierarchies. The list of available formats will vary depending on source database type.

1. Select the date format you want in your hierarchy and drag the field into an appropriate category.
   
2. Set up the meta data as normal
3. In the bottom of the tab, select the field parameter you want to base your field on. Some of the formats require a timestamp as input, so the list you can choose from will differ depending on the format.
4. Format your field as normal and save.

Possible fields include (based on an SQL Server source connection):

Field	Output Type	Input
Date	Date	Date or Date + Time
Year	Integer	Date or Date + Time
Month	Integer	Date or Date + Time
Month Name	Varchar	Date or Date + Time
Day of Month	Integer	Date or Date + Time
Quarter	Integer	Date or Date + Time
Week of Year	Integer	Date or Date + Time
Day of Year	Integer	Date or Date + Time
Day of Week	Integer	Date or Date + Time
Day Name	Varchar	Date or Date + Time
Hour	Integer	Time
Minute	Integer	Time
Second	Integer	Time
Week Start Date	Date	Date or Date + Time
Week End Date	Date	Date or Date + Time
Month Start Date	Date	Date or Date + Time
Month End Date	Date	Date or Date + Time
Year Start Date	Date	Date or Date + Time
Year End Date	Date	Date or Date + Time

See Drill Down Hierarchies for more information.

Pre-Defined Filters

Pre-defined filters can be created to assist users with adding filters or conditions to their reports that ensure the data they require is easy to extract.
The use of pre-defined filters is especially useful in instances where:

1. A common set of filters are used by report writers - such as location, or business unit; or
2. If there are particularly complex filters that can be built ahead of time and are commonly used.

Create New Filter

1. Filters can only be created using the formula builder. Like a standard calculated field select the filter options from the unattached fields list. Drag the filter option into the report folder list.
   
2. On dragging the field into your folder you will be presented with the filter formula builder.
3. Select the field you wish to filter and then use the operator, brackets, and or function buttons and text field to build your filter.
   
4. Click OK to save your filter and include it into your field list. The filter will be highlighted with a filter icon.

Filter Operands

When defining a filter initially set the operand drop down to the desired value. The options within the drop down will vary based upon the type of field that the filter is to be applied to.