Creating and Configuring Custom Queries

Custom Queries can be created and integrated within the Virtual Ticket 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 also be integrated into the rest of the Virtual Ticket system as an individual menu item to be accessed by other users defined in the system.

Procedures

Creating a new Custom Query

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 menu that you want the query to appear under from the Menu popup. Once the setup is complete, Virtual Ticket will display the Custom Query in the selected menu. Alternatively, you can type a new name to add a menu to the application menu bar. If you select the <Hidden> option, the query name will not appear in any menu.
5. Optional: Type a name in the Category field to create a sub-menu under which the Custom Query will appear.
6. 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.
7. 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.

8. 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.

9. Click SAVE. This will save the new Custom Query in the Virtual Ticket system and display the query name in the Structure grid of the window.
10. 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 Virtual Ticket 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

11. 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, the top of the Custom Query window will contain dialog boxes for you to enter values for each parameter.

12. 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.

13. Click the SAVE APPEARANCE button to save the settings for future use with the Custom Query.
14. 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.
15. Close the window when finished. The Test Query window will close, returning you to the Custom Queries Setup window.

Assigning access privileges for Custom Queries

16. 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 Virtual Ticket system.

17. 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.

18. Click Save to save the access privileges and make the updated settings available to the end users of the Virtual Ticket system.

::Related Topics
:: Virtual Ticket Administrator User Guide