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: