Introduction
In Kepion, we can use a SQL Rule to create dynamic Forms which will allow users to add rows as needed. In the image below, we have a Form with a large number of empty rows. Let's hide them and add a Rule that will show a new row only when we need it.
Example
Let's review the row definition. The rows are ordered first by members of the Account Dimension, and then by a Line Dimension to hold the account descriptions.
Now let's hide all the empty rows. When the Hide Empty Rows option is checked, the Form will only show rows that contain data.
This means that the only thing our Rule has to do is add data to a row, thus making it visible again by design.
Let's create the new SQL Rule now and define the following two parameters:
- Account Member to which we want to add rows
- Number of rows we want to add
Tip: If you're not familiar with Rule Parameters, this article should be helpful.
In the Definition tab, we gather the filter selections and the two Rule parameters and pass them to a Stored Procedure.
DECLARE @FiscalYear NVARCHAR(4);
DECLARE @TimeID INT;
DECLARE @EntityID SMALLINT;
DECLARE @ScenarioID SMALLINT;
DECLARE @ProductID SMALLINT;
SET @FiscalYear =
(
SELECT Name FROM @FilterScope
WHERE Dimension = N'Time' AND Hierarchy = N'FiscalYear'
)
SET @TimeID =
(
SELECT TOP 1 MemberId
FROM D_Time
WHERE FiscalYearName = @FiscalYear
AND Granularity = N'Month'
ORDER BY MemberId
)
SET @EntityID =
(
SELECT MemberId FROM @FilterScope
WHERE Dimension = N'Entity' AND Hierarchy = N'Entity Hierarchy'
)
SET @ScenarioID =
(
SELECT MemberId FROM @FilterScope
WHERE Dimension = N'Scenario' AND Hierarchy = N'Scenario'
)
SET @ProductID =
(
SELECT MemberId FROM @FilterScope
WHERE Dimension = N'Product' AND Hierarchy = N'Brand List'
)
EXEC [dbo].[USR_ADD_X_ROW]
@Account = @Account,
@NumberofRows = @NumberofRows,
@TimeID = @TimeID,
@EntityID = @EntityID,
@ProductID = @ProductID,
@ScenarioID = @ScenarioID;
In the Stored Procedure (located in SQL Server Management Studio), we find the row LineIDs and insert a value so they will appear on the Form.
ALTER PROCEDURE [dbo].[USR_ADD_TRADESPEND_ROW]
@Account tLabelSet READONLY
,@TimeID INT
,@EntityID SMALLINT
,@ProductID SMALLINT
,@ScenarioID SMALLINT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @AccountIDs tIDSet;
IF (SELECT TOP 1 Label FROM @Account) = N'All'
INSERT INTO @AccountIDs
SELECT *
FROM fnLEAVES
(
N'Account'
,N'Account'
,N'Post Invoice'
,1
)
ELSE
INSERT INTO @AccountIDs
SELECT MemberId
FROM @Account A
INNER JOIN D_Account DA
ON A.Label = DA.MemberLabel
IF (SELECT COUNT(*) FROM @AccountIDs) > 0
BEGIN
DECLARE @Account_MaxLine TABLE
(
AccountID INT
,LineID SMALLINT
)
--FIND THE LINEITEM IDS THAT NEED TO BE ADDED
INSERT INTO @Account_MaxLine
SELECT
A.ID
,ISNULL(MAX(LineID), 0) + 1
FROM @AccountIDs A
LEFT OUTER JOIN [dbo].[F_Trade Spend_CoreMG_Writeback] F
ON A.ID = F.AccountID
WHERE (TimeID = @TimeID OR TimeID IS NULL)
AND (EntityID = @EntityID OR EntityID IS NULL)
AND (ProductID = @ProductID OR ProductID IS NULL)
AND (ScenarioID = @ScenarioID OR ScenarioID IS NULL)
GROUP BY A.ID
--INSERT VALUE TO LINEITEMS SO THAT THESE ROWS WILL SHOW UP IN THE FORM
INSERT INTO [dbo].[F_Trade Spend_CoreMG_Writeback]
([AccountID]
,[TimeID]
,[EntityID]
,[LineID]
,[MetricID]
,[ProductID]
,[ScenarioID]
,[Value]
,[Status])
SELECT
A.AccountID
,@TimeID
,@EntityID
,A.LineID
,(SELECT MemberId FROM D_Metric WHERE MemberLabel = N'Description')
,@ProductID
,@ScenarioID
,0
,1
FROM D_Line DL
INNER JOIN @Account_MaxLine A
ON DL.MemberId = A.LineID
END
END
Note: The example scripts provided here may need to be altered to fit your individual requirements.
After associating the Rule with our Dashboard in the Administrator tab and adding a new Rule Component, we can now access our new SQL Rule.
Now we have exactly as many rows on our Form as we need.
