Eliminate Extra Blank Rows in XMLP Reports

A recent XML Publisher report of mine printed a blank page at the end of the report anytime the final page was close to full. The culprit was extra blank spacer lines that were being added even though the content the lines surrounded was not included from the report.

Here's what the report looked like:

...content...
{blank spacer line}
{template call for conditional data}

The result was that the blank line was added even when the conditional data was not. If a report has multiple conditional sections, this can cause gaps in the output. As in my case, if those conditional sections are at the end, it can cause a blank page.

The solution is to move the spacer line into the template. That way it is only included if the conditional data is included.

Your report now looks like this:

...content...
{template call for conditional data}

And the template looks like this:

{begin template}
{blank spacer line}
...content...
{end template}

 

This has the added benefit of including the spacing for each instance of the data, in case there are multiple instances of the conditional data.

One last note on extra lines: Beware a space. If a line in your RTF has a field form that does not generate output (such as an if statement), it will not generate a line on the output. However, if there's a space before or after the form field it WILL generate a line.

Built-in Function BulkInsertField

It's always a treat to come across a function you haven't used before, especially when it does exactly what you need. While reviewing chartfield configuration during an upgrade, I came across the delivered function BulkInsertField. I found it in the chartfield configuration app engine FS_CFUPDATE and it came in handy.

I'll leave the details to PeopleBooks, but essentially it allows you to add a field to pages and records. You supply it with two fields: the field to add and the field that you want to use as your model (what record should it come from, what should the page field properties be). The insert works especially well with grids. The function runs over a project, so you get great control of which pages / records are updated.

With a one-step app engine of my own, I was able to quickly add three fields to a number of pages and record. It was the perfect tool for the job.

Spicing Up Your Vanilla With a Custom Look & Feel

Last week at OHUG 2010, I presented a case for customizing the user interface of PeopleSoft applications. Sherry Rogers, HRIS Manager at Cerner Corporation, was generous enough to join me, demonstrating the custom look and feel we developed for Cerner's eBenefits module (screenshots of the demonstration) and detailing the reactions and results. Here's the slide deck:


Improving the usability of PeopleSoft applications is less effort then you might think, and the savings from increased employee productivity, fewer calls to the help desk, and reduced training are often substantial. Improved usability can be especially crucial for outward facing applications like recruiting; the last thing you want is a talented applicant's first impression of your organization to be a clunky and confusing web app. And while it's commonly believed that straying too far from vanilla puts you at risk, making the right technical design choices will dramatically reduce your exposure at upgrade time.

Have you encountered problems with PeopleSoft's usability? What have you done about it, or what are your plans? Let us know. 

Local Sub-Templates for XML Publisher

When building an XML Publisher report, I spend most of my time working with the Word Add On. Since everything is local, it's easy to make a change to the report then test it with the Preview feature. However, if the report includes sub-templates, then there's a small difference in the reference to the sub-template.

Here's what the reference to a sub-template looks like when it's stored on the server:

    <?import:psxmlp://ROSO_SUB_TEMPLATE?>

Here's what the local version looks like:

    <?import: file:///C:/temp/MySubTemplate.rtf?>

Note that the stored reference uses the Sub Template Id, while the local reference just uses the file name.

Generate Excel Spreadsheet from PeopleCode

PeopleBooks documents the built-in function GenerateQueryContentURL. It's a helpful function that lets you create a link to a query.

If you append the query criteria as URL parameters, you can bring the query up without prompting the user. This is handy when you've got a data page that contains your criteria—the link allows you to show more details to the user.

When you combine it with another PeopleTools function, ViewContentURL, you can launch a new window with the results of the query. It's a nice way to provide the user with additional data without derailing what they are currently doing.

It gets really interesting when you go off-PeopleBooks and use GenerateQueryContentURL's undocumented cousin: GenerateQueryContentExcelUrl. It takes the same parameters (and also can have the parameters appended to it).

Put it all together and you can launch an Excel spreadsheet with a single line of PeopleCode:

ViewContentURL(GenerateQueryContentURL(%Portal, %Node, "QUERY_NAME"
, True, False) | "&BIND1=US001&BIND2=123456789");

Checking Query Security via SQL

Query security always seems to be an issue: "I can't see the query!"

Here are a couple of SQL statements to review the setup. The last one tells you why a user can't see a query.

-- List of records available to query
SELECT * FROM PSQRYACCLSTRECS
WHERE CLASSID = 'ROSO_PERMISSION_LIST_NAME'
ORDER BY RECNAME;

-- List of Query Tree Access Groups
SELECT * FROM PS_SCRTY_ACC_GRP
WHERE CLASSID = 'ROSO_PERMISSION_LIST_NAME';

-- Records without security
SELECT * FROM PSQRYRECORD
WHERE QRYNAME = 'ROSO_QUERY_NAME'
AND RECNAME NOT IN (
SELECT DISTINCT RECNAME FROM PSQRYACCLSTRECS
WHERE CLASSID = 'ROSO_PERMISSION_LIST_NAME');

Rowset Manual Sort

The Rowset Sort method allows you to sort in either ascending or descending values based upon one or more fields. However, occasionally there is a need to sort in a non-linear way. For example, if a field includes the values "First", "Second", "Third", and "Fourth", the sort order is obvious to us, but not to the Sort method.

One route to do this type of sorting is to add an additional field. Assign that field values that will sort correctly. Each row with the value "First" gets a 1, each row with the value "Second" gets a 2... Then call the sort method and pass the new field in.

Why not build your own custom sort method? We can do so by leveraging the fact that PeopleTools drops rows that are not visible to the bottom of the rowset. It's a trick that generally trips everyone up at least once. It's also why when you are hiding rows that you need to go from the bottom back to the top.

Here's some psuedo-code that shows how you might build a custom sort method:

method Sort(&data as rowset, &order as array of string, &recordName as string, &fieldName as string)

    /* start by making all rows not visible */
    &data.HideAllRows();

    /* loop through data, looking for ordering values */
    local integer &i, &j;
local field &f;

    for &i = 1 to &order.len
        local string &orderValue = &order[&i];
       
        for &j = 1 to &data.ActiveRowCount
       
            /* does this row have the sort value? */
&f = &data(&j).GetRecord(@("Record." | &recordName)).GetField(@("Field." | &fieldName));
            if &f.Value = &orderValue Then
                &data(&j).Visible = True;
            end-if;
        end-for;
    end-for;

    /* in case any values were missing, they'll still be hidden */
    &data.ShowAllRows();
   
End-method;

You might assume from the use of the Visible property that this only works for buffer rowsets. However, it turns out that PeopleTools drops hidden rows to the bottom even on stand-alone rowsets. So this works for those, too.

Meta-SQL %InsertSelectWithLongs

While %InsertSelect is a great tool, it's always had one drawback: It leaves out long character fields. If the table you're inserting into has long fields that need to be populated, you need to find another solution.

Turns out there is another solution. It's not clear to me when it was introduced, but %InsertSelect has a partner in crime: %InsertSelectWithLongs. It does exactly what you think, it includes longs in the field lists.

It doesn't show up in the PeopleBooks for 8.48 or 8.49, though it does make an appearance in 8.50. I found it while googling. The folks over at Salibury University (the fightin' Sea Gulls, as it turns out) posted a PeopleTools 8.49 PeopleBooks Issues and Corrections doc which states that the handy meta-sql had been left out. I verified it works on PeopleTools 8.48.

Set Processing vs Complexity

There was a time when I was a fervent member of the Church of Set-Based Processing. I preached %InsertSelect and TAO tables, and I would brook no dissent. At the time I was writing app engines to bring data into Projects, and it's probably a fair bet to say that PROJ_RESOURCE is one of the largest tables in the Financials database.

On a recent project I had a requirement to allocate available quantity against incoming orders. I dutifully built an app engine that handled most of the work in a set, only dropping to a row-by-row loop at the end. It took a little debugging, I got turned around once or twice, but in the end it was fast and—if you don't mind me saying—somewhat clever.

But here's the thing: while the business analyst was congratulating me on how fast the process ran, he mentioned some of the rules he'd like to add in the future. (I know, new requirements. Who would have guessed?) I had architected for some flexibility, but had no room for the complexity he wanted to add. I could twist the clever knife a little deeper, but then woe be the developer that had to debug or add functionality down the road.

I wondered then if another tool might have served me better. Rather than temp tables and recursive DoSelect's, what if I had built logic with app classes? It's much clearer to write rules in PeopleCode than in recursive app engine steps with state records. And by their nature app classes are easier to extend.

When a similar requirement came up later in the project I did a little testing. I wrote some test code that created a set of objects in a PeopleCode event. They were very simple, bean-like objects that held keys and a few additional fields. I created 5,000. Then 10,000. Then 50,000. It took only seconds. The tests to update the table behind the objects performed similarly.

Writing business rules in PeopleCode is an order of magnitude easier. You can encapsulate complexity in classes and methods in a way that app engine sections just cannot approach. The process is easier to maintain, easier to test, and much, much easier to extend.

Now let's be clear, I'm not saying I'm leaving the Church of Set-Based Processing. (I still resolved my run control criteria with an InsertSelect into a temp table in the first step, then worked from there.) But I did learn that implementing complex business rule need not be complex. Sometimes PeopleCode and app classes are the right tool for the job.

PeopleTools 8.50 PeopleBooks

Just noticed that Oracle published the PeopleBooks for PeopleTools 8.50.

Screen Capture Software: Greenshot

Whether we like it our not, documentation is part of the job. And good documentation is worth it's weight in gold.

When I'm putting together guides for end users, I find screen shots indispensable. Of course, Snagit is the king. But half the time I'm on a client laptop and don't have Snagit.

A little google search turned up Greenshot, a nice open source option. It's a little bare bones (it doesn't have that cool sawtooth edge), but it's got pretty much everything you need.

Using Maps in PeopleCode

Maps, also known as hashmaps, are key-value pairs that let you store and retrieve data using a key. I wrote PeopleCode for years without using one, but after jumping back and forth between PeopleCode and Java, I can't write any code without maps.

What's great about maps is their simplicity. It's not that they do anything fancy, they just make writing code easier and make that code easier to understand. Wikipedia provides a good example of a map—a dictionary. The words are the keys and the definitions are the values.

One example I've used maps for is caching. If you've got a database lookup that occurs for each row in consideration, but there are only a couple of distinct lookup values, cache those values in a map instead of querying the database each time. For each row, check to see if the lookup is in the map: if it is, return it; if it is not, look it up from the database then store it in the map and return it.

Maps start to get more powerful when you realize you can store anything in one. Another example I used a map for is a totals grid. I had a list of items (a sales order or a purchase order) and wanted to display the total quantity for each item. The keys to my map were a concatenation of the BU and Item Id. The value returned by the map was the row representing that item in the totals grid.

Without the map I would have done a loop through the totals rowset each time, looking for the row in question. With the map, it was one line of code.

Can't find the Map object in the PeopleTools API? That's because it's not a delivered class. Instead, it's one that was added by application developers and must be imported to be used. In the HR database, look for application package HRTR_UTILITIES. The classes there refer to maps as hashtables. Check it out, it's good code with good comments.

Here's the public class declaration for StringHashtable, which uses strings for both the keys and values:

 class StringHashtable
   method StringHashtable();
   method Put(&key As string, &value As string);
   method Get(&key As string) Returns string;
   method GetKeys() Returns array of string;
   method IsKey(&key As string) Returns boolean;
   method GetValues() Returns array of string;
end-class;

Accessing the Run Control from App Engine PeopleCode

With the continued evolution of PeopleCode, including features like the XML Publisher, I find myself building more and more Application Engines that are primarily PeopleCode based. In many cases, in fact, I’ve got only two steps: a SQL action to populate the state record with the run control and a PeopleCode action to do the work.

Mostly it was habit. The SelectInit has been my friend for a long time. But I just realized I can bring the run control record directly into the PeopleCode step without the need of a SQL step to populate it first.

The state record is always available to a PeopleCode event with a simple GetRecord. The app engine always populates the state record with the process instance—you get that for free. Armed with that, a SQLExec to the process request table gets you the Run Control Id. Since run control tables are keyed by Run Conrol Id and User Id, you’re in business.

Local string &id;
SQLExec("SELECT RUNCNTLID FROM PSPRCSRQST WHERE PRCSINSTANCE = :1", GetRecord().PROCESS_INSTANCE.Value, &id);
Local Record &runControl = CreateRecord(Record.G9_JOBC_DESC_RC);
&runControl.OPRID.Value = %UserId;
&runControl.RUN_CNTL_ID.Value = &id;
&runControl.SelectByKey();

Of course, this counts on the app engine running through the process scheduler. Without that there’s no row in the process request table. If you like to run app engines directly from the App Designer this won’t work. (If you're debugging, just hard code your Run Control Id, then you're back in business.)

Rowset Fill Method and the JOIN Keyword

The Rowset method Fill is used to populate a stand-alone rowset from the database. It take a where clause as a parameter. The underlying record is used as a select record.

Here’s the description from PeopleBooks:

The Fill method flushes the rowset then reads records from the database into successive rows. The records are read from the database tables corresponding to the primary database record of the scroll into that record. The records are selected by the optional wherestring SQL clause, in which the optional bindvalues are substituted, using the usual bind placeholders (:n).

Often times the criteria for selection comes from a record other than the one your selecting from. Up until this point I’ve been writing WHERE EXISTS clauses to check that type of criteria. However, it turns out that you can have more than one record in the statement by use of the JOIN keyword.

The component processor generates the SELECT automatically based upon the fields in the record and the record name. After that, it just appends the where clause passed into the method. Since that’s the case, there’s no reason that where clause has to start out with WHERE.

For example, let’s say you want to select records from the JOB table, but only those with a Job Code in a certain Grade. Basically, you need JOB for the data and JOBCODE_TBL for the criteria. Here’s what that PeopleCode might look like (minus the effdt considerations):

Local Record &job = CreateRecord(Record.JOB);

&job.Fill("JOIN PS_JOBCODE_TBL JC ON FILL.SETID = JC.SETID
AND FILL
.JOBCODE = JC.JOBCODE WHERE JC.GRADE = :1", &paramGrade);

Now you’ve got SQL that’s easier to write and easier to maintain than a WHERE EXISTS clause.

Select All / Clear All via JavaScript

Good UI dictates that if you have a grid that allows users to check one or more rows, then you should also have Select All / Clear All buttons available to the user. Most of the delivered grids in PeopleSoft with the Multiple Row selection option include these. The code is straight forward, and you can even copy an existing example.

However, as with all PeopleCode, your choice is to defer it to the next server trip or force a server trip. Since it's an action that needs immediate feedback, you can't defer it. Thus you're left with a page flicker for a somewhat innocuous task.

In the never ending hunt to make usuable software, I decided to step outside PeopleTools and put together JavaScript to accomplish the task. By pushing the work to the browser, it happens immediately and without a server trip. While I impressed myself, the users did not notice—but I had what I wanted, software that doesn't get in the users' way.

Here's the JavaScript that does the work:

var ROSO_CHECKBOX_MGR = {
grid: "",
title: "",

addButton: function(f, check) {
$A($$('a[id^="'+f+'"]')).each(function(pb) {
pb.href='javascript:ROSO_CHECKBOX_MGR.check('+check+')';
});
},

check: function(v) {
$(ROSO_CHECKBOX_MGR.grid).select('[title="'
+ ROSO_CHECKBOX_MGR.title + '"]').each(function(cb) {
if (cb.checked != v) {cb.click();} });
}
};

 

The code above is a JavaScript library called ROSO_CHECKBOX_MGR_JS. The first function, addButton, attaches listeners to the Select All and Clear All buttons. The second function, check, is invoked by the listener and loops through the rows in the grid to select or clear the buttons.

Here's the JavaScript that invokes the library:

<script type='text/javascript' 
src='%Javascript(PROTOTYPE_V1601_JS)'> </script>
<script type='text/javascript'
src='%Javascript(ROSO_CHECKBOX_MGR_JS)'> </script>
<script type='text/javascript'>
U_CHECKBOX_MGR.grid = 'GRID_REC_NAME$scroll$0';
U_CHECKBOX_MGR.title = '%bind(:1)';
U_CHECKBOX_MGR.addButton('GRID_WRK_SELECT_ALL_PB', true);
U_CHECKBOX_MGR.addButton('GRID_WRK_CLEAR_ALL_PB', false);
</script>

 

The first thing to notice is the import of the trusty Prototype library. The next line imports the library from above. Within the script tag, there's not much left to do. It sets the id of the grid (as generated from the Page Field name and the component processor), then requires the Hover Text from the checkbox (it gets passed in from the PeopleCode). The Hover Text, which is the title attribute in the HTML, is used to identify all the check boxes. Finally, the addButton function is called once for each button, Select All and Clear All.

The real payoff in this case was the grid I implemented this feature on. It could commonly contain more than 1,000 rows. As such, it wasn't a half-second annoying screen flicker that I had avoided, but a 3-or-more second server trip.