Running PeopleSoft queries from Excel links

Quick

This approach is useful if you have specific queries that you run to populate Excel workbooks.
It saves a bit of time by cutting out the navigation through PeopleSoft and searching for the required query.
The results still need to be copied into your Excel report template.

1. Navigate to Reporting tools - Query - Query viewer
2. Search for the Query you want to run and click on the Excel hyperlink.
3. When the new query window opens, copy the link in the address bar.
4. In Excel, click on the Insert menu tab and select the Link icon.
5. Paste the copied URL into the Address field and click on OK.
6. Clicking on the link when you are logged into Peoplesoft with your default browser, will immediately run the query and download the results into a new Excel workbook.

If the query has parameters, you will be prompted for the parameters.

The results of the query can be copied into your report template.

Running queries from Excel Hyperlinks

This is a great option for users that have reports in Excel that are based on PeopleSoft query results.
If you have a lot of queries it is easy to lose track of which ones were used to populate the Excel workbook report.
Using this approach, will create a link to the query and the parameters last used.

Running the query is done by clicking on the link.
If you are logged into PeopleSoft, a new tab will be opened and the query run.
If you are not logged into PeopleSoft, the logon screen will be displayed first.

Structure of a PeopleSoft URL

First though, how is a PeopleSoft URL structured.

https://server/servletname/sitename/portalname/nodename/contenttype/

Server: the basic url
Servlet: psp = Show portal ‘Chrome’ or psc = only show content
Site: the application
Portal: the registry, normally EMPLOYEE
Node: the node, normally ERP
Content Type: q for query

Formula in Excel

The formula in Excel uses the HYPERLINK function and would have an action and query name added to the URL.

=HYPERLINK("https://server/servletname/sitename/portalname/nodename/contenttype/?ICAction=ICQryNameURL=PUBLIC.ALL_BUSINESS_UNITS","Run to HTML")

And if the query has parameters that you want to prefill rather than prompt for, they are added after the query name.

=HYPERLINK("https://server/servletname/sitename/portalname/nodename/contenttype/?ICAction=ICQryNameURL=PUBLIC.ALL_DEPARTMENTS_BY_SETID&BIND1=SHARE","Run to HTML")

To make the links dynamic and easier to maintain, the URL can be broken down into sections.
The URL sections are combined using the CONCAT function.
This makes testing and updates easier.

=HYPERLINK(CONCAT($A$2,$E$2,$A7),"Run to HTML")

Output

The query can be run to HTML (Screen) or to Excel by changing the action in the URL.

The action code for HTML is:

ICAction=ICQryNameURL

The action code for Excel is:

?ICAction=ICQryNameExcel

Parameters

Parameters can be passed to the query by adding the following code

&BIND1=

The name used, is stored in the Unique Prompt Name. It can be found via Reporting Tools - Query Manager - [Prompts] - [Edit].

There is a gotcha here. If the Query has been modified over time, then the BIND numbering might not match the order of the parameters. Also the name used might not be BIND.

Parameters using wildcards and dates

The percentage (%) wildcard can be passed into the URL with no changes.

Dates need to be formatted as Year-Month-Day.
The can be done be entering the value as a text value into the cell and then referencing it the HYPERLINK formula.

A better method is to enter the date into the cell as usual. Then use the TEXT function to convert it to the required format.

The formula tp vcovert the date to the required format would look like this.

=TEXT(A1,"yyyy-mm-dd")

The full formula would look like this.

=HYPERLINK(CONCAT($A$22,$D$22,$A5,"&BIND1=",B8,"&BIND2=",B9,"&BIND3=",TEXT(B10,"yyyy-mm-dd"),"&BIND4=",TEXT(B11,"yyyy-mm-dd")),"Run to HTML")