Open topic with navigation

Building a query

Building a query [>>] involves selecting columns from one or multiple PowerSchool SMS database schemas, and then selecting the fields that contain the data you want to display [Figure 48]. Based on your selections, PowerSchool SMS builds a grid of columns that display the data for the fields you specify. You can create queries that look for data across the district or data for a specific school. When there are many queries, you can organize them into categories [>>]. Note: A District Administrator can build queries for use across the district. With the appropriate permissions set by the district [>>], a School Administrator can build queries for his or her school.

Filters [>>] can be applied to queries, further refining the results the query will produce. You can set query properties so that users of the query are prompted to select the filter when they run the query [Figure 49].

To build a query:

1

In the Query menu, click All Queries.... The Advanced Query page appears.

=

Or, click the Shortcut to All Queries icon () in the Query menu.

2

In the Name column click the green plus sign (). The Add Query page appears.

=

To edit an existing query's properties, such as description, category, user access and filter prompting, or to mark the query as personal, in the Name column, hover your cursor over the query name and click the edit pencil.

=

To edit a query definition, in the Status column, hover your cursor over the query status and click the edit pencil

3

In theSchema Explorer window [Figure 48], expand a schema folder and click the schema column that contains the field to be queried. PowerSchool SMS moves the column into the Selected Columns window [Figure 48]. Note: After PowerSchool SMS moves the column into the Selected Columns window, the Schema Explorer displays only the columns for that schema. To choose columns from other schemas, click Pick a schema.... A list of related schemas appears.

4

In the Selected Columns window, click Define Column and Sort Order. The Define Column and Sort Order pop-up page appears.

5

Select and use the arrow buttons to specify the column display and sort order and click OK.

6

In the Criteria window, click Add and build the query criteria:

a

Set the field on which to search: click "Select a field..." and click a field name in the list. The field names available are only those from the columns in the Selected Columns window.

b

Set the operator: click "Operator..." and click an operator in the list.

=

The field you are adding determines which operators are available. Typical operators include: contains, starts with, ends with, equals, does not equal, is empty, is not empty.

c

Enter the value to find: click "Enter a value..." and type or select a value.

7

[Optional] Add another criterion: click Add and configure the new criterion. PowerSchool SMS displays the new criterion as: "Select a field... Operator... Enter a value" with AND OR buttons above it.

=

To set a criterion or group to be an alternative to the others, click OR above the criterion.

=

To add a criterion or group to the others, click AND above the criterion

8

[Optional] Logically arrange your completed criteria:

=

To group two or more criteria, select each criterion and click Group.

=

To ungroup the criteria in a group, select the group and click Ungroup.

9

Click Save Query. Enter the query properties [Figure 49] and click OK.

10

Click Test Query. [Figure 48] The query runs and displays a subset of query results in a grid of the columns you specified. To close the Edit or Add Query page, click Cancel.

Figure 48: Building a Query

More...

B

Columns: Click to see the schema's available columns. Click a column to move it to Selected Columns.

C

Pick a schema...: Click to choose columns from other related schemas.

D

Show/Hide: Select Hide to include but not display the data.

E

Define Column and Sort: Click to set the column and sort order for the grid of data produced by this query.

F

To delete a column from the query, select it and click the X.

G

Add: Click to add a criterion.

H

Delete: To remove a criterion, move your cursor to the end of the criterion until a dotted box appears, click within the dotted box to select, and then click Delete.

I

Group/Ungroup: Click to group criteria together or to ungroup grouped criteria.

J

Restart: Click to erase field criteria and start again.

K

Show SQL: Click to view the query's SQL code.

L

Reset: Click to revert to the previous instance of the query definition.

M

Test Query: Click to run a test of the query. A test query produces a maximum of 100 records.

Figure 49: Query properties

More...

B

Category: [Optional] Select the category [>>] to which the query belongs.

C

Description: Describe the results that the query will generate.

D

Personal: Click to make the query visible only to you.

E

User access: Select the role(s) that can run this query and use the arrows to move the role(s) into the Required fields list.

F

Prompt to select a filter before running: When you select this option, PowerSchool SMS prompts the user to choose a filter when they run the query.

Managing query categories

If you have many queries and need to keep them organized, you can create categories [Figure 50], and then group the queries into these categories.

To add a query category:

1

In the Query menu, click All Queries.... The Advanced Query page appears.

=

Or, click the Shortcut to All Queries icon () in the Query menu.

2

Click the Category pencil icon [Figure 42]. The Manage Categories dialog appears [Figure 50].

3

Click the green plus sign () and type the category name.

=

Note: You cannot edit or delete the "N/A" category.

4

Click OK.

Figure 50: Manage Categories

More...

B

Type a category name.

C

Click to delete a category.

Adding a query to the Query menu

You can add up to 20 predefined queries to the Query menu.

To add a query to the Query menu:

1

In the Query menu, click Edit List.... The User Settings page appears.

2

Select the query.

3

Click Apply.

www.powerschool.com
Tel: 866-434-6276
Email: smssupport@powerschool.com

Copyright 2015-2016 PowerSchool Group LLC and/or its affiliate(s). All rights reserved. All trademarks are either owned or licensed by PowerSchool Group LLC and/or its affiliates.