Contents
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.
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, Ui system assemblies. For the Fore.NET example, add a link to the ForeSystem system assembly.
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;
Imports Prognoz.Platform.Interop.Dimensions;
Imports Prognoz.Platform.Interop.Express;
Imports Prognoz.Platform.Interop.ForeSystem;
Imports Prognoz.Platform.Interop.Pivot;
Imports Prognoz.Platform.Interop.Ui;
…
Public Shared Sub Main(Params: StartParams);
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;
Wa: IWinApplicationClass;
Target: IUiCommandTarget;
Context: IUiCommandExecutionContext;
Result: object;
Begin
// Get current repository
mb := Params.Metabase;
// 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, uinteger.maxValue);
Tran.Enabled := True;
// Create parameters to open expression editor
Data := New object[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
Wa := New WinApplicationClassClass();
Target := Wa.Instance[Null].GetPluginTarget("Express");
Context := Target.CreateExecutionContext();
Context.Data := data;
Result := Target.Execute("ShowFormulaEditor", context, Null);
// Save changes in report
(Report As IMetabaseObject).Save();
End Sub;
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:
See also: