Documentation Vision Library+ Documentation

SQL Filter+

SQL Filter+ lists the values of a column in an AF table, linked to a SQL database or static, and lets operators click values to narrow other symbols on the display.

Overview

SQL Filter+ lists for Scope, Work Kind, numeric ranges and a two-level tree over one AF table

When to use it

Use this symbol when you need to:

  • Let operators narrow the rows of an AF table by the values of a column, such as Scope.
  • Combine several filters on the same table.
  • Offer numeric bands, such as Under 1,000 and Over 100,000, instead of every distinct number.
  • Drive event or asset symbols from a table value through a linked channel.
  • Open one display from many links, each with the filter already set.

Choose Asset Filter+ or Event Filter+ when the values come from AF assets or event frames. Choose SQL Table+ to show the rows themselves; a SQL Table+ on the same table follows this filter.

Before you begin

  • Create the AF table in the AF database, linked to a SQL database or static. The query lives in AF, not in this symbol.
  • Give display viewers read access to the AF table.
  • Know the attribute that places the symbol: PI Vision creates this symbol from a dragged attribute or PI tag.

Add and set up the symbol

  1. Select SQL Filter+ in the PI Vision symbol gallery.
  2. Drag an attribute from an element in the AF database that holds the table onto the display.
  3. Right-click the symbol and select Configure SQL Filter....
  4. Under Table, set AF Table to your table, such as PI Nexus. For a table in another database, set Source to Custom and choose AF Server and AF Database first.
  5. Under Dimension, open Arguments, select + and set Value to a column, such as Scope.
Table section with the AF table picked and the Arguments group with Scope as the value
  1. Close the pane and click a value in the list.

The list shows one entry per value of the column, and the clicked value is highlighted.

SQL Filter+ with PiVision selected in the Scope list

Common tasks

Combine two filters on one table

Use this when operators narrow a table by more than one column. Filters on the same table work together: a symbol on that table shows the rows that match every filter.

  1. Add a second SQL Filter+ on the same table and set Value to another column, such as WorkKind.
  2. To make its list follow the first filter, open General > Filtering and set Ignore Master to OFF.
  3. Set Filter Indicator to FULL to show the row count at all times.
  4. Click a value in the first filter.

The second list offers only the values left, and its indicator shows the remaining rows, such as 239 of 2752 rows.

A Scope filter with PiVision selected and a Work Kind list narrowed to the remaining values

A SQL Table+ on the same table follows the filters too and shows only the matching rows. A filter on another AF table is not affected.

Filter event symbols through a channel

Use this when the symbols to filter use other data, such as event frames, and a table column holds the same values as one of their properties.

  1. Open General > Filtering on SQL Filter+ and set Output Mode to LINKED, or to BOTH if symbols on the same table should follow too.
  2. Select Add Output, enter a Channel name, such as Severity, and set Source to the column, here Type.
Filtering group with Output Mode Linked, the Severity channel and Type as its source
  1. On the other symbol, open Filtering, select Add Input, enter the same Channel and set Target to the matching property, here Severity.
  2. Click a value in SQL Filter+.

The other symbol shows only its rows with that value, and its indicator shows the count.

SQL Filter+ with Warning selected next to an Event Analytics Chart+ reduced to warning events

The values must match exactly, including their type. A channel carries values, so it also connects two different AF tables.

Offer numeric bands instead of values

Use this when a numeric column has too many distinct values to list.

  1. Open Dimension > Arguments. Keep one argument and set Value to a numeric column, such as ProcessedCount.
  2. Set Mode to RANGES.
  3. Under Ranges, select + to add a range.
  4. Enter a Label, then a Minimum, a Maximum or both. A range in red is invalid.
Dimension section with the ProcessedCount value, ranges mode and three ranges

The list shows the ranges, and a clicked range narrows symbols on the same table. Ranges publish no linked channels.

A Processed Objects list with one range selected and a Work Kind list that follows it

Show a two-level tree

Use this when one column groups another, such as a scope and what triggered it.

  1. Open Dimension > Arguments and add a second column with +.
  2. Open General > Layout and set View to TREE.
  3. Set Start Expanded to ON if the tree should open with its branches visible.

Clicking a parent filters on the parent value. Clicking a child filters on both.

A tree of scopes with the triggers of each scope below, one trigger selected

Open a display with a value already selected

Use this when a display should open filtered, either always or from a link.

  1. Open General > Selection.
  2. To always start with a value, set Default selection to Specific values and enter the value, or choose the last item.
  3. To select from a link, enter a Link parameter name, such as Scope.
Selection group with Default selection set to Specific values and the link parameter Scope

A link ending in ?vlp.scope=PiVision opens the display with PiVision selected, and a link value takes priority over the default.

Read a table by name from the asset's AF database

Use this when the same table name exists in several AF databases and the display switches between assets.

  1. Make an AF attribute the symbol's first data source.
  2. Under Table, set Source to its first option and pick the table.
Table section with Source set to the first data source and the Limits table picked

When the asset changes to another AF database, the symbol reads the table of the same name there.

Use the symbol

A Work Kind filter with two values selected, numbered callouts on the header, search, All, a value and the indicator
CalloutPartWhat it does
(1)HeaderShows your text or the selected values. On a collapsible filter, click it to open or close the list.
(2)SearchShortens the list to the entries that contain the text.
(3)AllSelects or clears every value. Multiple selection only.
(4)ValueClick to select. Click again to clear.
(5)IndicatorShows that a filter is active and how many rows other filters left. Hover over it to clear this symbol's own selection.

Selecting a value changes other symbols on the display only. It does not write to the AF table and does not change the saved display. Leaving the display clears the selection.

The list reloads with the table at the Auto Reload interval, and a selected value stays selected. While the first load runs, the symbol shows a spinner. A table without rows shows an empty list and writes an Information entry to the PI Vision log. In edit mode an empty symbol says what is still missing.

Configure the symbol

Right-click the symbol and select Configure SQL Filter....

Table

SettingWhat it does
SourceThe first option reads the table by name from the first data source's AF database. Custom uses the pickers below. Available when the first data source is an AF attribute.
AF Server, AF DatabaseWhere the table is. Available with Custom, and always for a symbol without an AF attribute.
AF TableThe table to read. Changing it clears the selection.

Query Parameters

SettingWhat it does
Parameter NameThe AF table's parameter, starting with @.
Value SourceDisplay Start Time, Display End Time, Custom or a data source. Current Value shows what is sent.
ValueThe text sent. Available with Custom.
Data sourceThe data source whose current value is sent. Available when Value Source is a data source.

Dimension

SettingWhat it does
ArgumentsThe columns the list is built from. Add and remove them here. Two or more columns join their values with a dash, or form a tree.
ValueThe column of the selected argument. A column the table does not have shows as not in this table and offers no entries.
Group IntervalGroups a date and time column, for example by month. Available for a DateTime column.
ModeVALUES lists the distinct values. RANGES lists your ranges. Available with one numeric column. See Offer numeric bands instead of values.
RangesLabel, Minimum and Maximum of each band. Available with RANGES.

General

SettingWhat it does
Auto Reload, Auto Reload IntervalReads the table again at this interval. The default is 1 minute, and 5 seconds is the shortest.
TypeSTATIC always shows the list. COLLAPSIBLE folds it under the header.
Collapse On Select, Start ExpandedCloses the list on a click and opens it on load. Available with COLLAPSIBLE.
View, Start ExpandedLIST or TREE. Start Expanded opens the branches. See Show a two-level tree.
Mode (Selection)SINGLE or MULTIPLE. Multiple adds checkboxes and All.
Allow EmptyON lets a click clear the only selected value. OFF keeps one value selected and starts with the first. Available with SINGLE.
IconShows radio buttons or checkboxes.
Default selection, Link parameter nameSee Open a display with a value already selected.
Theme, Surface, ContainerThe symbol's look. Background and Text appear with the Custom theme.
Padding, Font, Font SizeRow spacing and text.
Header, TextShows the header, with your text or the selected values.
Search, PlaceholderShows the search box and its hint.
Filter IndicatorCOMPACT, FULL or HIDDEN.
Ignore Master, Linked InputsWhether other filters on this table, and linked channels, narrow the list.
Output Mode, Linked OutputsWhat a click publishes: MASTER to symbols on this table, LINKED to channels, or BOTH. See Filter event symbols through a channel.

Data sources

SettingWhat it does
Data source listThe data sources of the symbol, with their current values. They feed the table source and the parameters. Delete the last one to make the symbol read on its own timer.

Troubleshooting and limitations

SymptomCauseWhat to do
The list is emptyNo table or column is picked, or the table has no rows.Open the pane. In edit mode the symbol says what is missing. Check the PI Vision log.
The list is empty after a table changeThe saved column is not in the new table.Pick a column of the new table under Value.
A column is missing from ValueIts name contains a vertical bar or is reserved for the symbol's own use.A Warning in the PI Vision log names it. Rename the column in a view or in the AF table.
Other symbols do not narrowThey use another AF table.Use a linked channel.
A channel selects nothingThe values or their type differ.Compare the column with the target property.
The log shows Loading Data FailedThe AF table, its query or a parameter failed.Read the message, and test the table in PI System Explorer.

Limitation: The symbol reads every row of the AF table in the browser. PI Web API offers no paging for AF tables, so a very large table takes longer to load and writes an Information entry to the log.

Limitation: PI Vision places this symbol from a dragged attribute or PI tag. Delete that data source in the pane if the symbol does not need it.