In most cases, Filter selection is driven by end users on a Dashboard page. However, you may want to programmatically update the Filter selection. In this article, we will use an example to show how to update Filters through a SQL Rule.
Before You Start
Below are the system objects you will need to update the Dashboard filters:
- [Filter_Planning]: System table used to store the non-empty filter selections. It is updated when a Post or Save action is triggered from the Dashboard App.
- [Filter_Planning_Empty]: System table used to store the context of the filter if nothing is selected. It is updated when a Post or Save action is triggered from the Dashboard App.
- @SubmissionID: Built-in parameter that is ready-to-use in any Kepion SQL rule. Each app instance has a unique SubmissionID. When manually updating the system tables, we want to scope down to only the relevant user and app instance.
Example
In the example below, a Form is used to update project attributes, and I can create a new project as needed. The Form context is driven by the Project Filter. I want to set the Project Filter to the project a users has just added. Without updating the system tables manually, the Project filter will remain as the current one.
To manually update the Filter selections, follow these steps:
1. In the Modeler, navigate to the SQL Rule that will trigger Filter updates.
2. On the Definition tab, ensure @SubmissionID is passed in as a parameter to the stored procedure.
3. Go to the Advanced tab and select Update form filters. This will force the filters to be refreshed once the rule is executed.
4. Add the following code snippet to the Add New Project rule, i.e. the [USR_ADD_PROJECT] stored procedure.
UPDATE F
SET F.[Selected] = N'[Project].[Project].&[' + CONVERT(NVARCHAR(MAX), @ProjectID) + N']'
FROM [Filter_Planning] F
INNER JOIN [Submissions] S ON F.[SubmissionID] = S.[SubmissionID]
WHERE S.[SubmissionID] = @SubmissionID
AND F.[Selected] LIKE N'[[]Project].[[]Project]%'
DELETE [Filter_Planning_Empty]
WHERE [SubmissionID] = @SubmissionID
AND [Dimension] = N'Project'
AND [Hierarchy] = N'Project'
Note: Replace Project with the relevant Dimension and Hierarchy in your context.
Here's the complete stored procedure for your reference:
ALTER PROCEDURE [dbo].[USR_ADD_PROJECT]
@NewProjectName NVARCHAR(200)
,@PortfolioLabel NVARCHAR(200)
,@SubmissionID BIGINT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @PortfolioLabel IS NULL
PRINT N'<ERROR>Please select a portfolio.'
ELSE IF @NewProjectName IN (SELECT MemberLabel FROM D_Project)
PRINT N'<ERROR>This project already exists. Please be aware that projects must have an unique name.'
ELSE
BEGIN
-- Add new project to the Project dimension
INSERT INTO [dbo].[D_Project]
([MemberLabel]
,[MemberName]
,[Input]
,[Annotate]
,[Portfolio])
VALUES
(@NewProjectName
,@NewProjectName
,1
,0
,@PortfolioLabel)
DECLARE @ProjectID SMALLINT = (SELECT [MemberId] FROM [D_Project] WHERE [MemberLabel] = @NewProjectName);
INSERT INTO H_Project_Project
VALUES (@ProjectID, @ProjectID)
-- Update the Project filter
UPDATE F
SET F.[Selected] = N'[Project].[Project].&[' + CONVERT(NVARCHAR(MAX), @ProjectID) + N']'
FROM [Filter_Planning] F
INNER JOIN [Submissions] S ON F.[SubmissionID] = S.[SubmissionID]
WHERE S.[SubmissionID] = @SubmissionID
AND F.[Selected] LIKE N'[[]Project].[[]Project]%'
DELETE [Filter_Planning_Empty]
WHERE [SubmissionID] = @SubmissionID
AND [Dimension] = N'Project'
AND [Hierarchy] = N'Project'
PRINT N'<INFO>Project has been added successfully.'
END
END
5. Go to the App and click the Add New Project to create a new project
6. Configure as desired and click Add Project.
Once the rule is triggered and completed, New Project (instead of Health System Solution) is selected on the Project Filter.