Kepion's Modeler allows you to set up SQL Rules for end users . In this article, we will explain how to create a SQL Rule and associate it with a Form in a Dashboard.
Create New SQL Rule
1. Go to the Modeler and select your target Application.
2. Under the target Model, go to Rules and select Add.
3. Name the Rule and choose SQL as the Rule Type. In SQL Rules, we can filter records by Filter selection.
4. Go to your target Application in the Adminstrator.
5. Expand Dashboard App and select your target App.
8. Go to Rule and click Add.
9. Select the Rule(s) you want to add and click OK.
10. Locate your Rule(s) and select its On Post checkbox.
Tip: Use the Name and Form fields to quickly search for specific Rules or Forms.
11. Open SQL Server Management Studio (SSMS), connect to your SQL Server instance, and locate the stored procedure of your Rule.
We can see a set of stored procedures named as:
dbo.sp_R_{Model Name}_{Rule Name}
12. Right-click the procedure and select Modify.
The following statements are generated by default when you create a new Rule:
Test Default Parameters
It's often useful to test the Rule before we add any logic, to make sure everything's functioning correctly.
1. Select New Query.
2. Copy and paste some of the test strings below:
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
3. Select Execute.
4. Return to Kepion and go to the Dashboard Page associated with the new Rule.
5. Click Post, or right-click the Form and select the Rule to run it.
6. Add the following statement to the test script, highlight it, and select Execute.
SELECT * FROM TestFilterScope
Using FilterScope
Using the @FilterScope parameter allows us to to capture the Form's Filter selections.
Below is an example of a Rule definition we might write:
Note: We generally discourage users from writing 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 our 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')
);
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-created input parameters. 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.
1. Go to the Administrator module and select the Application.
2. Select Expense Planning.
3. Go to Rule and select Add.
4. Select Calculate Travel Expense and click OK.
5. Select 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, the Rule will run automatically whenever the user clicks Post on the Page.
We have entered information into line 6 below:
Then we click Post.
You can see values calculated by the Rule:
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.