Finance Query Manager for Districts
Presented by SDCOE CRC | April 20, 2017
T H I R D T H U R S D A Y D E M O
APRIL 2017
Overview
The April 2017 Third Thursday demo
is Finance Query Manager for Districts.
This demo is for districts and charter schools interested in allowing a limited number of employees to edit and create queries in PeopleSoft Finance.
TOPICS
1. What is Query Manager?
2. What are the “terms and conditions”?
3. What will I learn at training?
(DEMO)
4. How is it being rolled out for Finance?
5. Which organizations have
authorized users as of April 2017?
6. When is training offered?
7. What are the plans for rolling out
Query Manager for HCM?
What is Query Manager?
Query Viewer
Allows you to run queries created by others (M_ and FAR_)
All Finance users
Query Manager
Allows you to edit existing queries and create new ones using limited tables
A limited number of users per organization (approximately 3)
Special role:
M_QUERY_MANAGER_DISTRICT
Your organization has many Query Viewer users.
Your organization can have a few Query Manager users.
Query Manager
1. Search 2. View results 3. Edit a query 4. Run a query 5. Schedule a query 6. Access favorite queries
Each Query Manager user is identified by your organization’s PeopleSoft contacts
Training is required before the role is granted
M_QUERY_MANAGER_
DISTRICT role
Log in to PeopleSoft Finance using a unique
“Q” login specifically for managing queries
Not able to access Query Manager with usual login
User adheres to the Terms & Conditions
NOTES
Terms & Conditions
1. In order to be a user of Query Manager you must have
experience with Excel and database structure (tables, fields, criteria, joins, etc.).
2. In order to be assigned the M_QUERY_MANAGER_DISTRICT role that grants access to Query Manager, you must be trained in the use of Query Manager by the SDCOE Customer Resource Center and/or cross-trained by another staff member within your organization.
3. You will not have permissions to alter an existing public query (M_ or FAR_ for example).
4. You may find an existing public query (M_ or FAR_ for example) and/or create a new query and save it as your own private query.
You may share your private query with other users (employees)
within your organization.
Terms & Conditions
5. Not all queries available to you under your regular ID are available under your “Q” Query ID.
6. You may schedule a private query to run daily/weekly. Do not schedule a query to run hourly.
7. You may schedule a private query to distribute to other users or email addresses.
8. Use Query Manager at your own risk. It is recommended that you incorporate a process to test and validate a query to confirm that the returned results are accurate.
9. If you create and/or schedule a query that impacts PeopleSoft
system performance, the SDCOE reserves the right to remove
your access to Query Manager.
Terms & Conditions
10. The SDCOE will not support or troubleshoot a query that you or any district user creates. Only M_ and FAR_ queries are
supported and maintained.
11. There are two reasons to submit a HEAT ticket regarding Query Manager:
– Primary contact: To request that a user is given the
M_QUERY_MANAGER_DISTRICT role with a confirmation that the user has received training (see Item 2).
– To request that the CRC uses the Query Administration tool to
end a query that is not running to success.
Sample PeopleSoft Finance query
What will I learn in training?
1. Records: Allows you to select the records to use in the query. At least one record must be selected before the query can be saved.
2. Query: Used to select the fields you need for the query. At least one field must be selected before the query can be run and saved.
3. Expressions: To create expressions and use them as fields in your query (not covered in training). Example: To concatenate fields.
4. Prompts: To create runtime prompts.
5. Fields: View, edit, sort, reorder and remove fields that appear in the query.
6. Criteria: Apply criteria to filter query results.
7. Having: Add criteria to fields that use
aggregate functions - Max, Min, Sum, etc. (not covered in training).
8. Transformations: Add XSLT - Extensible Stylesheet Language Transformations (not covered in training).
9. View SQL: Displays the SQL that is generated when the query is created.
10. Run: Executes the query and displays results.
QY2 Training Objectives
Use Query Manager to locate M_ and FAR_
queries created by the MITI/CRC project team and SDCOE Financial Accounting
Edit Finance queries:
– Add a field from an existing record – Add a prompt
– Format the query to return the data set how you please
Create a new Finance query
Rename and save queries as favorites
Share a query
B Y T H E E N D O F T H E S E S S I O N , Y O U W I L L B E A B L E T O :
DEMO
How to use Query Manager to edit a query.
WE WILL:
Log in to Finance demo
environment as User 603473Q.
Find
QM_AP_VOUCHERS_POSTED query and Save As.
Edit the query to:
Add a field
Order the fields as desired
Sort the results
Rename the field
Test/run the query.
Copy the query to Users 506993 and 122105.
Scenario: Employee 603473 (Ron) at District 007 has access to Query Manager. A staff member has requested that he modifies an existing public Accounts Payable query to include a new field.
Ron will log in to PeopleSoft Finance with his “Q” ID. He will first run the query to see how many records return and identify where/how the new field should appear. Then he will modify the query and test it. This query will be his own private version (separate from the original public query) and he can share it with other users.
How is Query Manager being rolled
out for Finance?
Preliminary
Cardiff Dehesa (1)
Jamul-Dulzura (1) Julian Elementary (1) Julian High
San Pasqual (2) Spencer Valley Warner
Vallecitos
Group 1
Coronado (2) Lakeside
La Mesa-Spring Valley (4) SDCOE*
*Some SDCOE employees have a different level of QM access.
Group 2
San Dieguito Carlsbad (3) Encinitas (3)
Fallbrook Elementary Fallbrook High
Borrego Springs
Group 3
Escondido Elementary (2) Escondido High (1)
Poway (4)
San Marcos (5) Vista (3)
Group 4
Bonsall (3) Del Mar (2)
Guajome Park Academy Guajome Learning Centers Julian Charter
Lemon Grove National (2) Oceanside (4) Ramona (2)
Rancho Santa Fe (1) San Ysidro
Santee (2)
SIATech Charter (3) Solana Beach
Valley Center-Pauma
Group 5
Alpine
American Heritage Charter (2) Escondido Charter (2)
Heritage K-8 Charter (3) Literacy First Charter Mountain Empire Unified Pacific View Charter
Organizations with authorized Query Manager users as of April 2017
54 users from 24 organizations have been trained
by the CRC
and can create
Finance queries.
Training Dates for 2017
Query Manager Training (4x/year)
You must have approval from your organization’s PeopleSoft contact prior to registering.
• Spring: March 2017
• 4 initial sessions were offered in March
• Summer: June 2017
• Fri, 6/16/17 from 9:30-12:30 at SDCOE
• Fall: September 2017
• Tue, 9/12/17 from 9:30-12:30 at SDCOE
• Winter: January 2018
• Wed, 1/24/18 from 9:30-12:30 at SDCOE
Query Manager Workshops (2x/year)
These workshops are for employees who have been trained and are currently assigned the Query Manager role.
