This article provides suggestions on where you can find data to write queries on in PeopleSoft®.
Detailed Information
Arguably the most difficult part of writing a query is just trying to find the data you want to query on. There are several options.
UM Index of Reporting Tables
INFO: In order to see the TDX category, you must be logged in with your UMS login information.
Do you have an idea of what you're looking for, or are you just feeling brave? Click on UMS Index of Reporting Tables to go to our ever-growing list of tables showing all fields and key fields in each table that we have documented.
This highly rated, handy Chrome add-on provides added functionality within PeopleSoft MaineStreet. In addition to a Ctrl+J feature (see below), you can create Global Favorites or try their Field Inspector.
Look up the Data using Ctrl+Shift+J
When you are in the CSRPT, CSTST, CSSBX, FNTST, or HRTST databases, there is a ‘hidden’ keystroke that gives you diagnostic information about PeopleSoft, including some basic information about the underlying data. First, you need to navigate to a screen in PeopleSoft in one of these test databases that is the same as the actual screen you are using in the working database. Test Databases are linked on your MyCampus Portal under the "MaineStreet - Test Databases" section.
When you get to the data page, press the Ctrl and Shift, and J keys at once.
The example below is the information shown on the Enrollment Summary page in CSSBX. (Records and Enrollment > Enrollment Summaries > Enrollment Summary) It shows us that the majority of the data on this screen is held in STDNT_ENRL_INQ.
Using Query
DARTS team created a query called UMS_DTS_FIND_PAGE_DATA to find the list of tables and field names on a particular page of information. Run the query and enter the page name found on the Ctrl +Shift + J page on the prompt. (It is STDNT_ENRL_INQ in this example.) The results are shown below.
Note that this method isn’t perfect; it only gives us one location where the data is stored, not necessarily the best (or most convenient) record with the data. However, because we have the field names, we can search for records that contain that field and see if any are on our familiar list.
Environment
- PeopleSoft Campus Solutions (CSPRD)
- PeopleSoft Campus Solutions Reporting (CSRPT)
- PeopleSoft Human Resources (HRPRD)
- PeopleSoft Financials (FNPRD)