Capture Filter Selections with @FilterScope

@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, SQL rules have to be attached to a Form to be executed. Dashboard Rules can be attached to a Form in the Dashboard Editor, 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 Editor, we can quickly see where these values originate.

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

Real Dimension Member

When a Member is a real Dimension Member, that means one of two things:

• The Filter uses a Member List or a PC hierarchy, and the selected Member is not All
• The Filter uses an inputable leveled hierarchy (the leaf level is MemberId), and the selected Member is a leaf Member

In either case, the MemberId and Name column of @FilterScope will be the MemberId and MemberLabel of the selected member.

Not a Real Dimension Member

When a Member is not a real Dimension Member, this means one of three things:

• The Filter uses a Member List or a PC hierarchy, and the selected Member is All
• The Filter uses an inputable leveled hierarchy (the leaf level is MemberId), and the selected Member is a Parent Member
• The Filter users a non-inputable leveled hierarchy or an attribute hierarchy

In these cases, the MemberId will be 0, whereas Name will be the Member's display name, which is exactly the same as what's displayed in the Filter.

Test

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 our training video series.