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.
For information on how to access a User-Defined Field in an AdHoc Query, please see our article on UDFs.
Creating an AdHoc Grid
When writing your own AdHoc Grid can be a difficult process for an inexperienced User. PCR-360 provides useful tools in both the AdHoc Grid's Query Builder and the Data Dictionary.
Navigate to Admin > Reports > AdHoc Grids.
Click the
button.Enter the desired SQL Statement
Click the
button to run the Query, to confirm the desired output is provided.Click the
button, on the Saved Queries tab to save the query for use later.Click the
button to open the Add New Grid form, which will also move the previously run Query so it is not needed to be re-entered.Provide a Name, Description, as well as any desired Role Permissions and any other customizations to the look of the new Grid.
Click the Save button to save the Grid.
The following functions are not allowed for use: INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, EMPTY, and SELECT INTO.
When writing AdHoc grids do not use spaces and other characters in the SQL column Aliases. Using characters other than UPPER-CASE and UNDERSCORE characters, the AdHoc Grid will break and cause any filters to not work.
The 
button will rebuild the column definitions based off the existing query and the existing column definitions will be replaced. Any customizations made will need to be reapplied.
Import/Export AdHoc Grids
PCR-360 contains functionality to Import/Export AdHoc Grids from one environment to another. This is particularly useful for building a new AdHoc Grid on a Test Environment and then not needing to rebuild it from scratch on a Production Environment.
A User can click the 
button the AdHoc Grids Grid, to upload a JSON File containing an AdHoc Grid definition.
A User can click on an AdHoc Grid displayed on the AdHoc Grids Grid and then click the 
button to save a JSON File containing an AdHoc Grid definition to their local machine.
AdHoc Parameters
AdHoc Grids have a “Parameters” tab on the Manage Grid Form. From this Tab, a User can 
Parameters that can be used on the AdHoc Grid.
These Parameter Details are similar to the Types of UDF Fields a User can add to Forms.
The new Parameters can be used within the BIND and/or REPLACE fields on the “Query” tab. Using the parameters in the query allows for quick adjustment of the data within the Results.
On the AdHoc Grid, Users can 
/ 
to show the values to adjust what is being used on the Grid, or to hide them for convenience.
On CustomerCenter, users are shown any AdHoc Grid Parameters by default, and they can not be hidden like they can within the backend application.
Query Inserts
From the Query Tab of the AdHoc Grid, Users have access to tools to quickly add to the query already written for the AdHoc. The “Tables” and “Columns” inserts function the same as on the query builder form, while the “Bind” and “Replace” inserts will assist with adding Parameters to the Query. “Bind” will insert the param as bind parameter (:param) in the query. “Replace” will insert the param as a replaceable parameter ({param}) in the query.
Note: Queries should not use the parameters in column names or aliases that will appear in the output.
AdHoc Grid Library
For the convenience of our customers, this Library contains pre-built AdHoc Grids that anyone may copy and add to their PCR-360 application. If you require an AdHoc query not provided by this Library, please contact your Account Manager to request Professional Services; your Account Manager will provide you with a quote to create an AdHoc tailored to your needs.
Using the Library
To load the provided queries into your Organization's instance of PCR-360, simply enter the desired query as your SQL Statement when creating an AdHoc
RECID vs. ROW ID
The usage of RECID or ROW ID is for clarity about what the RECID or ROW ID represents.
ROW IDs - Row Identification Numbers (ROW IDs) are unique IDs to the row of data in the grid they're assigned to. Some grids do not have a RECID but all grids have ROW IDs.
RECIDs - Record Identification Numbers (RECIDs) are unique IDs for records within a given table. When a grid references a specific record from a table, the Table Name is usually the column name of the RECID that the grid is referencing.
IE: SERVICES_RECID in any given grid is referencing the RECID value from the SERVICES Table.
Some table names are long and need to be abbreviated to meet the 30 character limit imposed by Oracle Databases.
There are several reasons both are used.
Some grids have multiple RECIDs and a ROW ID is unique to that row of data in the grid.
Some grids don’t have a RECID and the ROW ID is unique to that row of data.
Some ROW IDs are actually combined RECIDs for two or more records in the row.
Library of AdHoc Queries
Cable Locations Report Query
Service Desk Cabling Query
Inventory Reorder Details Report
Duplicate MAC Address Locator Query