Submit a request

Capture Filter Selections with @FilterScope

Avatar
Kepion Support
  • Updated

@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.

 

mceclip0.png

 

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.  

 

Form.PNG

 

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

 

mceclip4.png

 

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.

 

layout.PNG

 

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? 

 

entityfilter.PNG

 

Here's the answer:

 

Dimension Hierarchy MemberId Name
Time FiscalYear 0 All
Scenario Scenario 3 Budget
Entity Entity Hierarchy 0 United States

 

 

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

 

entitymultiselect.PNG

 

 

Dimension Hierarchy MemberId Name
Time FiscalYear 0 All
Scenario Scenario 3 Budget
Entity Entity Hierarchy 2 Argentina
Entity Entity Hierarchy 3 China

 

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.

Related articles