How to trick PSQuery to use LIKE and UPPER

Client wants a PSOPRDEFN query that prompts user for OPRID...

Something that still works even if the user tYpEs LikE aN IdiOT or uses a wildcard character (%).

Ok, so your first instinct would be to create a new expression.

But then you realize, you can't use expressions if condition is LIKE.

Click Read More to see how to trick PSQuery to use LIKE and UPPER at the same time...

Lazy IF statement for Variable Assignment

Five-line code

If &Color = "Blue" And &Pet = "Dog" Then
  &Flag = True;
Else
  &Flag = False;
End-If;

can be converted to one line

&Flag = (&Color = "Blue" And &Pet = "Dog");

&Flag is TRUE if both conditions are true.

False otherwise.

Help! Excel VLOOKUP is so DAMN SLOW!

Warning: This post assumes you know the basics of Excel VLOOKUP.

By default, Excel automatically re-calculates formulas in the spreadsheet.

Problem is if you have a VLOOKUP against 100k+ rows, it's gonna be a long day for you at the office.

Here's how to fix that.

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.