Metric filters

Overview

Most metrics require constraints of some sort (for example, by District Code in multi-district environments, School Year, etc.) on the source data.

These data constraints are divided into two subclasses based on whether they are controlled by users:

1. Dashboard filters
2. Built-In Criteria

Dashboard filters must be defined separately from the metric and metric base, then linked to a dashboard so they can be selected by the user.

For the user's selections to be applied, the metric or metric base must be modified to use the Dashboard Filter.

Built-in criteria

Built-in criteria are part of a metric's definition and cannot be changed by users during their analysis sessions. Whereas most objects have corresponding tables, Built-in criteria exist only as expressions in the SQL clauses included in several Metric Definition objects. These filters are completely hidden from the user, and, as a result, their presence should be noted in the title or comments.

Metric filters are entries in the WHERE clause of the driving query for a metric. 

For example, the metric base Grades could be constrained by a dashboard filter to display grades from only one user-selected school. Or, several different metrics could be created, using the same metric base Grades, and each could then be constrained by built-in criteria to create a Math Grades Metric, a Science Grades Metric, and so on.

There are multiple ways to update the WHERE clause to add a metric filter:

• Directly on the metric's WHERE clause property in the Query Designer.
• On the metric base attached to the metric.
• Via metric base columns that are attached to the metric in the Query Designer; these column definitions also have a WHERE clause and when the column is attached in the "Filter Columns" area of the Query Designer, that WHERE clause is added to the driving query.

To apply a dashboard filter to several metrics:

1. Create a separate Metric Base Column for the metric.
2. Put the @@PROMPT statement in the WHERE Clause property.
3. Put any value in the Select Clause property.
4. Add the Base Column to the metric by dragging this new Base Column to the Filter Columns area of the Chart Data section of the Query Designer.

This creates a reusable filtering control that can be used by several metrics and is easy to maintain: When you update this Base Column object, it updates it for all metrics that have a reference to it.

Metric query statement

Dashboard filters can be linked to metrics at both the Metric and Metric Base levels.

For example, the following change could be made to the WHERE CLAUSE in either the Metric or the Metric Base:

Existing: WHERE_CLAUSE: (…)

Revised: WHERE_CLAUSE: (…) AND @@PROMPT(School Code, "DTBL_SCHOOLS.school_code", IN, IN, "1=1", int)

The above revision makes the Metric filter on School Code Dashboard Filter.

If the Filtername is not the name of a currently available filter, @@PROMPT() returns the value specified by the DefaultValue parameter. Since the DefaultValue is typically "1=1", the statement resolves to a true expression in the SQL statement. As a result, all misspelled (but otherwise valid) values for Filtername are disregarded.

@@VARIABLE expressions

Built-In Criteria are Boolean expressions that refer only to the database and to Scoped Variables and are contained in the WHERE_CLAUSE field of one of the Metric Objects. The values of Scoped Variables are accessed using the @@VARIABLE syntax.

Built-In Criteria can be included in the Metric Base (and by extension to all the Metrics based on it), in a Metric Base Column, or in the Metric on a specific dashboard page.

For example, an Average Test Score Metric Base could have a filter at the Metric level to only show OSAT composite scores. This same Metric Base could be filtered by SPED students, LEP/ESL students, and FRE students in three separate Metrics on the same page – or by test subjects Math, Reading, and Science in separate metrics on another dashboard page.

Built-In criteria are similar to dashboard filters in that both must be included in the WHERE clause of a Met Object to be effective. Aside from that, the two filter types have the following differences:

• Components – dashboard filters usually resolve to a Boolean expression and have a user interface (selection control) with which to make selections. Built-In Criteria usually resolve to a Boolean expression and cannot be modified by the user directly.
• @@PROMPT expressions – dashboard filters can have a Boolean expression defined in their @@PROMPT statement; Built-In Criteria cannot.
• User responsiveness – Users can influence the behavior of dashboard filters but cannot change the behavior of Built-In Criteria.

For example, the following expression can be used as the WHERE clause of a Metric Base, a Metric Base Column, or a Metric.

FTBL_ATTENDANCE_STUSUMMARY.LOCAL_SCHOOL_YEAR = '@@Variable(CYTD)'
AND DTBL_STUDENTS.STUDENT_ACTIVITY_INDICATOR = 'Active'
AND @@PROMPT(SchoolCode,"DTBL_SCHOOLS.SCHOOL_CODE",=,IN,"1=1",INT)