Creating Problem Report Queries

Queries are used to determine which problem reports are displayed on the main window. There are two levels of queries, system wide and user defined. System wide queries can only be created and edited by users with administrator access, and are available for use by all users. User defined queries can be created by anyone, but are only available for use by the user who created them.

PRepS comes with several predefined system wide queries. Many of these queries are closely tied to the default statuses, and thus should be modified to suit your needs if you change the status attribute setup.

<ALL>

Show all problem reports for the selected project(s). This is really the default query with nothing selected to limit by.

New Problem Reports

Show all problem reports that have a status of "new".

Closed Problem Reports

Show all problem reports that have a status of "closed".

Ready for Verification

Show all problem reports that have a status of "fixed" or "not a problem". In other words, these are problem reports that need to be verified that they are really fixed, or are really not a problem.

Unassigned Problems

Show all problem reports that are not assigned to anyone.

Besides there being two distinct levels of queries, system wide and user defined, there are two distinct types of queries, standard queries and raw SQL queries. Standard queries are easier to setup, but raw SQL queries offer more flexibility, and can do some things that the standard queries cannot (for an example, look at how the Unassigned Problems query is setup).

Standard Queries

Standard queries allow you to limit the problem reports selected based on severity, status, problem type, submitter, and assignee. Standard queries also allow you to setup a default order by, though the ordering is easily changed from the main window. Standard queries are easy to setup, yet flexible. Most of the pre-defined queries are standard queries.

Figure 4-8. The Standard Query Edit Dialog

The standard query edit dialog is displayed when you create a new query by pressing the New button on the query list dialog, or when you press the View/Modify button while a standard query is seleted from the query list.

Query Name

The query should have a short but descriptive name. This is the name that is displayed in the query drop down list on the main window.

Description

Provide a longer description of the query here. This information is only displayed here and in the query list dialog. It should provide comments on the intention of the query, or any other information that may be usefull to whoever maintains the queries.

Severity

Selecting severities from the list limits the results of the query to only the problem reports with the given severities. If no severities are selected, then the query is not limited by severity (problem reports of any severity are returned).

Status

Selecting statuses from the list limits the results of the query to only the problem reports with the given statuses. If no statuses are selected, then the query is not limited by status (problem reports of any status are returned).

Problem Type

Selecting problem types from the list limits the results of the query to only the problem reports with the given problem types. If no problem types are selected, then the query is not limited by problem type (problem reports of any type are returned).

Submitter

Selecting users from the submitters list limits the results of the query to problem reports submitted by the selected users. If no users are selected, then the query is not limited by submitter (problem reports submitted by anyone are returned).

Responsible

Selecting users from the responble list limits the results of the query to problem reports assigned to the selected users. If no users are selected, then the query is not limited by assignee (problem reports assigned to anyone are returned).

Order By

Place the default order here. At one time, this was useful, but at this point it is pretty pointless, since the user can easily change the order from the main window.

Raw Queries

With raw SQL queries, you are given the start of a SQL statement, and your job is to complete the statement to return the problem reports that you want. The raw query is intended for use when the standard queries will not work. For example, the pre-defined "Unassigned Problems" query is a raw SQL query. It is setup as a raw SQL query because it is getting all problem reports where the responsible ID is NULL. This is not possible with a standard query.

Raw SQL queries are very flexible. However, they are also more prone to errors than standard queries, and require a knowledge of the PRepS table structure. For that reason, standard queries should be prefered.

Figure 4-9. The Raw SQL Query Edit Dialog

The raw SQL query edit dialog is displayed when you create a new raw SQL query by pressing the New Raw button on the query list dialog, or when you press the View/Modify button while a raw SQL query is selected from the query list.

Query Name

The query should have a short but descriptive name. This is the name that is displayed in the query drop down list on the main window.

Description

Provide a longer description of the query here. This information is only displayed here and in the query list dialog. It should provide comments on the intention of the query, or any other information that may be usefull to whoever maintains the queries.

SQL

PRepS is expecting the SELECT part of the SQL statement to be in a particular format. Also, the information is only obtained from certain tables, and those tables must be properly linked. For that reason, PRepS provides the start of the query. Your job in creating a raw SQL query is to finish the query. The default raw SQL query is printed on the dialog to aid you in completing it.