Configure SQL Rule

Introduction

In Kepion we can setup SQL Rules to be executed by the end-user. We can create these Rules in the MODELER tab. In this article, we will go through the steps of creating a SQL Rule and associating it with a Form in a Dashboard.

  

 

Create New SQL Rule

Step 1. To create a new Rule, first navigate to the MODELER. Under the Model that you want to create the Rule in, select Add and choose SQL as the Rule Type. In SQL Rules, we can filter records by Filter selection.

 

 

Step 2. Associate the Rule to a Dashboard Page in the ADMINISTRATOR module. Check the On Post box.

 

 

Step 3. In the SQL Server, locate the stored procedure of the Rule we created in the first step.

We can see a set of stored procedures named as:

 

dbo.sp_R_{Model Name}_{Rule Name} 

 

 

Step 4. Select the procedure. Right-click and choose Modify. The following statements are generated by default when you create a new Rule.

 

 

Step 5. It's often helpful to test the Rule first, before we add any logic, to make sure everything's functioning correctly. Below are a number of test strings that you can execute.

 

 

IF EXISTS(SELECT name FROM sys.tables WHERE name = 'TestChangelist')
BEGIN DROP TABLE TestChangelist END

IF EXISTS(SELECT name FROM sys.tables WHERE name = 'TestScope')
BEGIN DROP TABLE TestScope END

IF EXISTS(SELECT name FROM sys.tables WHERE name = 'TestSelectionScope')
BEGIN DROP TABLE TestSelectionScope END

IF EXISTS(SELECT name FROM sys.tables WHERE name = 'TestFilterScope')
BEGIN DROP TABLE TestFilterScope END 

SELECT * INTO TestChangeList FROM @Changelist
SELECT * INTO TestScope FROM @Scope
SELECT * INTO TestSelectionScope FROM @SelectionScope
SELECT * INTO TestFilterScope FROM @FilterScope

 

Step 6. Go to the Dashboard Page we previously associated the new Rule with. Either click Post, or right-click on the Form to run the Rule.

 

 

Step 7. In the SQL Server, use the following script to see the Filter values that have been captured by the rule. Add the statement to the Rule definition, and then highlight it and run.

 

SELECT * FROM TestFilterScope

 

 

Tip: For more details regarding to the System Parameters that can be captured by a Rule, refer to pg. 44 of the Kepion Modeler Guide.

 

Step 8. Now we can use the Filter values in Rules. Below is an example of a Rule definition we might write.

 

 

Note: It's not usually considered best practice to write queries in Rules directly in the Kepion UI. Generally, we create a stored procedure to store the queries, and then call this procedure in the Rule. Check out the Kepion Sample Applications for examples of this.

 

To grab the MemberID of the Dimension Member used in the Filter Selections, as in the example script above, use the following query:

 

DECLARE @<VARIABLE> SMALLINT;

SET @<VARIABLE> = 
(
    SELECT TOP 1 [MemberId] FROM @FilterScope 
    WHERE [Dimension] = N'<DIMENSION>' and [Hierarchy] = N'<HIERARCHY>'
);

 

To get the TimeID selection from a Fiscal Year attribute filter, you can use a query like this:

 

DECLARE @<VARIABLE> INT;

SET @<VARIABLE> = 
(
    SELECT [MemberId] FROM [D_Time] 
    WHERE [Granularity] = N'Year' 
    AND [MemberLabel] = (SELECT TOP 1 Name FROM @FilterScope WHERE [Dimension] = N'Time' and [Hierarchy] = N'FiscalYear')
);

 

Step 9. Now that we've defined the Rule, we can go to our APP and start using it.

 

Tip: Kepion also allows SQL Rules to use user input parameters. Please refer to Use Parameters in SQL Rule to see how.

 

 

Associate Rule with Form

We can associate existing Rules to Forms in the same model. For this example, let's associate the Calculate Travel Expense SQL Rule with the Travel Expense Form.

 

 

Step 1. In the ADMINISTRATOR Module, let's click on the Expense Planning Dashboard APP. This Dashboard uses the Travel Expense Form we want to work with. Then navigate to the RULE tab.

 

 

Step 2. Select the TRAVEL EXPENSE Dashboard Page and click Add. In the pop-out window you can see a hierarchy of all the Forms associated with that Dashboard Page, and all the Rules we can associate with that Form. Let's select the Calculate Travel Expense Rule and click OK.

 

 

Step 3. Check the On Post box if you want the Rule to run automatically when users Post the Plan. The Hide box will remove it from the list of available Rules that users can run manually.

 

 

Now that the Calculate Travel Expense Rule is associated with the TRAVEL EXPENSE Dashboard Page, it will run automatically whenever the user clicks Post.

 

 

After entering in the relevant travel expense information and hitting Post, the Rule will update the values in the Form below.

 

 

If we didn't select the On Post option, we could have also accessed our Rule manually by adding a Dashboard Rule Component, or by un-checking the Hide option and right-clicking on the Form.