E-mail
Creating and Configuring Custom Queries
Custom Queries can be created and integrated within the Job Manager system through the Custom Queries Setup window. This window allows you to define your own SQL-based query that will be run against the data stored in the database. Each SQL query may also contain custom parameters, allowing the user to enter the specific data necessary for the query to run properly (e.g. a job number, a date range, a customer name, etc.).
When a Custom Query is created at the administrative level, it can be integrated into the rest of the Job Manager system as a link contained within the Custom Queries & Reports box at the bottom of most home pages.
Opening the Custom Queries Setup window
1. Click the Custom Queries link in the Administrator page of the Job Manager web client. The Portal Integration Tool reminder message will appear. (If you have turned off the Portal Integration Tool warning message, the Job Manager desktop client will open the Reports and Custom Queries window.)
2. Click OK ( 
). The Portal Integration Tool download reminder will close. The Job Manager desktop client will open to the Reports and Custom Queries window.
Note: If the Portal Integration Tool is has not yet been installed, you will need to install it before you are able to open the Job Manager desktop client. For more information on this, refer to the "Portal Integration Tool Guide".
1. Select Reports and Custom Queries from the Administration menu. This will open the Reports and Custom Queries administration window if it is not open, or bring it to the front if it is already open.
2. Click NEW ( 
) to and select Custom Query to begin creating a new custom SQL query.
3. Type the name of the new Custom Query in the Name field.
4. Select the appropriate page on which you want links to the Custom Query to appear in the Menu field. When selected, the Custom Query will appear in the Queries and Reports box at the bottom of the corresponding Job Manager pages. If you select the <Hidden> option, the query name will not appear in any page.
5. Type the SQL commands you want the Custom Query to utilize in the SQL Query Text field. This field utilizes standard SQL commands to determine the columns and data that will be included in the query result.
6. To add parameters to the Custom Query, click the NEW PARAMETER button ( 
) located in the Parameters area of the window and specify the description, SQL variable, datatype, and datatype length (for character fields only) in the appropriate fields of the appeared dialog window. In general, the SQL variable will be the database field name that will be used as the parameter to the end user (e.g. order_entry.job_number, customers.customer_name, etc.). To edit the definition of a parameter, double-click the corresponding entry in the Parameters list.
7. To ensure that the Custom Query is error-free and executed correctly, preview the results that are retrieved from the database when the query is executed. For the detailed instructions on how to preview Custom Query results, refer to the "Previewing Custom Query results" procedure below.
TIP: You may wish to preview the results during the course of creating the Custom Query to test the SQL commands that are being written and executed. This will help to ensure that the SQL commands being used do not contain errors and actually retrieve the expected information from the database.
8. Click SAVE. This will save the new Custom Query in the Job Manager system and display the query name in the Structure grid of the window.
- Assign access privileges for the new Custom Query as needed. For the detailed instructions, refer to the "Assigning access privileges for Custom Queries" procedure below. By default, no access privileges are set.
At this point, the new Custom Query has been created, tested, and integrated into the rest of the Job Manager system and is ready for use by the users defined in the system.
To edit a Custom Query, select the corresponding entry in the Structure grid of the Reports and Custom Queries Setup window and make changes as needed and click Save.
Previewing Custom Query results
9. While the Reports and Custom Queries Setup window is open, select the desired Custom Query in the Structure grid and click PREVIEW... button to view a preview of the results that are retrieved when the Custom Query is executed*.* *
*
If parameters have been defined, .
10. Resize the columns widths and window size as needed to view the information properly within the Test Query window. To resize the width of a column, move the pointer over a boundary of the column. When the pointer becomes a double-sided arrow, drag the boundary to the location that you want.
11. Click the SAVE APPEARANCE button to save the settings for future use with the Custom Query.
12. To update the query results displayed in the listing, click the SEARCH button and, if applicable, specify the parameters values that should be used to view the Custom Query results.
13. Click DONE when finished. The Test Query window will close, returning you to the Custom Queries Setup window.
Assigning access privileges for Custom Queries
1. While the Reports and Custom Queries window is open, select the desired Report or Custom Query in the Reports and Queries pane and click the Access tab . A dialog box will appear, allowing you to assign access privileges to the users and groups defined in the Job Manager system.
2. Assign access privileges to the appropriate users and groups. Available privileges include:
View - allows specified users the ability to view the custom query.
Save - allows specified users the ability to save the custom query results.
Print - allows specified users the ability to print the custom query results.
3. Click Save to save the access privileges and make the updated settings available to the end users of the Job Manager system.