Accessing PS Queries outside Query Manager and Query Viewer

The PS Query toolset provides the easiest mechanism to create and generate simple reports from PeopleSoft. While PS Queries cannot be used to produce highly formatted and graphical reports, it is very effective in extracting day-to-day enquiry queries which is of interest to employees, managers and administrators. Considering this, it is a bit surprising that most customers deploy this tool only for the use of administrators. One of the main reasons for the restricted use of PS Queries is that most customers are reluctant to let managers run a PS Query using the Query Manager/Viewer pages. Query Manager and Query Viewer are the standard options available in PeopleSoft to run PS Queries and there are indeed usability issues in opening up these pages to the end user community. But, the fact remains that there are easy alternatives to Query Manager and Query viewer in PeopleSoft, which can be effectively used to enable end users utilise PS Query based reporting. In this post, we take a high level tour of the methods that can be used to access PS Queries without using Query Manager or Query Viewer.

(a) Accessing PS Query as a navigation link (content reference): Managers/Employees are most familiar with navigating through the folder structure of PeopleSoft and accessing information in as minimum number of clicks as possible. So, what if your managers/employees could access relevant PS Queries just like any other page in PeopleSoft, just by clicking a navigation link? This can be achieved by creating a PS Query as just another content reference under the navigation structure that you desire. Once you create a query as a content reference, you could very well add that in a navigation collection as well, to improve the usability experience.
Here are the steps to create a PS Query as a content reference:

• Navigate to Peopletools > Portal > Structure and Content and create a new content reference definition.
• Select the URL Type as PeopleSoft Generic URL
• Enter the portal URL as q/?ICAction=ICQryNameURL={Query Name}
• Save the content reference definition and you are good to go!

By following the above steps, you will be able to expose a PS Query as just another navigation link. On clicking the link, users will directly be shown the query output (if there are no prompts for the query), bypassing query viewer/manager. The traditional query security is also skipped in this method, which is another plus.

(b) Accessing PS Query as a hyperlink on a page: I am sure that most of you have noticed certain pages in PeopleSoft which contains hyperlinks to PS Queries. This is most common in the security configuration side where there are pages with links to queries that display the roles a userid has, the pages that a certain userid can access etc. Accessing PS Query as a hyperlink is another alternative to using Query Manager. On clicking the hyperlink, the query is automatically executed, bypassing query manager and even query security. Implementing this is not as straightforward as the first option, as it will be required to do some simple peoplecoding and application designer changes to achieve this. The steps that can be done to expose a PS Query as a hyperlink is detailed below:

• Add a hyperlink object in the page where you want the query to be accessed.
• Put the destination properties of the hyperlink as a dynamic external link.
• Instantiate the value of the hyperlink to the URL of the relevant PS Query. You can get the URL of a PS Query using the CreateQueryURL peoplecode function.

The above approach is useful when you want users to directly run a PS Query from a transactional page. The downside of this method in comparison to the option of creating PS Query as a content reference is that this method will require some customisation.

(c) Creating PS Query as a Related Content: Related Content is an excellent feature introduced in Peopletools 8.50 which revolutionised the navigational capabilities within PeopleSoft. You can read more of related content and it's capabilities here and here. One of the features offered by the related content framework is that a PS Query can be configured as a related content definition. This means that using the related content framework, a PS Query that is related to a certain transaction page, can be made to appear as a related content definition. This means that your users will see the results of a ps query displayed directly within the context of a related transaction page. Given below is the setup page where a PS Query can be defined as a related content service.

 

(d) Accessing PS Query results using Feed Framework: Peopletools 8.50 introduced another exciting feature called 'Feed Publishing Framework', which enabled PeopleSoft customers to generate RSS feeds from PeopleSoft datasources. Using this framework, it is possible for users to consume information from PeopleSoft using a traditional Feed Reader/Aggregator or using the My Feeds page in PeopleSoft. This opened up yet another way to consume the results of PS Queries. The Feed Publishing framework allows users to publish a PS Query as an RSS feed, which can be consumed by end users.

 

From the above alternatives, it is obvious that PeopleSoft presents very rich alternatives to Query Manager/Viewer for accessing and executing PS Queries. These methods can be utilised to present PS Query based reporting to the end user community in a user-friendly manner, with minimal customisation.

Post Note: The above is not a complete list of the alternatives presented in PeopleSoft for running PS Queries. There are certain options which are more appropriate for administrator type users, which have not been documented above.

• The first among the options is Reporting Console (Main Menu > Reporting Tools > Reporting Console). Reporting Console is an excellent utility to organise various reports and processes based on functionality and is a must have for the administrator community.

• Another interesting alternative is the use of XMLLink registry function to access PS Queries directly from a MS Excel workbook. This is documented in detail here and goes a step further to improve the usability experience of the administrator community.

•  It is possible for external applications to create or invoke a PS Query in PeopleSoft using web services. The Query Access Service toolset provides a number of service operations that can be utilised by third parties to create or execute PS Queries.

• I had developed a bolt-on framework to organise and run PS Queries by functionality and designed to work outside the Query Manager/Viewer tools. This was developed to address the need to classify specific PS Queries that were routinely run to audit payroll results every bi-week. More details are documented here.

 If there are other methods to access PS Queries that you are aware of, please feel free to leave a comment and I will update this post accordingly.