PSFS IQ - Query Builder: How to create, run, modify, and save a new query

How to create, run, modify, and save a new query

A query enables you to pull information from your database based on the criteria you specify. This topic steps you through the processes of creating, running, modifying, and saving a new query.

Sections in this article

How to create a new query

How to run a query

How to modify a query

How to save a query

How to create a new query

If you are a beginner, you may find it helpful to review some suggestions that can help you save time and avoid some design problems. For details, go to Suggestions For Designing Queries.

Click New Query.
Choose the columns that contain the information you want to include in the query. To do this, complete these steps:

a. In the Column Picker panel, find the group that contains the columns you need. Then, click to expand the group to view the columns.

Some groups contain subgroups. Click to expand the subgroup to view the columns within it.

b. Scroll down the list and select the checkboxes next to the columns you need. For example:

At this point, the order in which you select the columns does not matter. You can change the order in which the columns appear in your results later.

c. Add the columns to the Result Columns panel by doing one of the following:

One-by-one, drag each column to the Result Columns panel, making sure that you place each column directly on top of the [Add new column] link.
At the bottom of the Column Picker panel, click .

If you need to add more columns to the query, either use the drag-and-drop method or the add column link method. Continue adding columns until all of the columns you need in the query are showing in the Result Columns panel.

Be aware that the order in which the columns are listed in the Result Columns panel is the same order in which they appear in your results. If you want to change the order in which the columns appear in your results, complete the following:

You can also drag use the drop-and-drop method to re-order columns. To learn how to use this alternative method, go to Using Drag-and-Drop to Reorder Columns.

a. Place your cursor on top of the name of the column you want to move. Slide the cursor to the left until you see this icon: . Click it to display this menu:

b. The column order options are at the bottom of the menu. Select the desired option to move the column into the desired position:

Move to the first: moves the column to the top of the Result Columns list. After you run the query, this column's results appear first in the Query Results panel.
Move to the previous: moves the column up one position in the Result Columns list.
Move to the next: moves the column down one position in the Result Columns list.
Move to the last: moves the column to the last position in the Result Columns list. After you run the query, this column's results display last in the Query Results panel.

If you want to summarize a column's data, you need to apply an aggregate function to it. To do this:

For information about aggregate functions, go to How to Calculate a Column's Data (Aggregate Functions).

a. In the Results Column, place your cursor on top of the name of the column whose data you want to summarize.

b. Slide your cursor to the right until you see this icon:. Click it to display the aggregate functions menu:

c. Select the function you want to apply to the column's data.

For example, if you want to count the data in the column, select Count. The name of the function is added to the column name to indicate it will be applied to the column's data, as shown below:

To remove the function from the column, click again.

By default, the information in a column is not sorted. If you want to a column's information to display in a specific order, you need to specify a sorting order. Do the following for each column whose data you want to sort:

a. Place your cursor on top of the name of the column. Slide the cursor to the left until you see this icon:. Click it to display this menu:

b. The sort order options are in the group at the top of the menu. Select one of these sort options:

Ascending: sorts the column's results in ascending order. For numeric results, sorts the information from lowest to highest. For alphabetic results, sorts the information in A to Z order. For dates and times, sorts the information from least recent to most recent.
Descending: sorts the column's results in descending order. For numeric results, sorts the information from highest to lowest. For alphabetic results, sorts the information in Z to A order. For dates and times, sorts the information from most recent to least recent.

By default, no conditions are applied to columns in the query. Conditions are criteria you can apply to a column to limit the results. For each column you need to apply a condition to, do the following:

If necessary, take a look at some examples of query conditions to help you think about your own query criteria.

a. Click the Add new condition link in the Query Conditions panel.

b. Select the column to which you want to apply the condition.

A condition statement that applies to that column's data is displayed. For example, selecting the Families > Family DUID column displays this condition statement:

c. Specify the condition by selecting an operator and a value. For operator descriptions and examples, see Query Operators and Examples.

Now that you have chosen the columns you want to include in your query, specified the arrangement of the information, and applied conditions, you are ready to run your query. Go to Running a Query, which follows.

How to run a query

How to modify a query

If the results are acceptable, you can skip this step and go to Saving a Query

If you need to make changes to the query, consult these topics for information on how to make specific changes:

● How to Add, Remove, or Replace a Column

● How to Reorder the Columns in Your Query Results

● How to Rename a Column Heading in Your Query Results

● How to Sort a Column's Data

● How to Add, Edit, or Remove a Condition

Re-run the query to view the results of your changes.

How to save a query

After you run the query, you can save it for future use. You keep the query private (for your eyes only) or you can share it with other users.

To save your query, click Save Query.

The Save Query window opens:

Complete the following:

a. (Required) In the Name field, type a name for the query.

b. To share the query with other users in your organization, select the Global check box. If you want the query to remain private, leave the box unchecked.

c. To assign a tag to the query, select one from the Tags dropdown list.

The Tags list contains only the tags saved in your system. If you need to apply a tag not found in the list, you must create it first. For instructions, go to Creating a Tag. You can then edit the details of the query to apply the tag you created.

d. In the Description field, type a description of the query.

It is a good idea to add a description that indicates the purpose of the query.

Click Save.

The system saves the query and adds it to the My Queries and Recent Queries lists. You see this message next to the name of the query in the title bar: .