Capture Filter Selections with @FilterScope

Introduction

@FilterScope is a pre-defined, read-only, table-value parameter in the Kepion SQL rule set.

@FilterScope dbo.tFilters READONLY

The tFilters table contains four columns: Dimension, Hierarchy, MemberId, and Name.

Let's use some examples to understand what @FilterScope is and how to use it in Kepion.

Example

In Kepion, a SQL rules have to be attached to a Form to be executed. Dashboard Rules can be attached to a Form in the Dashboard Designer, whereas On-Post Rules should be attached in the Administrator Module.

When a rule is executed, @FilterScope will contain all the filter selections within that Form. Use the Form below as an example. This form contains three filters with a single selection.  

If a rule associated with this Form is called, @FilterScope will contain three records.

The first two columns, Dimension and Hierarchy, are defined in the Form structure. If we take a look at the LAYOUT tab in the Form Designer, we can quickly see where these values are from.

The third and fourth columns of @FilterScope reflect the selected member in each filter.

If the selected Member is a real Dimension Member, which means either:

1. The filter uses a Member List or a PC hierarchy, and the selected Member is not All, or
2. The filter uses an inputable leveled hierarchy (the leaf level is MemberId), and the selected Member is a leaf Member

Then the MemberId and Name column of @FilterScope will be the MemberId and MemberLabel of the selected member.

If the selected member is not a real Dimension Member, which means:

1. The filter uses a member list or a PC hierarchy, and the selected Member is All
2. The filter uses an inputable leveled hierarchy (the leaf level is MemberId), and the selected Member is a Parent Member
3. The filter users a non-inputable leveled hierarchy or an attribute hierarchy

Then the MemberId will be 0, whereas Name will be the display name of the member, which is exactly the same as what you see in the filter on the page.

Here's a quick test. Take a look at this screenshot. Can you figure out what the values in @FilterScope would be? 

Here's the answer:

If the filter is multi-selectable, @FilterScope will contain multiple rows for that filter.

In the SQL rule definition, we can select filter selections from @FilterScope, and pass them to stored procedures for further calculations.

DECLARE @ScenarioID TINYINT = (SELECT TOP 1 MemberID FROM @FilterScope WHERE Dimension = N'Scenario' AND Hierarchy = N'Scenario');
    ,@EntityID SMALLINT = (SELECT TOP 1 MemberID FROM @FilterScope WHERE Dimension = N'Entity' AND Hierarchy = N'Entity Hierarchy');
    ,@YearID INT = (SELECT TOP 1 MemberID FROM D_Time WHERE Granularity = 'Year'
                   AND FiscalYearName = (SELECT TOP 1 [Name] FROM @FilterScope WHERE Dimension = N'Time' AND Hierarchy = N'FiscalYear'));

EXEC [spR_Calc_Expense]
    @YearID = @YearID,
    @EntityID = @EntityID,
    @ScenarioID = @ScenarioID;

Tip: For more information on passing parameters into SQL Rules, watch Session 12 of the Kepion Training Videos.