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:
Comments
0 comments
Please sign in to leave a comment.