Grouping by Attributes of Sidehead and Heading Dimension Elements

Description

Foresight Analytics Platform enables the user to group sidehead or heading dimension elements by attributes. Values of grouping elements can be aggregated.

This example displays creating a grouping by attributes in table sidehead and heading and output of aggregated values to a grouping element.

Requirements

Executing the example requires that the repository contains a regular report with the REPORT identifier. The report contains a table.

A table data source is a cube that contains four dimensions:

Sidehead and heading dimensions have three attributes:

Add links to the Dimensions, Drawing, Express, ForeSystem, Matrix, Metabase, Pivot, Report, Tab system assemblies.

Example

Sub UserProc;
Var
    MB: IMetabase;
    Report: IPrxReport;
    Slice: IEaxDataAreaSlice;
    Pivotslice: IEaxDataAreaPivotSlice;
    Pivot: IPivot;
    DimInst: IDimInstance;
    PivotGroupItems: IPivotGroupItems;
    PivotGroupItem: IPivotGroupItem;
    PivotGroupAggregations: IPivotGroupAggregations;
    DimselectionSet, Selection: IDimselectionSet;
    Stylesettings: IDataAreaHeaderStyleSettingsBase;
    HeaderStyle, DataStyle: ITabCellStyle;
    Settings: IDataAreaHeaderSettingsBase;
    Key: Integer;
    Viewsettings: IEaxGridViewSettings;
    Pivotdim: IPivotDimension;
    Method: BasicAggregatorOperation;
Begin
    // Get repository
    MB := MetabaseClass.Active;
    // Get report
    Report := MB.ItemById("REPORT").Edit As IPrxReport;
    // Get report slice
    Slice := Report.DataArea.Slices.Item(0);
    // Get object for working with table
    Pivotslice := Slice As IEaxDataAreaPivotSlice;
    Pivot := Pivotslice.Pivot;
    // Set object for grouping by dimension element attributes
    PivotGroupItems := Pivot.AttributesGroups;
    // Determine parameters of displaying data table
    Viewsettings := (Report.DataArea.Views.Item(0) As IEaxGrid).ViewSettings;
    // Create a copy of dictionary selections
    DimselectionSet := Pivot.Selection.CreateCopy;
    // Remove selections
    DimselectionSet.Clear;
    // Add selection
    DimselectionSet.Add(Pivot.DimItem(0));
    DimselectionSet.Item(0).SelectElement(1, False);
    // Add dimension located in heading
    DimInst := pivot.DimItem(2);
    // Determine grouping attribute
    Key := DimInst.Attributes.Item(2).Attribute.Key;
    // Add elements to grouping
    PivotGroupItems.Clear;
    PivotGroupItem := PivotGroupItems.Add;
    PivotGroupItem.AttributeKey := Key;
    PivotGroupItem.Dimension := DimInst;
    // Set column formatting
    Stylesettings := PivotGroupItem As IDataAreaHeaderStyleSettingsBase;
    HeaderStyle := Viewsettings.GetStyleSettings(Stylesettings).Header;
    DataStyle := Viewsettings.GetStyleSettings(Stylesettings).Data;
    HeaderStyle.BackgroundBrush := New GxSolidBrush.Create(GxColor.FromName("Yellow"));
    DataStyle.Font.Color := GxColor.FromName("Black");
    DataStyle.BackgroundBrush := New GxSolidBrush.Create(GxColor.FromName("LightYellow"));
    // Add dimension located in sidehead
    DimInst := Pivot.DimItem(1);
    Pivotdim := Pivot.Dimensions.FindByKey(DimInst.Key);
    // Determine header formatting style
    Settings := Pivotdim As IDataAreaHeaderSettingsBase;
    Stylesettings := Pivotdim As IDataAreaHeaderStyleSettingsBase;
    HeaderStyle := Viewsettings.GetStyleSettings(Stylesettings).Header;
    HeaderStyle.Font.Bold := TriState.OnOption;
    Viewsettings.GetViewSettings(Settings).Hierarchical := TriState.OffOption;
    // Determine grouping attribute
    Key := DimInst.Attributes.Item(2).Attribute.Key;
    // Add elements to grouping
    PivotGroupItem := PivotGroupItems.Add;
    PivotGroupItem.AttributeKey := Key;
    PivotGroupItem.Dimension := DimInst;
    // Set row formatting
    Stylesettings := PivotGroupItem As IDataAreaHeaderStyleSettingsBase;
    HeaderStyle := Viewsettings.GetStyleSettings(Stylesettings).Header;
    DataStyle := Viewsettings.GetStyleSettings(Stylesettings).Data;
    HeaderStyle.BackgroundBrush := New GxSolidBrush.Create(GxColor.FromName("Green"));
    DataStyle.Font.Color := GxColor.FromName("White");
    DataStyle.BackgroundBrush := New GxSolidBrush.Create(GxColor.FromName("SkyBlue"));
    // Determine second grouping attribute
    Key := DimInst.Attributes.Item(1).Attribute.Key;
    // Add elements to grouping
    PivotGroupItem := PivotGroupItems.Add;
    PivotGroupItem.AttributeKey := Key;
    PivotGroupItem.Dimension := DimInst;
    // Set row formatting
    Stylesettings := PivotGroupItem As IDataAreaHeaderStyleSettingsBase;
    HeaderStyle := Viewsettings.GetStyleSettings(Stylesettings).Header;
    DataStyle := Viewsettings.GetStyleSettings(Stylesettings).Data;
    HeaderStyle.BackgroundBrush := New GxSolidBrush.Create(GxColor.FromName("LightGreen"));
    DataStyle.Font.Color := GxColor.FromName("White");
    DataStyle.BackgroundBrush := New GxSolidBrush.Create(GxColor.FromName("Turquoise"));
    // Set aggregation method for rows
    PivotGroupAggregations := PivotGroupItem.AggregationMethods;
    Method := BasicAggregatorOperation.Sum;
    Selection := DimselectionSet;
    PivotGroupAggregations.Add(Selection, Method);
    // Refresh object for working with table 
    Pivot.Refresh;
    // Save report
    (Report As IMetabaseObject).Save;
End Sub UserProc;

Imports Prognoz.Platform.Interop.Dimensions;
Imports Prognoz.Platform.Interop.Drawing;
Imports Prognoz.Platform.Interop.Express;
Imports Prognoz.Platform.Interop.ForeSystem;
Imports Prognoz.Platform.Interop.Matrix;
Imports Prognoz.Platform.Interop.Pivot;
Imports Prognoz.Platform.Interop.Report;
Imports Prognoz.Platform.Interop.Tab;
…
Public Shared Sub Main(Params: StartParams);
Var
    MB: IMetabase;
    Report: IPrxReport;
    Slice: IEaxDataAreaSlice;
    Pivotslice: IEaxDataAreaPivotSlice;
    Pivot: IPivot;
    DimInst: IDimInstance;
    PivotGroupItems: IPivotGroupItems;
    PivotGroupItem: IPivotGroupItem;
    PivotGroupAggregations: IPivotGroupAggregations;
    DimselectionSet, Selection: IDimselectionSet;
    Stylesettings: IDataAreaHeaderStyleSettingsBase;
    HeaderStyle, DataStyle: ITabCellStyle;
    GxColorCls: GxColorClass = New GxColorClassClass();
    GxSolidBrushCls: GxSolidBrush = New GxSolidBrushClass();
    Settings: IDataAreaHeaderSettingsBase;
    Key: uinteger;
    Viewsettings: IEaxGridViewSettings;
    Pivotdim: IPivotDimension;
    Method: BasicAggregatorOperation;
Begin
    // Get repository
    MB := Params.Metabase;
    // Get report
    Report := MB.ItemById["REPORT"].Edit() As IPrxReport;
    // Get report slice
    Slice := Report.DataArea.Slices.Item[0];
    // Get object for working with table
    Pivotslice := Slice As IEaxDataAreaPivotSlice;
    Pivot := Pivotslice.Pivot;
    // Set object for grouping by dimension element attributes
    PivotGroupItems := Pivot.AttributesGroups;
    // Determine parameters of displaying data table
    Viewsettings := (Report.DataArea.Views.Item[0] As IEaxGrid).ViewSettings;
    // Create a copy of dictionary selections
    DimselectionSet := Pivot.Selection.CreateCopy();
    // Remove selections
    DimselectionSet.Clear();
    // Add selection
    DimselectionSet.Add(Pivot.DimItem[0]);
    DimselectionSet.Item[0].SelectElement(1, False);
    // Add dimension located in heading
    DimInst := pivot.DimItem[2];
    // Determine grouping attribute
    Key := DimInst.Attributes.Item[6].Attribute.Key;
    // Add elements to grouping
    PivotGroupItems.Clear();
    PivotGroupItem := PivotGroupItems.Add();
    PivotGroupItem.AttributeKey := Key;
    PivotGroupItem.Dimension := DimInst;
    // Set column formatting
    Stylesettings := PivotGroupItem As IDataAreaHeaderStyleSettingsBase;
    HeaderStyle := Viewsettings.GetStyleSettings[Stylesettings].Header;
    DataStyle := Viewsettings.GetStyleSettings[Stylesettings].Data;
    GxSolidBrushCls.Create(GxColorCls.FromName("Yellow"));
    HeaderStyle.BackgroundBrush := GxSolidBrushCls;
    DataStyle.Font.Color := GxColorCls.FromName("Black");
    GxSolidBrushCls.Create(GxColorCls.FromName("LightYellow"));
    DataStyle.BackgroundBrush := GxSolidBrushCls;
    // Add dimension located in sidehead
    DimInst := Pivot.DimItem[1];
    Pivotdim := Pivot.Dimensions.FindByKey(DimInst.Key);
    // Determine header formatting style
    Settings := Pivotdim As IDataAreaHeaderSettingsBase;
    Stylesettings := Pivotdim As IDataAreaHeaderStyleSettingsBase;
    HeaderStyle := Viewsettings.GetStyleSettings[Stylesettings].Header;
    HeaderStyle.Font.Bold := TriState.tsOnOption;
    Viewsettings.GetViewSettings[Settings].Hierarchical := TriState.tsOffOption;
    // Determine grouping attribute
    Key := DimInst.Attributes.Item[2].Attribute.Key;
    // Add elements to grouping
    PivotGroupItem := PivotGroupItems.Add();
    PivotGroupItem.AttributeKey := Key;
    PivotGroupItem.Dimension := DimInst;
    // Set row formatting
    Stylesettings := PivotGroupItem As IDataAreaHeaderStyleSettingsBase;
    HeaderStyle := Viewsettings.GetStyleSettings[Stylesettings].Header;
    DataStyle := Viewsettings.GetStyleSettings[Stylesettings].Data;
    GxSolidBrushCls.Create(GxColorCls.FromName("Green"));
    HeaderStyle.BackgroundBrush := GxSolidBrushCls;
    DataStyle.Font.Color := GxColorCls.FromName("White");
    GxSolidBrushCls.Create(GxColorCls.FromName("SkyBlue"));
    DataStyle.BackgroundBrush := GxSolidBrushCls;
    // Determine second grouping attribute
    Key := DimInst.Attributes.Item[1].Attribute.Key;
    // Add elements to grouping
    PivotGroupItem := PivotGroupItems.Add();
    PivotGroupItem.AttributeKey := Key;
    PivotGroupItem.Dimension := DimInst;
    // Set row formatting
    Stylesettings := PivotGroupItem As IDataAreaHeaderStyleSettingsBase;
    HeaderStyle := Viewsettings.GetStyleSettings[Stylesettings].Header;
    DataStyle := Viewsettings.GetStyleSettings[Stylesettings].Data;
    GxSolidBrushCls.Create(GxColorCls.FromName("LightGreen"));
    HeaderStyle.BackgroundBrush := GxSolidBrushCls;
    DataStyle.Font.Color := GxColorCls.FromName("White");
    GxSolidBrushCls.Create(GxColorCls.FromName("Turquoise"));
    DataStyle.BackgroundBrush := GxSolidBrushCls;
    // Set aggregation method for rows
    PivotGroupAggregations := PivotGroupItem.AggregationMethods;
    Method := BasicAggregatorOperation.baoSum;
    Selection := DimselectionSet;
    PivotGroupAggregations.Add(Selection, Method);
    // Refresh object for working with table 
    Pivot.Refresh();
    // Save report
    (Report As IMetabaseObject).Save();
End Sub;

Result of Fore and Fore.NET Example Execution

Before calculation the example contained the data table:

After executing the example, grouping elements are displayed in sidehead and heading. The Sum aggregation method is set for sidehead grouping elements.

The table will look as follows:

See also:

IPivot.AttributesGroups