(v1) .Reporting on AdHoc Grids v2019.7

AdHoc Grids allow for the creation of data Grids based on a User-Defined SQL query; any AdHoc Grid can be used to generate reports. Knowledge of the SQL language is required.

Navigate to Admin > AdHoc Grids > Manage Grids.

Query Builder

To build a Query, click the  button.  This button is located in the bottom left corner of the Grid.

This opens the Query Builder form.

SQL

The SQL field accepts any valid SQL select statement.

Results

The Table tab displays the dataset returned by the query in a table format.

The JSON tab displays the result set of the query in the JSON (JavaScript Object Notation) format.

Export to CSV

The  button allows Users to download the result set as a CSV file.

Saved Queries

Saving allows you to store and recall your queries.

• The  button replaces the active query with the selected one.

• The  button deletes the selected query.

• The  button similar to "Save New". However, replaces the selected query.

• The  button saves the active query.

Send to AdHoc Grid

Opens the active query in the AdHoc Grid definition form. The  button will open the Add New Grid form.

Grid Definition

Required Fields

• Name: The title of the AdHoc Grid to be created.
• Description: Explanation of the use of an AdHoc Grid.

Options

• (v1) Roles: Defines which User roles have access to the Grid. If none are selected, all valid Users have access. These Roles will also control if an AdHoc can appear to a Customer in the CustomerCenter. To make an AdHoc appear in the CustomerCenter, assign the Role or Roles that are used to make the CustomerCenter available within the organization.

• Autoload Grid?: When selected, the Grid will auto-populate when opened. Otherwise, it must be manually refreshed or a search must be applied.

Column Definitions tab

The Column Definitions tab shows the relevant data related to the Grid fields.

• Column Name: This is a read-only field, determined when the query is parsed by the application.
• Column Type: Has no effect on the query. Defines a type to be used for default column display filters.
• Label: Determines the text displayed in the column header.
• Description: When hovering the mouse cursor over the column header, this will be displayed as a tool-tip.
• Filter: Optional parameter to set a display filter for the values in the column.
• Width: The default width for the column.
• Visible?: If not selected, the column will not be visible on the Grid.
• Grid Button: Allows the addition of a Grid button to the AdHoc Grid that can be configured to meet a User need. See the Grid Button section for more details.
• Double Click?: Bind the double-click event to a Grid button.

Grid Buttons

Grid Buttons will allow the Admin to assign a Grid button to be available for Users on the AdHoc Grid. The Admin can customize what form will open, the label, and the icon of the button.

• Custom - Choosing the Custom form option will allow the User to set any form that already exists in PCR-360. Contact PCR for information about the form classes.

For convenience, the most commonly used forms are preset and selectable.

• Service Desk
  • Service Order - Opens the Order form when the button is clicked.
  • Incident - Opens the Incident form.
• Catalog
  • Location - Opens the Catalog: Location form.
  • Service - Opens the Catalog: Service form.
  • Equipment - Opens the Catalog: Equipment form.
• Services & Equipment
  • (v1) Equipment - Opens the Equipment form.
  • (v1) Purchase Order - Opens the Purchase Order form.
• People
  • Contact - Opens the Workers / Contact form.
• Admin
  • User - Opens the User Administration form.

• Important Note: Grid buttons can only be associated with column names that contain "RECID".

Double Click?

The double click option is bound to the Grid button option and as such the double click checkbox will not be available unless there is at least one assigned Grid Button. There can only be one double click checkbox set, and attempting to set another double click will override the existing selected checkbox. When a record is double-clicked in the Grid, the relevant form (attached to a Grid button) opens. There can only be one Grid button associated with a double click. Whenever a User attempts to double click a record on the Grid, the Grid will treat that double click as if they had selected the record and clicked the button.

Query tab

The Query tab displays the underlying query for this Grid. It is a read-only field. The primary use of this tab is so that the User can review what Query created the AdHoc Grid.

Opening the Grid

After the initial save of a new Grid, two additional buttons will be available on the form.

• The  button opens the Grid.
• The button opens the query in the Query Builder for editing.

SQL Errors

SQL errors will appear in the Grid when the SQL is invalid. In previous versions when the SQL was invalid, the Grid would only report that there was an issue. The actual SQL error will be reported back to the User when the query fails.