×
Tips and Techniques

Training PS/Query to do new tricks…

By Larry Grey • July 29, 2005

Yesterday, I created a posting that discussed this for PS/nVision. Today, we’ll talk about PS/Query.

Tricks… Tricks… What tricks?
Well, actually, the sky’s the limit. Many of the most common tricks are the following:

 

  1. Creating a wizard to simplify the user interface.
  2. Extending the delivered security.
  3. Change the behavior of the query UI.
  4. Adding new restrictions to the way that queries are built.
  5. Using queries to select data and use it in new ways (replace inquiry pages, use in batch programs, where users can define SQL).

Wow! How is this possible?

Well, Mr. Plant, thanks for asking. It all comes down to how PS/Query is architected.

In its initial incarnation, PS/Query was a C program. When Windows went from 16 to 32 bit, I was responsible for taking the old query rewrite the user interface (also using standard MFC controls). What came out of that effort was the windows-based PS/Query that you saw in release 7 and still see today if your use PSQED.EXE. That version had a lot of the things you would expect in a query design tool: a pane where the objects to build the query from exists, a pane that was the workspace for designing the query, and drag and drop and popup menus for building the query.

When we went from the windows client to the Internet Architecture, my initial plan was to take a similar approach as in the past. Develop the user interface in C/C++. Although some people were telling me to develop the user interface as a standard application page, I had concerns that PIA was not up to challenge of a query tool. PIA did not have (and still doesn’t have) drag and drop or popup menus. However, I also didn’t have enough people to rebuild the query design environment in C/C++.

Therefore, when release 8 came out, there was no query designer in the browser. The next release after that consumed me (and my team) with EPM-related requirements. Thus, the rewrite of PS/Query was handed to somebody else.

One of the first decisions the team made was to build the user interface in PeopleSoft application designer. They would build APIs around all the Query function calls and expose them in PeopleCode. This means that the complex rules for applying security, finding records, joining tables, etc. are all encapsulated in these APIs. This is exactly the promise that a service oriented architecture provides.

However, this team didn’t have enough time to complete the tasks in a single release, either. They feverishly built APIs and then UI to show it as quickly as possible, and PS/Query in PeopleTools 8.13 was the result (which I’m still apologizing for). After that release, I took PS/Query back and had developers flush out the APIs and the UI over subsequent releases, so that eventually it was functionally equivalent to the windows version.

So, at this point, PeopleSoft customers have something that is both more powerful (and less powerful) than the previous version. It’s more powerful, because you can harness the Query APIs to do anything you want to (except to break the security rules invoked in PS/Query). It’s less powerful, because it does not have drag and drop, popup menus, or a multi-pane design UI.

So, how do I leverage this power?

Another excellent question! There are three primary ways you can leverage this:

  1. By modifying the PS/Query UI code.
  2. By building batch programs to modify queries.
  3. By embedding queries into your application.
  4. By building your own program to generate queries and/or run them

Again, keep in mind that the PS/Query UI is merely a set of PIA pages and PeopleCode, just like any other page (although a little more complex). Just as you can customize the vendor page, you can customize PS/Query. You can either modify PS/Query directly, or clone it. You can also create brand new programs, such as a wizard (this is what was done by the HRMS development group with their Query Builder, which simplifies query creation).

Modifying PS/Query UI code

A few things that I’ve seen customers do in this area is the following:

  • Modifying the search dialogs to extend the delivered security, or to make the list of queries more managable to business users.
  • Extend the PS/Query UI to track additional data.
  • Remove options that you don’t want people to use.
  • Streamline the query building process based on standards used by a company (by automating some of the tasks and restricting what can be chosen).
  • Automatically extend queries with links to drill to other places.

Building batch programs in app engine

The most common use of this is to make a certain change to a mass set of queries. For example, let’s say that you decide to create a table that denormalizes two tables that have a parent/child relationship. You’re going to want to point existing queries to this new table. You can write an app engine program to accomplish this, using the query API. There are lots of other use cases for this, even on the reading side, where you write a program that merely runs an existing query (scheduled query is a good example of this), and then take the results and work with it. Because the query encapsulates the complexity of the SQL and also provides a layer of abstraction between the database tables and the progrem, you get a lot of benefits by using the query instead of writing the SQL by hand.

Embedding queries into your application.

The most simple means of accomplishing this is to embed the URL to run a query into your applciation. This was done by the PeopleTools security team to eliminate the need to build inquiry pages to allow users to inquire on what users/roles/permission lists/pages are related to each other. They built a query for each inquiry, and then built a page that had a hyperlink for each query.

Other examples are as follows:

  • The batch program example in the previous section.
  • Using a query to select data into a page to be used elsewhere. A good example is to use a query to select data into a rowset that is used to generate a chart.
  • Allowing a user to pick options in a page that automatically builds a query to be used elsewhere. This is similar to the next section, “wizard”, but can be applied in different ways. One example is to get around the limitation in PS/Query that you cannot have a runtime prompt that asks for a tree node. One customer created a crystal report and in the run control page, prompted for the tree node. Upon saving the values, the query was modified to select against the new tree node and the crystal report used that modified query.

Building a wizard

This may seem like a pretty big thing to do. In many circumstances, however, you will find that it’s actually very easy. This is because although it’s difficult to build a generic wizard that will work in all circumstances, it’s actually easy to build a wizard to meet a specific goal. In other words, if you wanted to built a wizard that created a query that allowed a call center analyst to search for cases different ways, that wizard should be relatively easy: the tables are pretty constant, and you also know how you organize your products, employees, etc. You can embed these standards into the wizard easily and make it simpler to build these queries.

Conclusion

Future postings will have more detail on some of the solutions listed above. I will also take any questions on specific approaches to problems you’re trying to solve (but beware, those approaches may show up in future postings to this blog). Feel free to email me at mailto:larry.grey@greysparling.com.

Labels:

Stay Updated

Request a Demo