Creating a Calculated Column in Table Sidehead

Description

Foresight Analytics Platform uses the Fore programming language to display calculated values in table headers as:

• Separate columns in table sidehead.

• Separate rows in table heading.

The values in calculated columns and rows are calculated by the formula consisting of table dimension attributes.

NOTE. A number of calculated rows and columns may slow down table performance due to calculation of formulas of calculated rows and columns.

This example shows creation of a calculated column in table sidehead.

Requirements

Executing the example requires that the repository contains an express report with the EXPRESS_SLOTS identifier. This report must include a data table.

Add links to the Dimensions, Express, Metabase, Pivot, and Ui system assemblies.

Example

Sub UserProc;
Var
    mb: IMetabase;
    Report: IEaxAnalyzer;
    Pivot: IPivot;
    pHeader: IPivotHeader;
    HeaderSlots: IPivotHeaderEvaluatedSlots;
    Slot: IPivotHeaderEvaluatedSlot;
    Getter: IDataAreaTransformationsGetter;
    DataArea: IEaxDataArea;
    Slice: IEaxDataAreaSlice;
    SelSet: IDimSelectionSet;
    Trans: IEaxDataAreaTransformations;
    Tran: IEaxDataAreaTransformation;
    Data: Array;
    Target: IUiCommandTarget;
    Context: IUiCommandExecutionContext;
    Result: Variant;
Begin
    // Get current repository
    mb := MetabaseClass.Active;
    // Receive express report
    Report := mb.ItemById("EXPRESS_SLOTS").Edit As IEaxAnalyzer;
    // Get object, based on which data table is created
    Pivot := Report.Pivot;
    // Get parameters of dimensions located in sidehead
    pHeader := Pivot.LeftHeader;
    // Get collection of calculated columns located in sidehead
    HeaderSlots := pHeader.EvaluatedSlots;
    // Remove all existing calculated columns
    HeaderSlots.Clear;
    // Add  a new calculated column
    Slot := HeaderSlots.Add;
    // Get object used to work with column formula
    Getter := Slot As IDataAreaTransformationsGetter;
    // Add calculation formula
    DataArea := Report.DataArea;
    Slice := DataArea.Slices.Item(0);
    Trans := Slice.GetTransformations(Getter);
    SelSet := Pivot.Selection.CreateCopy;
    Tran := Trans.Add(SelSet, Null, -1);
    Tran.Enabled := True;
    // Create parameters to open expression editor
    Data := New Variant[4];
    Data[0] := Report;
    Data[1] := Tran;
    Data[2] := Null;
    Data[3] := SelSet;
    // Open expression editor, in which the user manually
    // sets expression for calculation of calculated column formula
    Target := WinApplication.Instance.GetPluginTarget("Express");
    Context := Target.CreateExecutionContext;
    Context.Data := data;
    Result := Target.Execute("ShowFormulaEditor", context);
    // Save changes in report
    (Report As IMetabaseObject).Save;
End Sub UserProc;

After executing the example the calculated column is added to the express report table sidehead: The formula for calculation of column values is set by the user in the opened expression editor.

For example, there is a report containing the following data table:

This table contains the city dimension in rows, the calendar dimension in columns, and the factor dimension is fixed.

After executing the example the expression editor opens. The following formula is created in the editor:

Thus, the calculated column that displays factor name is added to the table sidehead: