Using Drilldown URL Links in PSQuery

This nifty trick basically allows you to create hyperlinks on a PSQuery result.

Very useful for those drilldown reports where you click a link and it brings you further down the rabbit hole to more detailed information.

In this case, we will create 2 queries and try to link them using Drilldown URL.

PeopleCode: How To Remove Tags With Blank Values From XML File

Wish there was an easy way of parsing an XML file using PeopleCode without having to write all these loops.
Here's a quick function that removes xml tags with no value (blank, nulls.. zeroes excluded).

Function onReadyState Error: setPSTouchHandlerDoc is not defined

How to Fix onReadyState error caused by setPSTouchHandlerDoc function

Function onReadyState Error: ptGridObj_win0.initGriddd854 is not a function

How to Fix onReadyState error caused by initGriddd854 function

Using XML with BI Publisher

Here's a quick guide for this deadly combo:
XML, BI Publisher, and Application Engine.

We start by importing the delivered Application Package.

import PSXP_RPTDEFNMANAGER:*;

The following function is optional if

Using Java's SLEEP in Application Engine

Let's say you scheduled several nVision instances using your AE and you want to send an email right after everything ran to Success.

How do you force your AE to wait?

You can use this piece of code.

Scheduling nVision through AE

For obvious reasons, I cannot possible document the entire AE in this blogpost so I'll just give a quick rundown.

nVision is scheduled through a series of setup pages under this navigation
Main Menu > Reporting Tools > PS/nVision

Behind the curtains, PeopleSoft is inserting data to the following tables

• PSNVSBOOK
• NVS_SCOPE
• NVS_SCOPE_FIELD
• NVS_SCOPE_VALUE
• NVS_REPORT
• PTNVSQRYPROMPTS
• PSNVSBOOKREQUST

Which means, in order to successfully schedule an nVision instance, you just need to

Add "Download All" Button to Report Manager

I have always wondered why PeopleSoft never included a "Download All" button in all their releases for Report Manager.

Of course, messing with delivered objects is generally frowned upon.
But if you really wanted to implement one, here's a sample.

Excel Macro for SaveAs Filename

Haven't posted much about Excel but this one has been the most common request from business users.

This is a macro to override the "Save As" filename.

REGEXP_LIKE To Find Unwanted Characters

This SQL will search for filenames that contain characters other than

1. a-z
2. A-Z
3. 0-9
4. underscore
5. parenthesis
6. dash
7. period
8. square brackets

Find Temp Tables used by Application Engine

I've been running into situations where our application engines are not clearing the temp tables even after process instance has been deleted.

Also, multiple occurrences of the process halting for no reason.

Oracle's workaround is to clear the temp tables and restart the process.

But which temp tables do you clear?

Try this script.