Overview
This article aims to provide an overview of Query Viewer and what it can accomplish. Query Viewer is a reporting tool that allows users to pull and view information from the UGA Financial Management System (FMS) in a read-only capacity.
What is Possible with Query Viewer?
With Query Viewer users can:
- View the results of a query. No changes can be made to a query using Query Viewer, as this functionality is used specifically in Query Manager.
- Search for information using the Basic Search or Advanced Search, depending on how much they want to narrow the results.
- Run and view UGA-created public queries.
- Retrieve query results via HTML or Excel spreadsheet. The Excel spreadsheet can be downloaded for future viewing. These results can also be printed for hard-copy viewing.
- Schedule queries to be run and sent to certain users at a determined date and interval.
What is a Query?
Queries allow users to find data within the UGA Financial Management System. Public Queries can be run by any users with "View Only" roles assigned. When running a query, a user may not get any results. This could be because of security limitations. Not all users in the Financial Management System have access to the same data. Therefore, the query might not pull any valid results. For more information on how to use the Query Viewer tool, see the instructions below.
What Public Queries are Available to Me?
A list of all public queries and reports from other UGA reporting platforms (i.e. Data Warehouse, Tableau, etc.) can be found by visiting reports.uga.edu.
What is the Query Naming Convention?
Most public queries will follow the naming convention: UGA_(Module Initial)_(Descriptive Title). Searching for queries by module will dramatically narrow down the search results and allow users to find information more efficiently. Some queries will not have a module pre-fix associated with them. Because of this, users may see queries in the system that do not start with these pre-fixes.
| Prefix |
Module |
| UGA_AM_ |
Asset Management |
| UGA_AP_; UGA_VOUCHER_ |
Accounts Payable |
| UGA_GL_ |
General Ledger |
| UGA_KK_ |
Commitment Control |
| UGA_CA_; UGA_PAY_ |
Commitment Accounting |
| UGA_AR_ |
Accounts Receivable |
| UGA_BI_ |
Billing |
| UGA_TE_; UGA_EX_ |
Travel and Expense |
| UGA_PC_ |
Project Costing |
| UGA_PO_ |
Purchasing |
| UGA_GM_ |
Grants Management |
| UGA_USERS_; UGA_WF_ |
Security and Workflow |
 |
|
On the UGA Financials home page, see the Query Viewer tile.
Click on the Query Viewer tile.
|
 |
|
The Query Viewer landing page is displayed.
You can use the Basic Search functionality on this page by selecting information to Search By.
|
 |
|
Click the Search By drop down menu to view available options and enter the appropriate information into the begins with field.
|
 |
|
The Advanced Search hyperlink allows you to choose different search filters for queries.
Click Advanced Search.
|
|
|
|
With Advanced Search selected, users can add the Query Name, Description, Users Record Name, Uses Field Name, Access Group Name, Folder Name, and Owner, along with certain conditions (i.e. begins with, less than, contains, between, in). Different data will be returned depending on which condition is chosen for each set of criteria. For instance, the "contains" filter will return more search results.
To navigate back to the Basic Search, click on the Basic Search hyperlink.
Note(s): There should be no spaces in the names when searching for a pre-defined query. In queries, the underscore key is used as a ‘space’. It is also important to note that when users add more details and choose more words for a search, the more precise users have to be. Each additional word and piece of information is working to narrow the search the FMS database more thoroughly.
For example, if a user knows that a query begins with UGA_USERS that is a good place to start. Any additional words will narrow the search, and if the other words are unknown or guessed, the search might not show results for the query a user is looking for.
|
|
|
|
|
For this example, Basic Search is the better option for finding the appropriate query: UGA_USERS_EMPLID. If you know the beginning of the query, UGA_USERS here, entering information in the begins with field will return all related results.
Click the Search button.
|
|
|
|
|
The search results for UGA_USERS are displayed.
For this example, the UGA_USERS_EMPLID query is the appropriate query because Employee ID is the information needed from the query.
Locate the appropriate option in the Query results table.
|
|
|
|
Now that the query has been selected from the search results (UGA_USERS_EMPLID), there are a few ways to retrieve the results of the query.
Results can be opened with Run to HTML, Excel, and XML. Choose the option that is the most convenient delivery method for the individual.
|
|
|
|
|
For the Run To options:
- Run to HTML does not require opening another program because a new browser window will open to run and view query results.
- Run to Excel requires downloading to your computer and opening the file in Excel and allows for additional filter and sorting options.
Select the appropriate Run To link for the corresponding query.
Example shown: Run to HTML for the UGA_USERS_EMPLID query.
|
|
11. Step 11
|
|
| Some queries will have a prompt once users attempt to run and retrieve the query results. With the UGA_USERS_EMPLID, the query prompts the query runner to provide MyID, Last Name, and First Name. This helps to narrow a query that might pull in a large amount of results.
For fields requiring number values, wildcards can be used to narrow the search. The percentage symbol ( % ) is used as a wildcard for more than one character and the underscore ( _ ) is used for exactly one character.
Queries with required prompts, such as "To" and "From" dates, prevent the return of large data sets and the slowing of system performance. If a large query does need to be run, the best method should be to schedule the query, which is outlined in later in this article. You can add details to the query prompt to see what data populates when the query is run.
Add MyID, Last Name, and First Name to the query prompts.
NOTE: Because sensitive information would be shown, screenshots are not provided here.
|
|
|
|
|
From the Search Results page in Query Viewer, you can save queries to the My Favorite Queries section, which is below the search results.
Under the Add to Favorites column, click the Favorite hyperlink.
|
|
|
|
|
The UGA_USERS_EMPLID query has been added to the My Favorite Queries list.
To delete a query from their list of favorites, use the “ - ” button in the Remove column. To remove all queries, select the Clear Favorites List button.
NOTE: You cannot recover a favorites list after clearing your list.
|
|
|
|
|
For large queries, use the Schedule option to avoid system processing issues.
Click the Schedule link.
|
|
|
|
|
Before scheduling the query and deciding when and how often it will run, users must enter a Run Control ID for the query they selected. The Query name is grayed out with the query a user selects (UGA_USERS_EMPLID).
Example shown: Run Control ID is named EMPLID_SCHEDULED_QUERY_TEST.
Enter a name for the Run Control ID and click Add.
Note: A Run Control ID must be named by the user. Previous run controls will be saved after they are initially created.
|
|
 |
|
If required, a separate window will open, and criteria can be entered to narrow the results.
Enter information in the additional search fields as appropriate.
Click the OK button.
|
|
|
|
|
On the Schedule Query page, entered information is added to the Prompt Name and Value table.
Use Update Parameters to add additional parameters, if necessary.
Once parameters are set and edits are complete, click the OK button.
|
|
|
|
|
The Process Scheduled Request page is displayed. You can add the Time Zone, Run Date, Run Time, Type, Format, and Distribution. The Reset to Current Date/Time button is used to reset settings if needed.
Example shown:
- Time Zone (EST)
- Run Date (4/26/2023)
- Run Time (3:30PM)
Click the OK button when all information has been entered.
|
|
|
|
A confirmation window is displayed.
To see whether or not the process has run yet, navigate to the Process Monitor page in UGA Financials.
Click the Main Menu dropdown menu.
|
|
|
|
Select the following navigation path: Main Menu > PeopleTools > Process Scheduler > Process Monitor.
|
|
|
|
|
The Process Monitor page allows you to review the process Run Status and Distribution Status.
The Run Status will be "Queued" and the Distribution Status "N/A" until the process runs.
Click the Refresh button to update the Run Status.
NOTE: The statuses will not change until after the date and time set for the process to run.
|
|
|
|
|
Once the query runs, the new Run Status of "Success" and the Distribution Status of "Posted" are displayed.
|
|
|
|
|
To view the Process Detail within the Process Monitor tool,
click the Details link.
|
|
|
|
View the Process Detail page is displayed and contains:
- Process Information
- Run Information
- Date/Time Information
When finished viewing this information, click OK.
|
 |
|
The Process Monitor page is redisplayed.
Queries set up to run on a schedule can also be viewed in the Report Manager.
Click the Main Menu dropdown menu.
|
|
 |
|
To access the Report Manager use the following navigation: Main Menu > Reporting Tools > Report Manager.
|
|
 |
|
Users can view the Report, Report Description, Folder Name, Completion Date/Time, Report ID, and Process Instance on this page.
Click the Report link.
Example shown: Find employee ID (prompt).
|
|
|
|
The Report Details page is displayed with he Report, Distribution Details, File List, Distribute To, and other information related to the scheduled query.
|
|
|