Page History

...

...

概要

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.
   Image Removed
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.
   Image Removed
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.
   Image Removed
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.
   Image Removed
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.
   Image Removed
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.

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

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.
   Image Removed
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.
   Image Removed
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.

Yellowfinのビュービルダーを使って、データベースから読み出したデータを使って計算を行う、計算フィールドを作成することができます。これらの計算には、単純な四則演算だけでなく、複雑なSQLステートメントを含めることが可能です。

作成することができる計算フィールドの種類は以下の通りです： 

フィールドの新規作成

必要に応じ、計算を行うフィールドや同じカラムの書式の違うコピーなど、新しいフィールドを作成してビューに追加することが可能です。

1. 1. 新しいフィールドを作成するには、「未結合のフィールド」フォルダーリストの「計算フィールド」フォルダーを開きます。「式ビルダー」、「 フリーハンドSQL」、 「式テンプレート（カスタム関数）」、および「日付階層」サブフォルダーが表示されます。異なる方法で計算を作成できます。
   Image Added
   次に「新しい関数の作成」、「SQLの登録」、「（既存の）関数の選択」から、どのような方法でフィールドを作成するかを選択します（カスタム関数の使い方のセクションを参照してください）。
2. 方法を選択したらレポートフォルダーにドラッグします。編集セクションが表示されますので、まずは既存のフィールドと同様にメタデータを定義してください。
3. 「式」タブをクリックして、式の定義に進みます。「日付階層」を選択した場合を除いて、これが最初のタブに設定されます。