Reporting on AdHoc Grids
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
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
Actions - Opens the Manage Service Desk Action form.
Incident - Opens the Incident form.
Problems - Opens the Manage Problem form.
Projects - Opens the Manage Project form.
Service Order - Opens the Order form when the button is clicked.
Work Order - Opens the Manage Work Order form.
Services
Services - Opens the Manage Service form.
Cable Maintenance
Access Points - Opens the Add New Access Points form.
Cable Management - Opens the Manage Cable form.
Path Legs - Opens the Manage Path Legs form.
Pair/Strands - Opens the Manage Pair/Strand form.
Ports/Pins/Lens (PPL) - Opens the Manage PPL form.
VLANs - Opens the Manage VLANs form.
Catalog
Equipment - Opens the Catalog: Equipment form.
G/L Accounts (GLA) - Opens the Manage Account Number GLA form.
Location - Opens the Catalog: Location form.
Service - Opens the Catalog: Service form.
Inventory
Equipment - Opens the Manage Equipment form.
Purchase Order - Opens the Purchase Order form.
Return Order - Opens the Add New Return Order form.
Warehouse - Opens the Manage Warehouse form.
People
Contact - Opens the Workers / Contact form.
Department Hierarchy - Opens the Manage Department Hierarchy form.
Vendors - Opens the Manage Vendor form.
Workgroups - Opens the Add New Workgroup form.
Admin
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.