Using parameters in rules that have NativeSql implementation in Planning Business Modeler

PerformancePoint Planning Business Modeler enables you to pass parameters to rules that have NativeSql implementation. Parameters can help you re-use a rule between different models.

Important:  String parameters are not supported.

In addition, you can use parameters to pass model variables to a native rule by entering the variable as the value of the parameter in the Parameters for Rule dialog box at runtime. For more information on the dialog box, refer to Planning Business Modeler help.

The following list shows the steps that you must follow to create parameters for use in a rule that has NativeSql implementation. For an example of using parameters in a native rule, see the Example section of this topic.

  1. Add the parameters to the rule in Planning Business Modeler.

    For information about how to add a parameter, see Adding a rule parameter.

  2. Insert a stored procedure declaration into the Rule expression pane.

  3. Include the stored procedure in the Calc namespace. Use the preprocessor command sp$procedure to specify the procedure name.

    For more information about these commands, see Preprocessor commands for native code rules.

  4. Declare a SQL type for each parameter that you defined in step 1. Be sure to use the same names that you specified in step 1.

    The following table describes the relationship between Planning Business Modeler types and SQL types.

Planning Server types

SQL types









Member Of Dimension


This value represents the member ID is passed into the stored procedure.

  1. Implement your native SQL rule just as you typically do, inside the stored procedure declaration.

  2. Use the SQL parameters just as you ordinarily use parameters in a stored procedure.

At runtime, PerformancePoint Planning Server compiles the parameters that are supplied by the user, and passes them into the stored procedure. If the user provides a reference to a model variable as a parameter value, the value of the variable is passed into the stored procedure.


The following example shows a rule that takes a parameter, and has NativeSql implementation. This rule deletes all the rows in the fact table that were created by a particular rule.

This example uses the preprocessor commands that Planning Business Modeler supports for rules that have NativeSql implementation. For reference information about these commands, see Preprocessor commands for native code rules.

The parameter is the integer ID of that rule,


Notice that the


command makes sure that the procedure is given the correct name and is added to the Calc namespace.

create proc Calc.[sp$procedure] @RuleId int as
delete from dbo.[tbl$factTable]
where column$ruleId = @RuleId

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!