Using Advanced Features of Compare by Expression Validation Rule

The Compare by Expression validation rules enable the use of series from different time series databases, data from different calendar frequencies and data from different revisions in calculation. A number of features exists:

On using series from different time series databases:
- Data sources must be specified in expressions for series. If no source is specified, the current time series database that contains validation is used. For example: WEOProduction!BCA BCA.
- Validation results are loaded to the database that contains the validation.
- Several different time series databases can be used for different expression terms.
- On working with series mode (Ad-hoc), series from another database are searched in series mode, if series are not found, data will be loaded from database.
On using different calendar frequencies:
- Calendar frequency must be specified in the expressions for series. If the frequency is not specified, validation filter frequency is used. For example: BCA.A <> BCI.A.
- Term frequencies in the expression must match.
- Validation results are loaded to the frequency that is specified in the validation settings.
- On working with the series mode (Ad-hoc) series with specified frequency are searched in the series mode, if the series are not found, the rule returns no exceptions.
- On working with the Collapse function, input term frequency must be equal to validation frequency.
On using series from different revisions.
- Only great revisions (labels) of the current validation catalog can be used in the expression. Actual data is always used for the terms from other databases. For example: V1:X=V2:X where V1 is the first revision and V2 is the second revision.
- Validation results are loaded to the actual data.
- On working with the series mode (Ad-hoc) series with the specified revision are searched in the series mode, if the series are not found, data is loaded from the database.

To execute the example, you will need:

The Compare by Expression validation rule with the 111 key. Validation has annual frequency.
Time series database with the 333 key. This database does not contain the validation specified above.
A form with the MetaAttributesBreadcrumb component that has the MetaAttributesBreadcrumb1 identifier. MetaAttributesBreadcrumb is set up for the time series database specified above.
This database contains the COUNTRY and INDICATOR factor attributes. Before starting the example select these attributes from MetaAttributesBreadcrumb.

Calculation is set up to the formula:

{RussiaPavel!Argentina|NGDP_D.Q} <> collapse({Argentina|NGDP_D.Q:LastRevision}, MsCollapseType.Average, MsFrequency.Annual)

Where:

RussiaPavel. Identifier of the time series database with the 333 key.
Argentina. The first element of the dictionary, to which the Country attribute refers.
NGDP_D. The first element of the dictionary, to which the Factor attribute refers.

It is also necessary to add links to the Metabase, Ms, Dimensions, Rds, Cubes, Forms system assemblies.

Sub UserProc;
Var
    mb: IMetabase;
    filter: IValidationFilter;
    customV: IValidationCustom;
    Transform: IMsFormulaTransform;
    variable: IMsFormulaTransformVariable;
    dimSelection: IDimSelection;
    metaAttribute: IMetaAttribute;
    attrVal: IMsMetaAttributeValue;
    rub: IRubricator;
    BreadcrumbItems: IBreadcrumbItems;
    Slices: IMsFormulaTransformSlices;
Begin
    mb := MetabaseClass.Active;
    // get validation rule by its key
    filter := mb.Item(111).Edit As IValidationFilter;
    customV := filter.Details As IValidationCustom;
    // variable of other database
    Transform := customV.Transform;
    variable := Transform.Inputs.Item(0);
    rub := mb.Item(333).Bind As IRubricator;
    // modify variable data source
    variable.SetStub(rub As IVariableStub);
    // as the data source was modified the variable selection must be readjusted
    // set the first dictionary element, to which the Country attribute refers
    BreadcrumbItems := MetaAttributesBreadcrumb1.Items;
    metaAttribute := (BreadcrumbItems.Item(0) As IMetaAttributeBreadcrumbItem).MetaAttribute;
    dimSelection := MetaAttributesBreadcrumb1.GetAttributeValue(metaAttribute);
    Slices := variable.Slices;
    Slices.Item(0).Selection.Item(0).DeselectAll;
    Slices.Item(0).Selection.Item(0).SelectElement(dimSelection.Element(0), False);
    //  set the first dictionary element, to which the Factor attribute refers
    metaAttribute := (BreadcrumbItems.Item(1) As IMetaAttributeBreadcrumbItem).MetaAttribute;
    dimSelection := MetaAttributesBreadcrumb1.GetAttributeValue(metaAttribute);
    Slices.Item(0).Selection.Item(1).DeselectAll;
    Slices.Item(0).Selection.Item(1).SelectElement(dimSelection.Element(0), False);
    //set quarterly frequency
    Slices.Item(0).MetaAttributeValueList.Clear;
    metaAttribute := rub.Facts.Attributes.FindByKind(MetaAttributeKind.CalendarLevel);
    attrVal := Slices.Item(0).MetaAttributeValueList.Add(metaAttribute);
    attrVal.Value := DimCalendarLevel.Quarter;
    // variable with the specified frequency and revision, that belongs to the current database
    rub := variable.VariableStub As IRubricator;
    variable := Transform.Inputs.Item(1);
    Slices.Item(0).MetaAttributeValueList.Clear;
    //set quarterly frequency
    metaAttribute := rub.Facts.Attributes.FindByKind(MetaAttributeKind.CalendarLevel);
    attrVal := Slices.Item(0).MetaAttributeValueList.Add(metaAttribute);
    attrVal.Value := DimCalendarLevel.Quarter;
    //set revision to the last one before the relevant data, thus pass the -1 value
    metaAttribute := rub.Facts.Attributes.FindByKind(MetaAttributeKind.Revision);
    attrVal := Slices.Item(0).MetaAttributeValueList.Add(metaAttribute);
    attrVal.Value := -1;
    // save validation rule
    (filter As IMetabaseObject).Save;
End Sub UserProc;

After executing the example calculation options of the Compare by Expression validation rule will be changed.