Pass Cell Changes as Parameter in SQL Rule

Author: Joyce Zhou

Introduction

In Kepion, cells that have been changed but not posted yet will be marked by a blue triangle on the top left of the cell, as shown below.

At post, these cells are stored in the @Changelist parameter, which can then be used in any on-post SQL Rules.

Example

The @Changelist data type is dynamically defined by the Model that the Form belongs to. The data type follows the naming convention:

dbo.t_Fv2_{Model Name}_CoreMG_Writeback

You can see that it's very similar to the Writeback table structure in the same Model.

Whenever you want to use cells that have been changed, you can pass the @Changelist as a parameter into the SQL stored procedure.

In the stored procedure, you will define a parameter with the right data type.

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[USR_CALC_TRADESPEND]
    @Changelist [dbo].[t_Fv2_Revenue-Planning_CoreMG_Writeback]
AS
BEGIN
…

Then in the stored procedure, instead of reading data from Writeback table, which includes all the Writeback data in that Model, you just need to read data from @Changelist parameter.

SELECT *
FROM @Changelist CL

This will help reduce the calculation scope and thus optimize SQL Rule performance when used correctly.