Query

When in the Reporting Database (REPT) click Reporting Tools > Query > Query Manager. Automatically your system defaults to find an existing query and in the drop-down menu Query Name should appear (if not select the drop-down arrow and select Query Name). In the adjacent box, type in the name of the query in which you are searching. Click the Search button and your query should appear.

*Note: You can elect to type in a partial amount of the title you are searching and your search will pull up all queries that begin with your search information.

[insert screenshot]

How do you run a Query?

You can run a query one of several ways. After searching for a specific query, when looking at your search results, toward the right hand side there are hyperlinks available for you to make a selection on how you would like your query results to appear. Two columns appear with the titles: Run to HTML and Run to Excel. By clicking either of these two hyperlinks - in the row where your query appears - will run your query and return the existing results.

Run to HTML will: Run your query and pull up your results in an HTML format.

Run to Excel will: Run your query and pull up your results in MS Excel in a spreadsheet.

Another option on how to run a query is by selecting the Run tab on the top right hand side of the screen when editing query tables, fields, and criterion.

[insert screenshot]

What is the protocol for naming and saving my queries?

When creating your own queries from existing queries it is proper “query etiquette” it select save as and rename the query as your own - do this by putting your initials in front of your query name. The same goes for creating your own query. This is important because it effectively keeps queries organized in the system.

For example: WAP_QUERY_EXAMPLE

How do I know there are no existing queries with the same name?

Search the title in the Query Manager subsection of Reporting Tools to find an existing query.

How do I create my own Query?

Reporting Tools> Query> Query Manager> select the hyperlink that states: Create New Query, follow by adding tables.

I have access to Query Viewer but not Query Manager. What does this mean?

This means you have run only access; you can not write queries but you may view queries that have been created.

How do I delete a query?
Go to Query Manager, search for the query you want to delete. Click the box on the left hand side of the query are deleting. Click the *Action drop down arrow and click Delete Selected. Then click Go and your query will be permanently deleted.

How do I rename a Query?

Go to Query Manager, search for the query you want to delete. Click the box on the left hand side of the query are deleting. Click the *Action drop down arrow and click Rename Selected. Then click Go. Type in the new name and click OK.

Should I have the distinct check box checked?

Yes, this prevents duplicate data. To do this go to the fields tab, click properties, and check the distinct box.

What does the null condition type do?

How do I know what tables I have access to?

Reporting Tools> Query> Query Manager> select the hyperlink that states: Create New Query. *Notice*: the Records Tab in the upper right hand side of the page is selected – the records tab is where tables are added to your query. Select the Search button to see what tables you have access to.

[insert screenshot]

How do I add a table to my new/existing Query?

Select the Records Tab if not already selected. Existing record is also another name for existing tables. Type in the name of the table in the space provided. You may search for a table by either access group name, contains field name, description, or records name. Your system will automatically default to records name. Select the Search button and your table should appear. Select add record to the table you would like – *Notice*: after selecting a record/ table it will transfer you over to the Query Tab – this means you have effectively selected your table to be included in your query. Check all fields that you would like to appear in your query and select the Fields tab to see all your selected fields.

[insert screenshot]

What table contains active and leave employees only?

NW_Employees

What table contains position funding information?

NW_Posn_Dist

How are the GL tables organized?

They are organized by fiscal year – September 1^st through August 31^st.

When using the GL tables, is there anything important that I have to know?

Always display NW_GL_NUM_ASSIGN as a field in the output because it will make your data unique.

What table contains born active and terminated employees?

JOB

How do I reorder or Sort my fields?

This is done by selecting the reorder/ sort button the right upper hand side of the screen. On the left hand side there are fields provided for you to type – put the appropriate number in the box next to the field where you would like that fields results prioritized.

Can I alphabetize fields?

Yes, you can alphabetize fields by selecting the AZ sort button on the upper right hand side of the screen. It is a small icon, but very helpful!

What does the fields tab do?

It displays fields that will appear in the output (excel) report. You can customize how fields appear on the output report from this tab.

How do I add a field?

You can add a field by clicking the Query tab and clicking on a folder to the left of a displayed record. This expands your options. Then click on the fields you would like to add to your query and click the Fields tab to see your selections.

Back to Top
How can I obtain FASIS fields and appearance codes?
For example: What is a paygroup and what paygroups are used at Northwestern University?

You can look on the FASIS website or refer to your lookup class materials.

How do I customize field heading text?

Click the Fields tab then the edit button for any headings you wish to change. In the heading box click text and type the desired information in the Heading Text box. Click O.K.

What does criteria do?

A criterion is used to filter information from the FASIS database and the records you have selected. It helps extract the specific information you would like to pull from the database and helps include and exclude data.

How do I add Criteria?

When you have selected your query and you are editing/adding information click the Criteria Tab. Then click the yellow Add Criteria button. Under the Expression 1 box you are selecting what field you would like to filter out information. Select the magnify glass and select the field you are interested in filtering. Keep in mind that there are many fields for as many records/tables you have added to your query. If you need to obtain a field from a different folder than what your computer has elected, simply select the Show Fields button next to the table name.

[insert screenshot]

Once you have selected your field. Select the condition type.

For example: Deptid “is equal to” XXXXXX” – the condition type is in quotations and the department number I am filtering is represented by the X’s.

After selecting the condition type in Expression 2 box you must select a constant/value. Pending on what your field is your constant may be a department number, a specific benefits plan, etc. You may click on the magnifying glass to elect what type of information you are looking for in the field you have selected. Once you have completed the necessary information click OK.

*Adding Criteria is different for every field selection; you can refer to your Query Training Manual on page 42 – Establishing Criteria. In your manual from training you can find much more extensive information can suit your needs.

I would like to establish criteria that have more than one constant.

For example: You want people who are in paygroup MON and MOF.

When creating criteria select “in list” as the constant, click the magnifying glass and add your values one at a time and then click OK.

What is a condition type within the criteria tab?

It is the condition to show how your two expressions are linked.

For example: Table A information is “equal to” Table B’s information – there is a relationship.

How do I reorder criteria?

Click the Criteria Tab then click the reorder criteria button. On the left hand side place the number of the desired spot you wish for your criteria to appear. Then click OK.

How do I delete criteria?

Click the criteria tab you may delete criteria by clicking the minus button, in the delete column, on the right hand side of your listed criteria.

How do I create a prompt? And how do I use this prompt in my criteria?