A grid is a select statement that you would run on a database. It defines the columns you are looking for, the tables from which these columns are derived and the conditions that must be satisfied to show the data. The reason it is called a grid is because after running the select the result looks like a grid consisting of rows and columns like you would see in Excel.
The Grid Designer screen has the following important attributes:
-
Grid Name – Must be 6 characters exactly and the second character must be a ‘U’. Unless the Grid Type is ‘Tab’ in which case it must be 3 characters starting with ‘X’.
-
Grid Type – Make sure you select ‘Alert Management’ if this grid will be used on the Alert Management screen. Otherwise select ‘List View’ for a stand-alone grid that you can apply to the HxGN EAM menu, or select ‘Tab’ if this grid will be a new tab on an existing HxGN EAM screen.
-
Parent Screen – Only required if you selected ‘Tab’ for the grid type.
-
From Clause – List the tables that you want to select data from. Obviously one table is the minimum, but if you need to join multiple tables together you can specify them all here in this field.
-
Select Clause – List the columns you would like to include. You can leave this blank initially and define the columns on the Fields tab of this screen later. Columns for a list view or a tab type of grid can be displayed in the grid and can be used by the Dataspy for filtering and sorting. On alert management grids the same holds true, but there the columns can also be used as ‘parameters’ to:
-
Populate the e-mail message content or subject.
-
Determine the e-mail recipient:
-
indirectly if you include an Employee, User, Contact (from the Call Center) or Supplier code (E-mail Messenger will substitute the code with the associated e-mail address automatically) or
-
directly by including a column with the e-mail address.
-
-
Populate work order attributes like Type, Status, Description, etc.
-
-
WHERE Clause – List all conditions that apply. Typical conditions that you would find in the where clause of a select statement, like act_event = evt_code or evt_rstatus = ‘R’, are all applicable here. Additionally, you can make use of (substitution) parameters. Feel free to define these parameters yourself. Some examples:
-
If this is a tab on the work order screen and you want certain data that pertains to the selected work order to show you must add a line to the where clause like ‘and xxx_event = :WOnbr’. On this line :WOnbr is the parameter that during execution of the grid should be replaced with the selected work order number. Some additional setup is required for this on the Parameters tab.
-
If this is an alert management grid searching for a temperature value and you do not want to hard code the temperature because you want the grid to be usable for alerts in all regions of the country, then you could add a line to the where clause like ‘and xxx_temperature > :TempThreshold. On this line :TempThreshold is the parameter that can be assigned a value on the alert (on the Grid Parameters tab) and that will be used during the execution of the grid.
For alert management grids there is a system parameter called :parameter.alert. During
execution of the grid this parameter will always hold the Alert code. A very handy
parameter indeed, if you need to join to the alert history for some reason.
-
Designing a grid should be done by IT experts that are well versed in SQL. Often, for more complicated select statements, it is best to write the select statement in a dedicated tool, like SQL*Plus or equivalent, and execute it in that tool and then, once the select statement works as intended, cut and paste the results into Grid Designer.