SQL Table+

Overview

When to use it

Use this symbol when you need to:

• Show a static AF table, a linked CSV file, or a linked SQL table on a display.
• Keep only the rows inside the display time range.
• Show only the rows that match a condition, or only the rows of the selected asset.
• Color cells and rows by value so that errors stand out.
• Group a long table by a column such as the production line.
• Let authorized users edit the records of a static AF table.

Choose Time Series Table+ when the rows are PI Point or attribute values over time, or Events Table+ when the rows are event frames.

Before you begin

• Create the AF table in PI System Explorer. It can be a static table, a linked CSV file, or a linked SQL table.
• To look the table up from the display's asset, add an attribute of an asset to the symbol. The attribute must be in the AF database that holds the table. Without an attribute, choose the AF server and AF database in the pane instead.
• For a linked SQL table, link a database view or a query that returns only the rows the display needs. The filters of SQL Table+ run in the browser, so a smaller query loads faster. SQL decimal columns are not supported: cast them to float in the link query.
• To edit rows, use a static table and make sure the users have write permission to it.

Add and set up the symbol

1. Select SQL Table+ in the PI Vision symbol gallery, and drag an attribute of an asset onto the display.
2. Right-click the symbol and select Configure Table....
3. Under Table, set Source (1) to its first option. It looks the table up in the AF database of the first data source.
4. Select the table in AF Table (2). The columns of the table appear under Columns.
5. Click an empty point of the display. The symbol lists every row of the table.

To pick the AF server and database yourself, set Source to Custom. A display that uses Custom cannot be moved to another PI Vision server without choosing the table again.

Common tasks

The screenshots use an event log table named WriteTable. Each task continues from the previous one. The display time range runs from 5/13/2023 12:00 PM to 5/14/2023 12:00 AM.

Show only the rows in the display time range

Use this when the table has a DateTime column and operators move the display time range.

1. Right-click the symbol and select Configure Table....
2. Under Columns, select the DateTime column, here Time (1).
3. Select Filter (2).
4. Keep Mode (3) on Display Time.

The table keeps the rows whose time is inside the display time range. When no row falls inside the range, the table shows only its column headers.

Choose and label the columns

Use this when the table has helper columns or technical names that operators should not see.

1. Under Columns, select a column in the list (1).
2. Set Show (2) to OFF to hide it. Hide TimeConverted, Byte, and Temperature this way.
3. Select Location, set Label (3) to Custom, and enter Line.
4. Choose the Alignment (4) of the values.

The table shows the visible columns, and the header of Location reads Line.

Show only the rows that match a condition

Use this when one table holds rows for several purposes and a display needs one slice, such as the errors of one line.

1. Under Filter, set Enabled (1) to ON and Mode (2) to Basic.
2. Select + Add Condition (5).
3. Set Column (3) to Type, keep the equals comparison, set Value Type to Custom, and enter Error as the Value (4).
4. Add a second condition with Column set to Location and Value set to Line 2.

A row must match every condition. The table keeps the errors of line 2.

To combine conditions with or, set Mode (1) to Advanced, type an expression in the field (2), and select Apply (3). Use the column names, put text in double quotes, and group with parentheses. The field turns green while the expression is valid.

To follow a live value, set Value Type (2) to its first option, and select the data source (3). In Advanced mode, write '[1]' for the first data source. The condition in the image keeps the rows where Location equals the Name attribute of the asset. The table then follows the asset switcher.

Filter the table from other symbols

Use this when operators narrow the table by clicking values in another symbol.

1. Add SQL Filter+ on the same AF table, and set its Value to a column, such as Type.
2. Click a value in SQL Filter+.

The table shows only the matching rows. A filter chip in the corner of the table shows the count, such as 16 of 143 rows. Clear the selection in SQL Filter+ to see every row again.

To follow a symbol that uses other data, open Filtering here, select Add Input, enter the Channel of that symbol and set Target to a column. To keep this table out of filters on the same table, set Ignore Master to ON.

Color cells by state

Use this when operators must spot errors and warnings without reading every row.

1. Right-click the symbol and select Configure Multi-State....
2. Select the column, here Type (1), and select Enable Multi-State (2).
3. Set Property (3), here to Solid Fill.
4. Under Status, enter the value Error (5) and select +. Add Warning and Info the same way.
5. Select the color of each state (6).

Cells with a listed value take its color. A cell with another value keeps the table color.

Select Apply to row (4) to color the whole row with the state of the cell.

Group rows by a column

Use this when operators read a long table by line, unit, or status.

1. Under Columns, select Location (1).
2. Set Group (2) to ON.
3. Under General, select Count so that each group header shows its row count.
4. Clear Auto Expand to start with every group collapsed.

In the second image, (1) marks Auto Expand and (2) marks Count.

The table shows one header per value, such as Line 1 (22). Click a header to expand or collapse its rows.

Use the symbol

• Sort. Click a column header to sort by it, click again to reverse the order, and click a third time to return to the AF table's own row order.
• Filter a column. Type in the box below a header. Text columns match any part of the text, and ignore case. Numeric columns have Min and Max boxes. Boolean columns offer a list.
• Open a link. A cell that holds a web address shows a link icon. Click it to open the address in a new tab. When Link is on for the column, Open in chooses the place. Only addresses that start with http or https open.
• Reload. The table reloads on its own every minute by default. It also reloads when the display time range, the asset, or the table changes.
• Read-only mode writes nothing. Sorting, filtering, and grouping change the view only.
• Export. A CSV, PDF or Excel export holds the rows on screen. Rows that a filter hides are left out.

Edit rows in write mode

Write mode edits the AF table itself. Use it only on a static table. Set Mode under General to Write, and a toolbar appears below the table.

• Click a cell to edit it. A column with Allow Edit off stays read-only.
• Select rows with the check boxes at the left to delete them. With Range Selection on there are no check boxes: click a cell or a row number, or drag over several, then select delete. Until you do, delete is dimmed and removes nothing.
• Import CSV adds one row per line of the file and skips the first line as a header.

Important: Save writes the whole table back to AF, where other displays and users read it. Edits stay in the browser until you save, and the table does not reload while edits are pending. If a save fails, you see the error and the edits and undo history stay, so you can fix the cause and save again. Save keeps every column of the table, and every row, also the rows a filter hides.

A filter from another symbol can arrive while you edit. Your edits stay, an edited row stays in or out of view by its loaded value, and a row you added stays visible. Selected rows that the filter hides are deselected.

Configure the symbol

Right-click the symbol and select Configure Table.... The pane has the sections Table, Query Parameters, Columns, General, Filter, a data sources section, Style, and Export Data. Multi-state colors have their own command, Configure Multi-State....

Table

Query Parameters

Use these for a linked SQL table whose query has parameters. The pane only supplies values. The matching parameters and predicates must already exist in the linked table in PI System Explorer.

The server filters the rows before they reach the browser. A table with parameters is read-only, whatever Mode says. The PI Web API version must support the parameters argument of the table data call.

Columns

Select a column in the list, then set it. The arrows under the list move it up or down. Reload reads the columns of the table again, for example after a column was added in AF.

General

Filter

Filtering

Data sources

The data sources section lists the attributes or PI Points of the symbol. They supply the table lookup, query parameter values, and the values for filters. Reordering them keeps the '[n]' references of filters in step. Deleting a data source that a filter uses breaks that filter.

Style

Export Data

Multi-state

Right-click the symbol and select Configure Multi-State.... See Color cells by state.

Troubleshooting and limitations

Limitation: Linked SQL tables are read-only, and SQL decimal columns are not supported.