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

To add parameters to a SQL Rule, first navigate to the respective Rule in the right-hand navigation pane in MODELER.

Click Edit and open the PARAMETER tab. Choose Add  to create a new parameter. In the Rule Parameter configuration window, you can name the parameter, and choose from Float, String, and Member Picker as the Type. In the right-hand pane you can select defaults for the parameter.

If you choose type Member Picker, you can select additional options. As the names suggest, Multi-select allows you to choose multiple members, (Leaves Only) Selectable restricts you to picking only leaf members, and (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 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