"PeopleSoft Queries and Excel" survey results

posted Oct 26, 2017, 12:45 PM by Peyri Herrera
In August-September 2017, the SDCOE Customer Resource Center (CRC) surveyed districts and charter schools in San Diego County to collect responses to this guiding question: “Do you run PeopleSoft queries, export them to Excel, and then do something specific in Excel to manipulate the data?”

Thank you to the 37 users from 27 organizations who participated in the survey (# in parentheses is the number of users):
  1. Cajon Valley: (1)
  2. Carlsbad: (1)
  3. Chula Vista: (1)
  4. Coronado: (1)
  5. Encinitas: (1)
  6. Escondido Charters: (1)
  7. Escondido Elementary: (2)
  8. Escondido High: (2)
  9. Fallbrook Elementary: (1)
  10. Grossmont: (1)
  11. Guajome Park Acad & Learn Cntrs: (2)
  12. Jamul-Dulzura: (1)
  13. Julian Elementary: (1)
  14. Julian High: (1)
  15. La Mesa-Spring Valley: (2)
  16. Lakeside: (1)
  17. Lemon Grove: (1)
  18. Mountain Empire: (1)
  19. Oceanside: (3)
  20. Pacific View: (1)
  21. Poway: (2)
  22. Ramona: (2)
  23. San Marcos: (2)
  24. SDCOE: (2)
  25. SIATech Charter: (1)
  26. South Bay: (1)
  27. Vista: (1)
Users provided examples of how they use the following Excel functions and formulas: subtotaling, pivot tables, vlookups, macros, conditional formatting, mail merge (into Word), text-to-columns, concatenate, and other formulas.

What we did with the information from the survey:

Guide: Compiled all of the survey responses to create a guide: Excel & PeopleSoft Queries Guide v1.1.pdf (22 pp.).

Videos: Made 2 video tutorials to illustrate how to use various Excel functions with PeopleSoft data:
  • Excel and PeopleSoft Queries HCM Video (Rich Fayad, Jason Cohee). This 12-minute video demonstrates several Excel functions: pivot table, freeze panes, filters, subtotaling, remove duplicates, vlookup, and concatenate. Data from two HCM queries are used: M_TL_PAYABLE_TIME_AUDIT and M_PY_PAYCHECKS_BY_DIST.

  • Excel and PeopleSoft Queries FIN Video (Natalie Schuff). This video shows some ways to analyze the M_GL_DETAILS_PO query in Excel using filters, pivot tables, If and Left formulas. This query includes all details in Finance including AP payment information. Learn how to summarize the data very quickly with a few formulas and a pivot table. At about 7 min, 41 sec, see how to configure a pivot table to mimic a Trial Balance by Restricted and Unrestricted. 
Conference: Presented all of this information at the PeopleSoft User’s Conference “Excel & PeopleSoft” sessions. Here are the handouts: