Restricting access to web report records by using a query builder

The Report Console includes a simple query builder that allows you to quickly construct a simple query. By default, the report designer screen opens with the simple query builder active. 

The advanced query builder is located below the simple query builder. By default, the advanced query builder is closed. Click the expansion arrow to open it. 

To use the advanced query builder to find the same records (Teresa Logan's classes that have students enrolled), expand the advanced query builder and then add the following qualification: 

'Instructor' LIKE "Teresa%" AND 'Number Enrolled' > 0 

To add fields, you can drag them from the Available Fields list, or select the field and then click Add Field. The query builder buttons appear after you add a field or click in the query area. 

You can enter queries in both the simple and advanced query builders for the same report. If you do, these queries are linked with an AND operator when the report runs. If the advanced query builder is closed, but contains a query, the beginning of the query appears along with the Advanced expansion button:

You cannot add elements in the middle of an existing query in the advanced query builder. To modify an advanced query, add the modification at the end of the existing query, or revise the entire query. You can also use these query builders to add a query to an existing report at runtime. See Updating-a-query-or-qualification-at-runtime

To use the simple query builder

1. In the Filter By area of the report designer screen, select a field from the Available Fields list.
2. Drag the field to the query area, or click Add Field.
3. Select the query operator:
   • Is equal to—Selects records in which the value in the chosen field exactly matches the value entered in the query.
   • Is not equal to—Selects records in which the value in the chosen field does not match the value entered in the query.
   • Is empty—Selects records in which the chosen field is empty.
   • Is not empty—Selects records in which the chosen field contains some data.
   • Is LIKE—Selects records in which the value in the chosen field matches the string defined in the query.
     The LIKE operator requires that you use the percent (%) wildcard, which matches any string of 0 or more characters. For example, to get a report of classes for which Teresa Logan is the instructor, use one of the following search strings:
     • Teresa% matches all entries that begin with "Teresa"
     • %Logan matches all entries that end with "Logan"
     • T%eresa% would find entries that start with "Teresa" or "Theresa"
4. Type the value to search for in the blank field.

For example, to find Teresa Logan's classes that have students enrolled, you could use the simple query builder to construct the following query:

To use the advanced query builder

1. Make sure a report is created as described in Creating-and-managing-reports-from-the-AR-System-Report-Console.
2. Open the Report Definition screen.
3. In the Filter By area, select the first field that you want to use in the query from the Available Fields list.
4. Click Add or drag the field to add it to the simple query builder.
5. In the simple query builder, click the down-arrow and select from the list of operations.
6. Enter the value to search for.
   For example, to find classes for which Teresa Logan is the instructor, select the Instructor field and the is equal to operation, and then type in Teresa Logan.
7. To add another item to the qualification, select the appropriate field from the Available Fields list, and then click Add or drag the field to the simple query builder.
   The second search criterion is added to the simple query builder with an AND search. In other words, a record must match both conditions to appear in the report.
   For example, to find Teresa's classes that have at least one person enrolled, select Number enrolled and is greater than, and then enter 0.
8. Click Save.

Queries on Selection fields

Selection fields include drop-down lists, and check boxes. In selection fields, the sort order is determined by the database value assigned to each selection. This value is not visible when you are constructing the query. Depending upon how the database value is configured, you might get unexpected results. For example, a Priority field in which the user selects High, Medium, or Low might have the database values High=0, Medium=1, Low=2. In this case, the query "Priority is greater than or equal to Medium" will return records with priority set to Medium or Low, because in database terms, the qualification is seeking values greater than or equal to 1. 

If this occurs, try revising the query to use the opposite operation, for example "Priority is less than or equal to Medium," and then re-run the search.