Grid

Grid Properties | Configuring a Grid

A grid displays tabular data similar to a spreadsheet. SQL and non-time based data are the most frequently used sources for grids; however, any data source can be used with grids. The Historian Grid is recommended for use with historical data.

For basic information on Proficy Portal grids, see Grids.

You can configure a grid to allow the following types of modifications during run-time:

• Inserting rows

• Updating rows

• Deleting rows

The databases that can be written to include Historian (with the Historian Grid), SQL, Oracle, Access, and DB2. To allow a database to be written to with a grid, the following considerations must be made:

• The user must have write permission at the data source.

• The grid must be configured to accept changes. See To configure a grid to accept changes for more information.

• For Grids (not Historian Grids), SQL statements specific to the types of changes to be made are needed. For example, if you want the user to update the database, an SQL UPDATE statement must be configured and entered in the grid's configuration panel (Data tab). For more information on writing SQL statements, see Designing SQL Statements.

Mapping Parameters from Grid Columns

When SQL statements are used to allow users to update, insert, or delete rows in grids, you may be required to map parameters from the statement to the grid columns. This is done when the SQL statement is set on the grid's configuration panel Data tab. In the following example, the OrderDate parameter has been mapped to the grid's OrderDate column name.

 

For instructions on how to map parameters to grid columns, refer to To configure a grid to accept changes.

Thresholds

You can configure a grid (or an Historian grid) to color the displayed data based on a a specific value or on a specific range of values. For example, if a value is between 0 and 20, the value appears red but if the value is between 40 and 60 value is blue. These ranges of values are called thresholds.

Using thresholds, you can easily provide users with visual cues about the quality of the data. For example, the following figure shows thresholds defined for ranges of values. Values 0 - 20 are set as green, values 20 - 27 are set as yellow, and values 27 - 50 are set as red. On the grid during run-time, the values are colored accordingly.

The threshold table (on the Grid Configuration Columns/Threshold tab):

 

The grid at run-time:

 

Threshold values do not need to be numeric; they can also be string values. For example, you can use the Exact Match type threshold and enter the string in the Value field. When the grid is run, it will display all matching string values in the color you specified.

Grid threshold defaults are defined in the application preferences (see Application Preferences). You can modify the threshold values and the specific colors to use for any grid. You can also add threshold ranges as you need for each grid column.

Alternatively, if you have pre-configured threshold tables you want to use you can apply a threshold table to the grid with the Load button. When the table loads, it overrides the current threshold settings. Likewise, if you create a threshold table you want to reuse, you can save it with the Save As button.

You can also define a color value outside the range of the threshold table by clicking the Edit Out of Range button and selecting a color to use.

Enumeration

When you configure a grid to accept changes, you can enter new values into individual cells during run-time. If you have a large amount of data to enter, this method of entering data can be error-prone. One way to reduce data entry errors is to configure one or more columns to accept entries from a pre-determined list of valid values. With this method of data entry, the user can only select values from a drop-down list.

You can configure a pre-determined list of values for any column in a grid by using an SQL SELECT statement that has been configured to retrieve the necessary values. The SQL statement must use the same values as those of the column it will update.

You also have the option of manually entering a list of values that will be used to update the grid column.

In the following example, the user can select the product name from the drop-down list when entering a product shipment record. To set this up an SQL SELECT statement was created for the product name and set as the enumeration source for the ProductName column. For more information on writing SQL statements, see Designing SQL Statements. For step-by-step instructions on setting up enumeration, see To enumerate grid values.

 

Linking Grids to other Objects

Grid properties can be linked to parameters in other objects in a number of ways. For example, you can link a grid to a chart so that when the user clicks on a specific cell in the grid, the value of that cell is passed to the chart. In this example, the data source for the chart is an SQL statement with a parameter that is satisfied by the value passed in from the grid. The following source properties are available for grids:

The source property you set as the source for a connected parameter will depend on the grid-specific configuration settings and how you want the parameter to be passed to another object.

A grid can be configured to update several objects when the user clicks on a cell, column, or row; however, a single value only can be passed to other objects. For example, you can configure the grid so that when a user clicks on a cell, the value is passed to parameters in another grid and several types of charts at the same time. However, the same value must be passed to the other objects. You cannot configure the grid so that when a user clicks on one cell, a value is passed to another object, and when the user clicks on another cell in the same grid, a different value is passed to another object. There is one exception to this rule, however, and that is when grid column parameters are passed to the Hyperlink object. For example, a grid may be configured with a number of parameters and each parameter can be linked to its own hyperlink. This method of linking a grid's parameter row to a hyperlink is used to pass parameters from one display to another. For more information, see Passing Parameters Between Displays.

A grid may be configured to use a stored procedure to allow for updating, inserting, or deleting of rows during run time, and although these stored procedures appear in the Link To dialog box, a stored procedure cannot be used to pass parameters from one object to another.

For examples of Link Property To functionality and step-by-step procedures, see Parameter Connections.

Grid Printing Options

The grid expansion options allow you to set each grid to print differently in Run mode, depending on how much or how little of the grid's data (rows and columns) that you want to be printed. These printing options apply to grids that are printed as part of a display; the options do not apply to grids that are printed using the Print Grid command. There are three grid expansion options available on the Printing tab of the grid's Configuration dialog box:

• No Expansions – when the display is printed, the grid is printed as it appears in your display. Any columns or rows that are hidden do not print.

• Vertical Expansion Only – when the display is printed, the grid's width is printed as it appears in your display; however, the entire length of the grid is printed. For example, if you have a grid that retrieves 100 rows of data (vertically), the grid will be printed on as many pages as are needed to show all the rows.

• Vertical and Horizontal Expansion – when the display is printed, the grid's entire length and width is printed (that is, all data retrieved by the grid is printed).

• • Fit to – additionally, with the Vertical and Horizontal Expansion option you can specify a number of pages that you want the grid to `fit to'. For example, if the grid is too wide to fit on one page, you can set the Fit to option to one page. The grid will then be scaled so that all the columns appear on one page.

See Also

---