Use Parameters in SQL Rule

Introduction

We can use the Kepion UI to get user input and pass it to a SQL Rule as a parameter. In the screenshot below, Category and Brand are parameters of the Add Trade Spend Rule.

In this article, let's take a look at how we can define parameters for this context, and use them in a SQL Rule.

Example

1. Go to Modeler and locate the target Rule.

Tip: If you don't know how to create a SQL Rule, refer to this article. 

2. Click Edit and go to the Parameter tab.

3. Select Add  to create a new parameter.

4. Configure the parameter as necessary.

If you choose Member Picker for the type, you can select additional options:

• Multi-select allows you to choose multiple members
• (Leaves Only) Selectable restricts you to picking only leaf members
• (All) Non-Selectable prevents you from choosing the All Dimension Member.

Below are the two parameter definitions used in the initial example:

Once created, all Rule parameters can be found in the SQL Server under {Database Name} > Programmability > Stored Procedure > {Rule Name} > Parameters.

Notice the two parameters we created are of the data type tLabelSet. The way to refer to this data type’s value is to use the query:

SELECT TOP 1 Label FROM {Parameter_Name}.

In this example, we could use following queries to get the user’s input:

SET @AccountID = (SELECT TOP 1 MemberID FROM D_Account WHERE MemberLabel = (SELECT TOP 1 Label FROM @Category))

SET @ProductID = (SELECT TOP 1 MemberID FROM D_Product WHERE MemberLabel = (SELECT TOP 1 Label FROM @Brand))

For some examples which utilize SQL Rule parameters, check out the following articles:

• Add Row to Form with SQL Rule
• Manage Dimensions with SQL Rule