Like what you see? Have a play with our trial version.

Overview

top
Calculated fields allow you to create basic calculations with the fields available in your report.
Note: a calculated field that you create in the report builder cannot be used across multiple reports. It is unique to your report only. If you create a set of calculated fields you wish to use across multiple reports you can either:


  1. Request the calculations to be included in the View by your administrator or
  2. Copy the report containing the calculations using the copy function – the copied report will contain the new calculated fields as well.


Why Use Calculated Fields?

When an administrator designs a View they may not create all the variables that you are likely to need in the course of your report writing. The intention of a view is to provide you with sufficient fields for you to be able to write the report you need and to use some of these fields as the basis for more complex calculations.


Simple Formula

Simple Formulas allow you to build calculations using the formula builder button interface. These may range from very simple addition or multiplication through to CASE statements.


  1. Click the + button at the bottom of the field list in the Data step of the report builder.
  2. The Calculated Field window will now appear. You will need to define the following:


    1. Provide a name for your formula in the Calculated Field Name box. This will be used as the field name in your table and/or chart output.
    2. Select Simple from the Formula Type dropdown list.

    3. Select the data type of the resulting field for this calculation from the Resulting Field Type dropdown list.

      If Auto Detect is selected, then the system will determine the nature of the result field itself. However, if a data type is specified by the user (eg, Numeric, text, etc) then the system will not perform a validation check upon saving the calculated field.

    4. Use the formula buttons and field drop down list to add components to your calculation. You cannot type directly into the calculation area of the window, each part of the formula needs to be added using buttons. Yellowfin will not permit you to select combinations of variables that are incompatible – therefore options that are greyed out will become active as you add in elements.
      Note: if you wish to type your calculation you may need to use the Freehand SQL option discussed later.


  3. Once finished click the Validate button to ensure your calculation is not missing any components.


  4. Click Save to complete your calculation. It will now be available in a folder called calculated fields at the bottom of the field list in the Data step.

Pre-Defined Formula

Using a pre-defined formula you can add calculation to your report that will be based on formula rules defined by an administrator or are system default.


  1. Click the + button at the bottom of the field list in the Data step of the report builder.
  2. The Calculated Field window will now appear. You will need to define the following:


    1. Provide a name for your formula in the Calculated Field Name box. This will be used as the field name in your table and/or chart output.
    2. Select Pre-Defined from the Formula Type drop down list.
    3. Choose the formula you wish to apply – the selection is specific to the type of database that your data is sourced from.
    4. You will now be provided with options to complete your calculation. Click on each parameter to define it.
    5. Provide a value using one of the options provided. These will vary depending on the type of value required.
  3. Click Save to complete your calculation.
  4. Your calculation will now be available in a folder called calculated fields at the bottom of the field list in the Data step.

Freehand SQL

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.


  1. Click the + button at the bottom of the field list in the Data step of the report builder.
  2. The Calculated Field window will now appear. You will need to define the following:



    1. Provide a name for your formula in the Calculated Field Name box. This will be used as the field name in your table and/or chart output.
    2. Select Freehand SQL from the Formula Type drop down list.

    3. Select the data type of the resulting field for this calculation from the Resulting Field Type dropdown list.

      If Auto Detect is selected, then the system will determine the nature of the result field itself. However, if a data type is specified by the user (eg, Numeric, text, etc) then the system will not perform a validation check upon saving the calculated field.

    4. Enter the SQL use wish to use to populate the field.
      Note: The Freehand SQL builder will require:
      1. An SQL SELECT fragment, not including the SELECT keyword or any FROM or WHERE clauses
      2. Columns referenced to exist in the view
      3. No aggregate functions (AVG, MIN, MAX, SUM, and COUNT) should be used.
  3. Once finished click the Validate button to ensure your calculation is not missing any components.
  4. Click Save to complete your calculation. It will now be available in a folder called calculated fields at the bottom of the field list in the Data step.

Edit a Calculated Field

To edit a calculated field:


  1. Hover over the calculation in the Field List of the Data step and click on the drop down menu to open it
  2. Select the Edit Calculation option

Copy a Calculated Field

To copy a calculated field:

  1. Hover over the calculation in the Field List of the Data step and click on the drop down menu to open it. Select the Copy option.

  2. A confirmation popup is displayed, click on Ok to create a copy of the selected field.

The new field will have the same name as the old field with “Copy” appended to the end. You can rename this field as required. Note that copying calculated fields also works at subquery level.

Delete a Calculated Field

To delete a calculated field:

  1. Hover over the calculation in the Field List of the Data step and click on the drop down menu to open it
  2. Select the Delete option



Formatting Calculated Field Columns 

Users can further format the resultant column of a calculated field. Click here for information on Column Formatting.

Note that a description setting is also provided for calculated field columns that allow users to provide a description to aid report writers to clearly understand the purpose and content of this field.


Calculated Total

Much like dimension and metric fields, you can also create an aggregated total on a calculated field. This is done by using the Calculated Total option, which uses the calculation to create the total.

Note: Calculated Total is not supported in charts, except Numeric Display.

 

  1. Bring up the menu on a calculated field, and select Totals.
  2. Then choose the Calculated Total option. This option is only available if the field is a calculation.



  3. The total summary of the calculated field will be added at the end of the report.



top