Run Rules on Selected Cells
By Joyce Zhou - Apr 06, 2017
Users can now run complex business rules on any selected data cells from a form. A popular scenario that uses this functionality is allocating data at different levels of aggregation. Here, in our sample application we illustrate the use for this case. In the application, we provide two methods to allocate data:
- Set a target at any level of the hierarchy, and the rule will allocate the target data to the leaves proportionally.
- 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 cell in the form, right click, and select Run Rules.
In the pop-out window, users can select a rule to execute.
Here, let’s select Adjust by Percentage, enter 10%, and click execute.
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 member has been increased by 10%, which results in a 10% increase of the parent member.
Configuration in Model Rule
To use this functionality, the Include selection facts option of the rule has to be checked.
When this options 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 is 2070.
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.'
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 calculation 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 on 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 to detect the last run.
IF ISNULL(@SelectionFactsIndex,0) = (ISNULL(@SelectionFactsCount,0) - 1)
INSERT CALCULATION LOGIC HERE
Configuration in Dashboard
The Run Rules option in right click menu is hidden by default. To display it, we need to check the Show Run Rules option in form configuration in dashboard.
The Hide Default Rules option hides the built-in Adjust and Spread rules that runs on write enabled cells.
Configuration in Administrator
To show the rules in Run Rules window, we need to attach the rules to dashboard pages in the ADMINISTRATOR module. Leave the Hide and On Post unchecked (i.e. we want to see the rule in the options and we don’t want to automatically run these rule on post).
Having this option available provides a powerful new way to create user friendly rules. However, please be aware that selection facts option only works on data cells. The rule will not get run when only row/column cells are selected.
Build Enterprise BI & CPM solutions within minutes with Kepion Modeler.