How to Implement KeyFigure based Validation Rules in SAP Analytics Cloud Planning Templates

[mansi_dandavate]

Lot of times users have requirements when they want to restrict the value of a certain measure to allow users to only input certain values. Eg. You have a quantity measure and you want to restrict users from entering a negative value.

Similarly you may have to run some validation rules on the measures that user is inputting in the template to make sure the calculations are correct. Lets take an example.

We have Budget amount that user inputs in the template. Then you have monthly spread where he needs to input how much budget they will spend in each of the months in the budget year.

Then another amount is input by the user which is after budget year so this is the budget they will spend after the budget year and finally you have the unutilized budget which is Budget amount – monthly spread – After budget year. So in this case the unutilized budget should not be negative.

Such kind of checks are required in the input templates. We have option to create validation rules at the model level but these rules check  if planners can only enter data successfully for the valid dimension member combinations specified in the validation rule. 

So these validation rules cant be used in such requirements. The only option in our case if to use SAC scripts.

Below is the script that can be written in the OnResultChanged Event of the table.

var array = Table_1.getDataSource().getResultSet() ;

for (var i=0;i<array.length;i++)

{

 InputKey =  ( array[i][Alias.MeasureDimension].id ) ;

         if (InputKey  === "Key" )

         {                       InputKey _val = ( array[i][Alias.MeasureDimension].formattedValue ) ;

                           if (InputKey _val === "1")

                                           {}

                             else if(InputKey _val === "2")

                                  {}

                             else if(InputKey_val === "3")

                                            {}

                             else if (InputKey _val === "4")

                                            {}

                             else if (InputKey _val === "5")

                                            {}

                  else

                                           {

Application.showMessage(ApplicationMessageType.Error, "Key must be between 1 to 5");

                                             }

        }

Quantity =  ( array[i][Alias.MeasureDimension].id ) ;   

  if (Quantity  === "Quantity" )

              {    Quantity_Keyval = ( array[i][Alias.MeasureDimension].formattedValue ) ;

                            if (Quantity_Keyval< "0")

                                           {

                         Application.showMessage(ApplicationMessageType.Error, "Quantity cannot be negative");

              }

       }

 BudgetUn =  ( array[i][Alias.MeasureDimension].id ) ;

 if (BudgetUn  === "30021035-8936-4284-3557-115183456351" )

// Check if unutilized Budget is negative

              {

                             BudgetUn_Keyval = ( array[i][Alias.MeasureDimension].formattedValue ) ;

                             if (BudgetUn_Keyval< "0")

                                           {

                                                         Application.showMessage(ApplicationMessageType.Error, "Unutilized Budget cannot be negative");

                        }

}            

}

Also make sure below list of variables are created in the Outline. All the variables will be of type string.

Quantity

Quantity_Keyval

BudgetUn

BudgetUn_Keyval

InputKey

InputKey_val

If you want to use any calculated or a restricted measure in the validation you need to check the technical name of the measure in console. Eg. See below console screenshot which shows the technical name of the Unutilized Budget amount.

Once you implement the script and execute the story, if user enters any invalid values then we get an error message as below:

However after closing the error message if I still try to publish the data the system will publish and will not check anything.

So now we have two options either we ask the planners to be responsible enough and not to publish anything if they get error messages.

The other option is to implement a custom publish button and disable standard publish in the story.

Below script can be added in the custom publish button so that if users enter any invalid values they cannot publish the data.

var array = Table_1.getDataSource().getResultSet() ;

var temp = 0 ;

for (var i=0;i<array.length;i++)

{            Quantity =  ( array[i][Alias.MeasureDimension].id ) ;

               if (Quantity  === "Quantity" )

              {

                             Quantity_Keyval = ( array[i][Alias.MeasureDimension].formattedValue ) ;

                             if (Quantity_Keyval >=0 )

                                           {}

                  else

                                           { temp = 1 ;

                                           }

                  }

 }

if (temp === 1)

              {

Application.showMessage(ApplicationMessageType.Error, "Quantity cannot be negative");

              }

else

            {

                Table_1.getPlanning().getPublicVersion("Budget").publish();

                 }

Thus as shown above the key figure based validation option provides a powerful alternative in SAC planning templates to ensure users enter valid set of values before publishing the data.

[spurwar]

@mansi_dandavate : How is the overall performance with such kind of checks for each data entry. can you share your experience in handling such sceanrios?