Add a filter

Overview

To set the filter control, you can clone, copy, or create a filter.

A cloned filter is a filter control object that links back to the parent version for all its settings. Filters normally exist in the Shared Objects folder and are cloned from that location. The cloned version of the filter is then pasted into the intended dashboards.
A copied filter makes a copy of the filter object. Each copied filter is a new version of the filter and will end up existing multiple times. It has no reference to the filter it was copied from.

It is preferred in most scenarios to clone a filter rather than copy it. A cloned filter allows you to maintain the filter in one location; if a change is made to the original filter object in Shared Objects or the cloned version, it will update everywhere. A copied filter does not allow for this function; a copied filter is modified only when that version is changed.

The only scenario that requires a new filter or a copy of an existing filter is if the functionality of that filter is completely different, meaning the query (and joins) are so different that a variable cannot be used. The values returned by the filter can also be different, which would require a new or copied filter.

To create a new dashboard filter:

Select a folder or dashboard in the Navigation tree.
Choose New, then select Metric Filter.
Fill out the +filter properties.
Click Save.

To copy or clone an existing filter:

Select the filter in the Navigation tree.
Click More in the Navigation tools, then choose Copy or Clone Link.
Update the link name and properties.
Click Save.

Cloning children filters

If a cloned filter is placed on a dashboard and if the Display with parent property is checked, it will automatically pull its parent's filters onto the dashboard as well. The parent filters will not appear in the filters folder in the navigation. It is best to clone the parent filters onto the dashboard. If the parent filters are not cloned, they will not use any of the variables.

Duplicate dashboard filters

If dashboard filters are incorrectly copied or cloned, you may see duplicate filters. For example, you may see two school filters if the grade filter was cloned, but the school filter was copied. The grade filter looks up its parent and checks to see if it exists on the dashboard. Since the school filter was copied and not cloned, the grade filter determines it is not present and adds it. When this happens, the user sees the copied version of the school filter (which will not cascade to grade) and the cloned school filter that came from grade.

To resolve this issue, delete the copied school filter, go to shared objects, clone the correct school filter, and paste the cloned version on the dashboard.

Preselect filter values

To preselect a school in a filter, for instance, the filter object needs to be modified to have a school variable. To edit the school filter, go to shared objects filters and look for the school filter.

Click the Edit (pencil) button to edit the school filter. In the filter property screen, an option is available for "Default Selected Value". Set this to a school_key value.

The variable will need to be created. Typically, this query will select the user's primary assigned school. This query can be done by querying the row-level security tables for school groups where the username equals the logged-in dashboard user.

Apply the filter to the metric

Creating dashboard filters using the filter control allows users to select values on which to filter. However, for these filters to be applied to a metric, that metric's code must be revised to refer to the dashboard filter.

The currently selected value of the dashboard filter can be incorporated into a SQL fragment by using the @@PROMPT function:

@@PROMPT(filtername,"Left Expression",Operator,AllValuesAction,"Default Value",DBColumnType)

Any reference to the "Right Expression" refers to the items selected by the user from the corresponding dashboard filter control. This will either be a single value or a list of values, depending on the control type or the operator that is applied.

When the @@PROMPT() function is resolved, it is normally substituted with an expression of the following format:

(Left Expression) (Operator) (Right Expression)

This expression typically represents a Boolean statement that evaluates to True or False. However, it can also be an assignment statement, especially when used in Report objects.

All non-quoted parameter values are case insensitive.

@@PROMPT parameters

Parameter	Description	Values	Notes
Filtername	Object name of the filter from as it appears in the navigation tree.	Any valid name of a dashboard filter control	Allows spaces in the name of the filter control. However, it does not allow commas (,).
Left Expression	The expression that appears to the left of the operator	Any string value	The value must be double-quoted.
Operator	The operation performed between Left Expression and the Right Expression (selected values from the dashboard filter).	<>, !=, <, <=, >=, >, =, IN, NOT IN, LIKE, Beginswith, Endswith, Contains	Only IN and NOT IN allow multiple values to be processed from those Filter controls that are defined to allow multiple selections. All other operator types use the first selected value when processing Filter controls that allow multiple selections. Beginswith, Endswith, and Contains are new operators that will generate a corresponding LIKE expression with the wild cards in the appropriate locations around the selected value for the dashboard filter control.
All Values Action	Defines what should be used in place of the prompt expression when the special "all" value is selected.	None, IN, NOT IN, Default, PromptDefault	IN creates a SQL "IN" statement with all the non-empty values in the filter list. NOT IN creates an SQL "NOT IN" statement with all the non-empty values in the filter list. Default (when the "all" item is selected then this uses the value defined for the "all" item in the interface). PromptDefault uses the DefaultValue value.
Default Value	Used when no value is selected from the filter.	Any string value.	The value must be double-quoted. In most cases, this is not used unless the AllValuesAction is set to PromptDefault, since empty values are not allowed as a valid selection by the Filtering controls.
DBColumnType	Specifies the data type of the filter values so the proper SQL can be generated.	int, string, datetime, bit, decimal, exact	At this time, no conversion or validation is performed for the different data types, there are only single quotes (e.g., 'some values') put around the value(s) for string and datetime types.

Operator attributes

Operator attributes allow you to define additional actions or override actions on the @@PROMPT statement that affect the final statement that is created. Operator attributes are defined by adding a colon after the operator, defined in the 3rd parameter of the @@PROMPT statement, and then specifying the attribute. Multiple attributes can be defined by separating each with a colon. Only the attributes that pertain to a specific operator will be applied.

Usage: @@PROMPT(filtername,"LeftExpression",Operator:Attribute1:Attribute2,AllValuesAction,"Default Value",DBColumnType)

Attribute	Description
ToUpper	Converts the value of each selected value in the dashboard filter control to upper case when the resulting statement is generated. This allows for easier SQL matching when using a case-sensitive database, such as Oracle. Then you only need to use a case-conversion statement around the value in the Left expression.
ToLower	Converts the value of each selected value in the dashboard filter control to lower case when the resulting statement is generated.
Name	Uses the displayed value (text) of the selected items, instead of the actual value that the filter control has defined, if the text and value fields are different. This is useful when you want to display the listed values to the user, and do not want to show the hidden values that are associated with the selected items, since the values may not be as meaningful when displayed to the user.
Direct	Generates a resulting statement that does not contain the operator between the left and right expressions. This is useful when you only want to display the selected values, and do not need a Boolean expression. To display just the values, leave the value between the double quotes in the 2nd parameter empty.
Swap	Swaps the left and right expressions when generating the resulting statement. Note that if an IN or NOT IN operator is used, then only the first selected value will be used when placing it on the left side of the statement since the left expression can only have a single value. This is useful in cases where the left expression contains a list of values (contained in a comma-delimited list with parenthesis around it "Standard SQL notation for an IN list") and then you would like to see if the value selected by the user is in this "static" list. Otherwise, it can be used when the value the user selects is actually the field or parameter name (e.g., for reports) that you want to assign a static value (defined in the left expression) into.
Delimiter attributes	Semicolon, comma, space, tab, newline. The above attributes all pertain to the delimiter that will be used when the operator allows multiple values and the user has multiple values selected, or the All option is selected and the IN operator is specified for that case. The default delimiter, when not defined, is a comma.