Calculating Relations in Standard Cube Using Custom Function

To execute the example, make sure that the repository contains a table with data by days, a calendar dictionary containing the Days level, and a standard cube. Set up the following for the standard cube:

  1. On the Facts page set two facts: Value and Previous year.

  2. On the Fact Binding page add a table to data sources and bind the table's numeric field with the Value fact.

  3. On the Dimensions page add as a cube dimension calendar dictionary and bind dimension to data source field containing date, by the Primary Key of Days Block index.

Add links to the Cubes, Dimensions system assemblies.

Function GetLag(DimInst: IDimInstance; El: Integer): Integer;
Var
    Result: Integer;
Begin
    // Execute calculation
    If CalendarDimension.Level(DimInst, El) = DimCalendarLevel.Day Then
        Result := CalendarDimension.Day(DimInst, CalendarDimension.Shift(DimInst, El, 365));
    Else
        Result := -1;
    End If;
    // Return result
    Return Result;
End Function GetLag;

Public Function Lag(T: Variant): Variant;
Var
    Cube: ICubeInstance;
    CubeDest: ICubeInstanceDestination;
    DimInsts: ICubeInstanceDimensions;
    DimInst: IDimInstance;
    Res: Array Of Integer;
    I: Integer;
    Cnt: Integer;
Begin
    // Get the current cube
    Cube := CubeClass.CurrentCube;
    CubeDest := Cube.Destinations.DefaultDestination;
    // Find calendar dimension
    DimInsts := CubeDest.Dimensions;
    Cnt := DimInsts.Count;
    For I := 0 To Cnt - 1 Do
        If DimInsts.Item(I).Dimension.IsCalendar Then
            DimInst := DimInsts.Item(I);
        End If;
    End For;
    // Determine whether it is possible to have an array as input parameter T
    If T.VarType = ForeVariantType.Matrix Then
        Res := T As Array Of Integer;
        Cnt := Res.Length;
        For I := 0 To Cnt - 1 Do
            Res[I] := GetLag(DimInst, Res[I]);
        End For;
        // Return result
        Return Res;
    Else
        //Return integer value    
        Return GetLag(DimInst, T);
    End If;
End Function Lag;

After creating a unit open the standard cube editing wizard and execute the operations:

  1. On the Relations page add a relation named Previous Year Value, with the YEAR_BEFORE identifier and set up relation as the following custom function: [Unit identifier].Lag(T) for Fore.

  2. On the Calculated Facts page for the Previous Year fact specify the formula Previous year value[Value] in the formula editor. After the formula is saved, it looks as follows: YEAR_BEFORE[@[1]]. In the calculated fact editing dialog box select the Calculate by Physical Data checkbox in order that the custom function handles and returns array of numeric values on relations calculation.

As a result of relation in the calculated fact formula, value for the Previous Year fact is taken from the same day of the previous year, for example:

See also:

Examples