Student Faculty Information System

Now available on the SFIS Blog – PeopleSoft Query Advanced Concepts documentation.

This on-line instruction follows up on the training presented in the Introduction to PS Query course.  It reviews and reinforces a number of topics previously presented and introduces additional subjects such as aggregate functions, having, outer joins, subqueries and union queries.   You can review the material at your own convenience and observe some of the demonstrated queries in Query Manager.

Please feel free to contact me with any questions about these subjects or if you have a specific query you would like to discuss.

The topics covered are

Tags: Criteria, Demonstration, Expression, Outer Join, Prompts, Query - Advanced, Subquery, Summarizing, Table Ordering, Union

• You’re working with PeopleSoft Query and would like to display the Wesleyan Class Year for each student without having to add in another table.
• Or the Query that you’re updating displays the student name as Last Name, First Name.  You would like to show it as First Name then Last Name.
• Or you need to show students’ country of citizenship, spelled out, in your Query output.

How do you go about presenting this information in your query?

There is a tool available for use with SFIS that can provide help with these examples and many others – the Wes Function or SFIS/HRMS Function.

What is a Function?

Since the term “function” as used with PeoplesSoft Query can have a number of definitions, to differentiate this use of the term, we refer to it as a Wes Function.  A Wes Function is a method created by the Wesleyan ITS staff that enables the PeopleSoft user to exploit the benefit that Expressions can lend to a query.   It is pre-written text created specifically for inclusion in Query Expressions for Wesleyan users.

What are Expressions and how and why would you want to use them?

• Expressions are calculations that PeopleSoft Query performs as part of a query.  They are created on the Expressions Page.  Use them when you must calculate a value that PeopleSoft Query does not provide by default.  The expression becomes an additional field.
• An expression can be used like a field.  If you use an expression as a field, the expression can be used like any other field in a query.  When you preview the query, the expression name appears as a column heading in the query.  When selected for output, you can change its column heading or sort it.
• Normally, data that is the result of a calculation is produced when the query is run in real time.
• You use expressions to display a field value differently from the way you store the value.   Examples would be an expression that displays the appearance of a date as July 4, 2010, or an expression that inserts a space or hyphen in a field, or an expression that displays characters all in upper case.

Click here to see a demonstration of building an Expression with the Wes Function WES_GET_CLASS_YEAR.

The article  SQL EXAMPLE with FUNCTION and Formatting of Date displays various date formats that you may want to use in an Expression.   Also demonstrated are the use of concatenation with a date and the use of Name Parts.   Displayed are the output and the associated SQL.

You can observe Wes (SFIS/HRMS) Functions in a chart format.

Other examples of using Expressions can be found under Query Demonstrations.

And to see a list of Expressions, click here.

Tags: Demonstration, Expression, FAQs, Query - Advanced, Wes Function

The  SFIS FAQs page has only a few questions now, but as time goes on, it will be a quick resource for your most common questions. 

If you have any questions that you think should be added, please send them along to Carolyn Pike at cpike01@wesleyan.edu

Tags: FAQs

Since the introduction of the SFIS Blog last week, there have been three additions to the Query section that you should know about:

 First, there is a Query Tip Sheet that has hints, suggestions, and ideas.  Among the topics discussed are joins, table ordering and criteria.  This page will be added to as needed. 

 Second, there is a page entitled Using Microsoft Excel and Word with Query which gives you some information about instructional resources for these tools that are used with data derived from Query.  

 Third – related to the previous item – is Query to Excel (Pivot Table Intro) which is a brief demonstration of the utility of Excel Pivot Table with typical SFIS data. 

Tags: Criteria, Microsoft Excel, Microsoft Word, Query - Advanced, Query Joins, Summarizing, Table Ordering, Translate Values