Submit a request

Use Parameters in SQL Rule

Avatar
Kepion Support
  • Updated

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.

 

addtradespend.PNG

 

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.

 

addtradespendrule.png

 

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

 

2. Click Edit pencil.pngand go to the Parameter tab.

 

editparameter.png

 

3. Select Add  to create a new parameter.

4. Configure the parameter as necessary.

 

configureparameter.PNG

 

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:

 

endresult.PNG

 

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

 

4-parameters_in_ssms.png

 

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:

 

Related articles