Run Rule on Selected Cells in Form

Author: Joyce Zhou

Introduction

Users can run complex Business Rules on any selected data cells in a Form. A popular scenario that uses this functionality is allocating data (top-down) at different levels of aggregation.

 

 

Example

In our sample application we illustrate the use for this case. In the application, we provide two methods to allocate data:

1. Set a target at any level of the hierarchy, and the rule will allocate the target data to the leaves proportionally.
2. Increase or decrease any value by a certain percentage along the hierarchy, and the rule will adjust all its leaf members value by the percentage.

 

Users can select one or multiple cells in the Form, right-click, and select the relevant Rule.

 

 

In the pop-out window users can select a Rule to execute. Here, let’s select Adjust by Percentage, enter .10, and click Run & Close.

 

 

The Post Invoice is a parent member. Its value is aggregated from its children. We can see in the Form, the value of all its leaf members has been increased by 10%, which results in a 10% increase of the parent member.

 

 

 

Configure in Modeler

To use this functionality, the Include selection facts option of the Rule has to be checked in the ADVANCED tab in the MODELER.

 

 

When this option is enabled, there are three parameters that will be available in the Rule.

• @SelectionFactsContext – dbo.tMembers READONLY
• @SelectionFactsValue – FLOAT
• @SelectionFactsOverlap – BIT

 

The @SelectionFactsContext is a table that contains a list of Dimension Members associated with facts based on the selected cell. See the screenshot below for an example. This model contains four dimensions – Account, Time, Entity, and Product.

 

 

When we run Rules on the selected cell, here is the @SelectionFactsContext.

 

 

The @SelectionFactsValue is the selected cell value. In this example it's 2,277.

The @SelectionFactsOverlap detects whether the selected cells overlap each other in terms of the associated fact records for each cell. If two cells each share the same fact records, then @SelectionFactsOverlap will be set to 1.

Here is the definition of the SQL rule.

 

IF @SelectionFactsOverlap = 1
    PRINT N'<ERROR>Please ensure the selected cells do not have overlapping data.'
ELSE
    UPDATE F
    SET F.Value = F.Value * (1 + @Percentage)
    FROM [dbo].[F_Data Spread_CoreMG_Writeback] F
    WHERE AccountID IN (SELECT MemberID FROM @SelectionFactsContext WHERE ModelDimension = N'Account')
    AND TimeID IN (SELECT MemberID FROM @SelectionFactsContext WHERE ModelDimension = N'Time')
    AND ScenarioID IN (SELECT MemberID FROM @SelectionFactsContext WHERE ModelDimension = N'Scenario')
    AND EntityID IN (SELECT MemberID FROM @SelectionFactsContext WHERE ModelDimension = N'Entity')
    AND ProductID IN (SELECT MemberID FROM @SelectionFactsContext WHERE ModelDimension = N'Product')

 

Please note, when multiple cells are selected, cell context will be passed in to the Rule one after another. This means the @SelectionFactsContext and @SelectionFactsValue is relevant for only one cell at a time. In addition, you may want to run certain calculations only when all the cells are executed. The system-defined parameter @SelectionFactsIndex and @SelectionFactsCount can help you detect the last iteration of the Rule execution.

• @SelectionFactsCount – INT
• @SelectionFactsIndex – INT

 

The @SelectionFactsCount is the total number of cells selected in the Form.

The @SelectionFactsIndex is ranging from 0 to N-1, based on the current iteration. Here N is equal to @SelectionFactsCount. When @SelectionFactsIndex is 0, it returns NULL.

You can use the following SQL script to detect the last run:

 

IF ISNULL(@SelectionFactsIndex,0) = (ISNULL(@SelectionFactsCount,0) - 1)
/**
  INSERT CALCULATION LOGIC HERE
**/

 

 

Configure in Dashboard

The Run Rules options in the right-click menu is hidden by default. To display it, we need to check the Show Run Rules option in the Form configuration settings  in the Dashboard editor.

 

 

The Hide Default Rules option hides the built-in Adjust and Spread Rules that run on write-enabled cells.

 

 

Configure in Administrator

To show the Rules in Run Rules window, we need to attach the Rules to the Dashboard pages in the ADMINISTRATOR module. Leave the (Hide) and (On Post) options unchecked (i.e., we want to see the Rule in the options and we don’t want to automatically run these Rules on post).

 

 

Note: The selection facts option only works on numeric data cells. The Rule will not run when only row/column cells are selected. If you need to apply it to annotation fields, programmatically populate a 0 into the Value column of the annotation records.