Home / Educational Content / PeopleSoft / Beyond the Data: How to Build Advanced PeopleSoft Queries That Solve Business Problems

Beyond the Data: How to Build Advanced PeopleSoft Queries That Solve Business Problems

Building advanced PeopleSoft queries requires more than knowing which records to join or expressions to write. The most effective queries begin with a clear understanding of the business problem, the rules that govern the data, and the decisions the results need to support.

During RECONNECT Dive Deep 2026, Alina Longoria, Senior Application Lead at the University of Houston System, shared a practical framework for transforming business challenges into reliable PeopleSoft queries. Using a real-world financial aid reporting problem, Longoria demonstrated how functional analysts can define requirements, locate data, build complex calculations, and validate results before delivering a report to end users.

Her central message: The query is the output of the process, not where the process begins.

Start with the Business Problem, Not Query Manager

At the University of Houston System, which supports more than 74,000 students across its three universities, reporting requirements can be complicated. The team also continues to support a fourth institution during its transition to another university system, making reusable reporting solutions particularly valuable.

Longoria illustrated her approach through a financial aid scenario involving Pell Grant awards. Beginning with the 2024–2025 aid year, the Department of Education no longer provided the traditional Pell award chart that institutions had previously used to verify award amounts.

Without that familiar reference, financial aid staff needed another way to determine whether students were receiving the correct amounts and identify awards requiring adjustment.

The initial request seemed straightforward: Create a report showing students whose Pell awards need review. However, that request alone didn’t provide enough information to build an accurate query.

Before opening Query Manager, Longoria recommended asking several questions. Which students should be included? What determines the expected award? Which federal regulations apply? What constitutes an exception? What information does the reviewer need to take action?

These questions transform a general reporting request into functional specifications. In the Pell example, those specifications included the aid year, student population, award status, expected values, comparison logic, and exception criteria.

Find the Right PeopleSoft Records and Fields

Once the business requirements are established, the next challenge is identifying where the necessary data resides.

Longoria emphasized that knowing a field name doesn’t necessarily mean you’ve identified the correct source. Analysts must understand record relationships, keys, and effective-date requirements to avoid inaccurate results.

She demonstrated three approaches for investigating PeopleSoft data:

  • Data Models: Navigate to a PeopleSoft page through the Data Models menu to view its underlying records and fields, including field types and lengths.
  • Field Inspector: Use the Chrome Field Inspector extension to hover over fields on a PeopleSoft page and identify their associated records and field names.
  • Page-to-record queries: Users can create a query using delivered PeopleSoft tables and the ctrl+j function to find the records and fields associated with that page. UHS did this using the tables PSPNLFIELD, PSRECFIELD, and PSRECDEFN.

These tools help functional analysts connect information displayed in PeopleSoft to the underlying data structures needed for reporting.

Longoria particularly favored Data Models because they provide a convenient way to examine multiple records and their field definitions. During the session’s Q&A, she acknowledged that access may vary by environment and security configuration, making alternative discovery methods useful.

Building Advanced PeopleSoft Queries with Expressions and Business Logic

With the requirements defined and records identified, Longoria demonstrated how those pieces came together in the University of Houston’s Pell award validation query.

The completed query incorporated eight tables and multiple expressions to calculate expected awards, compare them against actual amounts, and identify potential discrepancies.

One technical challenge involved the Student Aid Index (SAI). Because SAI values can be negative, Longoria needed to account for those values when performing award calculations. She used expressions to treat negative SAI values as zero for the relevant calculations, preventing incorrect mathematical results.

The query also incorporated maximum and minimum award amounts, calculated semester-level awards, and determined differences between expected and actual values.

Additional fields helped reviewers evaluate whether students met other important financial aid requirements, including Pell Lifetime Eligibility Used (LEU), remaining eligibility, Satisfactory Academic Progress (SAP), and Return of Title IV Funds (R2T4) calculations.

Another complication was that the institutions used different item type numbers for Pell awards. Rather than hard-coding institution-specific item types, Longoria used a consistent Pell description and an uppercase conversion to make the selection criteria more broadly reusable.

This approach reflected an important design principle: Build queries around shared business rules whenever possible, rather than institution-specific values.

Longoria also identified a PeopleSoft Query limitation: Expressions cannot directly reference other calculated expressions. Consequently, some calculations required repeating earlier expression logic within subsequent expressions, creating more complex formulas.

While hard-coded award amounts were necessary for certain calculations, she generally recommended using prompts and flexible criteria wherever possible to reduce ongoing maintenance.

Build Incrementally to Identify Problems Early

One of Longoria’s most practical recommendations was to build advanced PeopleSoft queries one record at a time.

Instead of joining every table and troubleshooting the completed query, she starts with a base record, applies essential criteria, and tests the results before adding another record.

During her demonstration, she began with the student award disbursement record and limited the query to a single student, institution, and aid year.

The initial results included more award information than needed. After narrowing the criteria to Pell awards, two rows remained because the data represented different terms.

By temporarily displaying all available fields, Longoria could identify the term field as the reason for the additional row and refine the criteria accordingly.

This incremental approach makes it easier to identify which record, join, or filter introduces unexpected results.

She also recommended beginning with a small group of known records. For the Pell validation query, she initially tested just three students, checking each calculated value before expanding the query to the full population.

Testing a manageable dataset allows analysts to investigate discrepancies without sorting through thousands of results.

Validate Query Results with Business Owners

A query that runs successfully isn’t necessarily a query that produces correct information.

Longoria stressed that functional analysts may understand PeopleSoft records and calculations without being experts in every business regulation or operational process. That makes collaboration with business owners essential.

For the Pell validation query, validation involved reviewing known student examples, comparing calculated amounts against expected outcomes, and investigating missing or duplicate rows.

Unexpected results could indicate incorrect joins, missing aid-year criteria, improperly handled negative values, or business rules that needed refinement.

Even the decision to include students with zero-dollar awards required input from financial aid staff. Depending on the reporting objective, those students might represent important exceptions or records that should be excluded.

Longoria encouraged analysts to verify both inclusion and exclusion criteria, compare results against trusted data or manual calculations, and confirm that the final report gives users enough information to act.

After reviewing the results with business stakeholders and making adjustments, the team produced a working Pell award validation query that addressed its reporting gap.

Apply the Same Framework Across PeopleSoft

Although Longoria’s example focused on financial aid, the methodology extends to other PeopleSoft functional areas, including Student Records, Student Financials, and Admissions.

The framework is consistent: Define the business question, translate the rules into specifications, map the data, build the query incrementally, and validate the results with the people who understand the business process.

Longoria also encouraged analysts to develop their technical skills through experimentation. Saving copies of existing queries, testing unfamiliar expressions, and troubleshooting in a test environment can help build confidence with advanced functionality.

Ultimately, the goal isn’t simply to create more sophisticated PeopleSoft queries. It’s to produce accurate, reusable information that solves real operational problems.

By approaching reporting as a functional analysis exercise rather than immediately jumping into Query Manager, organizations can develop solutions that are easier to troubleshoot, more meaningful to users, and better aligned with business needs.

Want more?

Explore more content and resources to help you get the most from your PeopleSoft investment:

Not a Quest member yet? Join today and tap into the ultimate Oracle customer network.

 

Beyond the Data: How to Build Advanced PeopleSoft Queries That Solve Business Problems