Page Comparison

{anchor:top}
{toc: class=contents}
h2. Overview 
{styleclass: Class=topLink}[top|#top]{styleclass}
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: 
# Pre-defined filters, 
# User Parameters,
# Manually Created Calculations, 
# Custom Built Functions, or
# Date Hierarchy Formatters.  

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


h2. Create New Column
{styleclass: Class=topLink}[top|#top]{styleclass}
You can create new calculated fields within your view as required.
# 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)._
# 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.
# 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.


h2. Formula Builder
{styleclass: Class=topLink}[top|#top]{styleclass}
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.
# By selecting the Create New Formula option the formula generation dialog will open.
# 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. 
!109.png|thumbnail,border=1!
# Test your formula by clicking the test formula link. 


h2h3. Case Statements
{styleclass: Class=topLink}[top|#top]{styleclass}
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".
# 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.
!110.png|thumbnail,border=1!
# 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.
# 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.
# Once the calculated column is saved it will be added to the list of available columns in the fields view.
# The column can then be treated as a normal field within the Report Data page.


h2. SQL Editor 
{styleclass: Class=topLink}[top|#top]{styleclass}
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.


h2. Formula Templates / Custom Functions
{styleclass: Class=topLink}[top|#top]{styleclass}
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.
# Select the type of calculated field as *Formula*, and select the formula you wish to use from the drop down menu.  
# 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.
!111.png|thumbnail,border=1!
# 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.
!112.png|thumbnail,border=1!
# Once values for each of the arguments has been assigned, click the save button to save the column to the list.


h2. Creating New Custom Functions
{styleclass: Class=topLink}[top|#top]{styleclass}
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.

 
h2. Date Hierarchy Fields
{styleclass: Class=topLink}[top|#top]{styleclass}
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.
# Select the date format you want in your hierarchy and drag the field into an appropriate category.
!115.png|thumbnail,border=1!
# Set up the meta data as normal
# 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.
# Format your field as normal and save.

h4h3. {expand:title=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|
{expand}

bq. See [Drill Down Hierarchies] for more information.

 
h2. Pre-Defined Filters 
{styleclass: Class=topLink}[top|#top]{styleclass}
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:
# A common set of filters are used by report writers - such as location, or business unit; or 
# If there are particularly complex filters that can be built ahead of time and are commonly used.


h2h3. Create New Filter
{styleclass: Class=topLink}[top|#top]{styleclass}
# 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.
!113.png|thumbnail,border=1!
# On dragging the field into your folder you will be presented with the filter formula builder.
# Select the field you wish to filter and then use the operator, brackets, and or function buttons and text field to build your filter.
!114.png|thumbnail,border=1!
# Click OK to save your filter and include it into your field list.  The filter will be highlighted with a filter icon.


h2h3. Filter Operands
{styleclass: Class=topLink}[top|#top]{styleclass}
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.  

{expand:title=Possible values for the operand include:}
||Operand||Description||
|Equal to|Equal to a single alphanumeric or string value|
|Greater than|Greater than a single alphanumeric or string value|
|Greater than or equal to|Greater than or equal to a single alphanumeric or string value|
|Less than|Less than a single alphanumeric or string value|
|Less than or equal to|Less than or equal to a single alphanumeric or string value|
|Different from|Not equal to or different from a single alphanumeric or string value|
|Between|Between variable 1 and variable 2 these will need to be legitimate parameters such as date, age etc.|
|Not Between|Not between variable 1 and variable 2 these will need to be legitimate parameters such as date, age etc.|
|In List|One or more alphanumeric or string values|
|Not In List|Two or more alphanumeric or string values|
|Is Null|Record contains no value for selected attribute.  No Parameter can be set|
|Is Not Null|Record contains a value for selected attribute.  No Parameter can be set|
|Like|Records that contain the same letter or letters.|
|Starts With|String starts with letter or letters|
|Ends With|String end with letter or letters|
{expand}
\\
\\
{horizontalrule}
{styleclass: Class=topLink}[top|#top]{styleclass}