This came as a comment from the following posting, and I felt that it deserved its own blog entry.

The exact question is as follows:

What would be the best approach to share reports between two PeopleSoft environments, considering we use drill down extensively?

Background

This is an extremely good question, and it brings back memories of some of the discussions we had when updating the report repository in PeopleTools 8.40. Therefore, it makes sense to talk a bit about the history of the report repository as a starting point.

Prior to PeopleTools 8.0

Prior to PeopleTools 8.0, the report repository did not exist. Customers would run reports either locally using the client/server tools (PSQED.EXE, PSNVS.EXE, PSSQR.EXE, CRW32.EXE), or on the process scheduler server. The reports would either be viewed with online viewers (when using the client/server tools), or saved in the file system accessible to the process scheduler server. Users would then access the files using network shares (and secured using network security).

PeopleTools 8.13

PeopleTools 8.13 represents the first release where we had a report repository and report manager. At the time, it was architected to work with only one system, and would not actually authenticate the user accessing a report (it would create an RBAN that was impossible to guess as the identifier of the report --- for those geeks out there, RBAN stands for Really Big Alpha-Numeric). Other limitations include a lack of using folders to categorize reports in the user interface.

PeopleTools 8.40

One of the main projects in PeopleTools 8.40 was to extend report manager to address some of its limitations. One of the primary use cases was to make it easier for users of nVision to utilize it. Here is a snopsis of some of the features that went into that release

• Folders, so that reports could have some level of categorization.
• Support for cross-system report lists
• Improved security over accessing reports (thus eliminating the imfamous RBAN).

The designs for this release pulled together all the requirements into one solution. Because foldering and cross-system access required additional data and infrastructure, it was decided to leverage integration broker for both (and the PSRF application messages were born). The report manager pages were reorganized, including two additional pages added to report manager that utilize the information published and subscribed by these new messages.

• The List tab is a standard search page against cross-system data and the first level of foldering.
• The Explorer tab is a tree view over the foldering metadata attached to the reports.
• Finally, the old report manager page was renamed to "Administration", so that you could access reports on the local system, even if integration broker wasn't working in your environment.

About Deployment Strategies

Okay, now that we have some of the background covered, let's go into a little more detail about how the cross-system report access was intended to work (because we leverage the same concepts in our Report Explorer product).

Notification versus Ownership

The key to understanding deployment options is to understand that conceptually we are separating report notification from report ownership. In other words, the system that generated a report is always owned by that report. Ownership means that the system continues to secure and grant access to the report, and any actions taken when viewing that report are specific to the system that generated the report (more on this later). This also means that any means of aggregating the list of reports and/ore notifying end-users of those reports (with links back) is just that: a list of reports with links to access them in place.

For a product like nVision, where you need to drill, the information needed to do the drilling is embedded directly in the report from the system running the nVision report. There's actually PeopleCode that embeds this information as a parameter on the command line. This means that regardless of how you open the nVision report, it will go back to the system that ran the report to perform the drill (which is exactly what you would want it to do). This works the same way if you use one of the drilling techniques discussed in the following blog entry. Because the local system is running and managing the report, you don't have to worry about it if you use the PSRF integration broker messages as a means of pulling it together. Even if you physically move the reports to a new location, the metadata needed to drill contains the URL to access the system it was generated from (but if you delete the report from the report repository after you move it, you will prevent the drilling from occurring because the process scheduler won't be able to find it).

So, the simple answer is that because the system that ran the report also puts in the additional drilling metadata (including the URLs to access the system), drilling will continue to work even if you copy or aggregate the links to access the reports elsewhere.

Labels: Drilling, Report_Manager

Chris and I finally had some downtime today to catch up on a few things.

• Catch up with Peter Gassner and the Verticals On Demand Crew
• Finally record a flash demo for Client/Server single signon

When we recorded the flash demo of the single signon, we remembered that we had one customer who had created shortcuts to run queries in windows, which single signon made work really well for them. Of course, we had to include this in the demo.

Cool! How did you do it

Well, we actually leveraged a hook that I had been using up until release 8.0 in the windows client (you could actually pass parameters in the windows client to open up a panel with a specific item the same way you can in the web).

The actual command line used in the shortcut is as follows

E:\pt846\bin\client\winx86\psqed.exe PUBLIC.QUERY.GS_TEST_JRNL_DTL

As long as you follow the syntax of PUBLIC.QUERY and then the query name (or PRIVATE.QUERY if the query is private), this will work. Unfortunately, there isn't a way to tell it to run a query and pass in the parameters (but Chris tells me he's already working on it).

Labels: Drilling, Query

This is another blog posting that Chris and I had discussed putting out there (but got distracted doing other things, one of which is keeping up with the PeopleSoft community... Chris calls this Yak Shaving.)

So I finally found this posting on ITTOOLBOX, and realized that it was time to discuss some of the intricacies of bypassing the search page here.

For those who have been following the blog, you may be familiar with some of the posts we've done on drilling and PeopleSoft.

• Adding hyperlinks to Queries and nVision reports (described in this powerpoint).
• Drilling to other Content in nVision blog posting.
• Drilling Deeper into PeopleSoft Pages blog entry.
• Code to drill to any chartfield blog entry.
• Drilling to a performance or development document from a URL blog posting.

So, as you can see, drilling is a recurring theme here in the blog. Let's look at what the component processor does, and how this affects drilling.

How the component processor handles drilling

Let's start by revisiting the how you can create a URL that opens up a specific page with a specific item. The first thing is to look at the structure of the PeopleSoft URL to do this.

http://www.psserver.com/	Server
psp	Portal or Content psp=Show portal frame psc=Show content only
/ps/
EMPLOYEE	Portal Name
/ERP/	Site
c/
PROCESS_JOURNALS	PeopleSoft Menu
.JOURNAL_ENTRY_IE.GBL	Component and Market
?
BUSINESS_UNIT=US005&	Search Parameter
JOURNAL_DATE=2004-03-31&	Search Parameter
JOURNAL_ID=MKMAR5&	Search Parameter
ACTION=U&	Mode A=Add U=Update C=Correction
PAGE=JOURNAL_ENTRY1	Page Name

So, from the above table, you can see that many of the PeopleSoft artifacts have a place in the URL. The menu and component tell you what component to invoke (and notice that the page within the component is a parameter just as is the mode in which the component is brought up). Any field on the search record of the component can be passed as a parameter, but you need to have all of the (primary) Search Keys passed if you want to bypass the search page.

Okay, I think I get it, but what can cause it to trip up?

Well, there are 3 major things that will bring you back to the search page versus getting directly into the component.

• Not all primary search fields are passed in the URL
• The component is set to force the search page to display
• The component processor encounters an unexpected condition.

Let's go through each of these one at a time

Not all primary search fields are Passed

This was actually the problem with opening up the Job page. You see, the EMPL_RCD field is a primary search field for the Job component. This is because employees could potentially be in more than one job (although in my experience it doesn't happen that often). In this example, just adding &EMPL_RCD=0 to the URL will do the trick if your employees only have 1 job. Although you could create a new menu item that overrides the search record for certain situations, it wouldn't work here because the key structure of the level 0 record of the component expects EMPL_RCD key to be passed in.

One other thing to note, is that quite often it is desirable to invoke the search page with a subset of parameters populated (to target the search results). One example that we show is drilling into tree manager, populating the fieldname, so that you can see all trees built against that field. We use this to facilitate the configuration of our report explorer product, where we know what field the user is interested in, but not which tree they would want to use.

The Component is set to force the search page to display

So, you may ask yourself why anybody would design a page so that you have to display the search page. The answer is that you can use PeopleCode to implement row level security in the SearchInit and SearchSave events (which has been done by PeopleSoft developers in spite of the fact that it isn't recommended). The only time SearchInit or SearchSave events fire is when the search page is displayed, which means that for those components our little trick of passing in values would actually bypass the security implemented for that page. This is discussed in quite a bit more detail here.

The component processor encounters an unexpected condition

So, let's say that you have a URL that opens up a component in add mode, and there is already a row in the search table that contains all of the keys passed in. Or, let's say that you pass in a set of search keys to open the page in update mode, but there isn't any data in the search record table. In both of these circumstances, the component processor will determine that it can't meet the request and will display the search page to allow the user to correct the problem (i.e. change the values passed in or change the mode in which the page will be invoked).

Labels: Drilling

Well, I guess that I should have done a little more testing prior to publishing last night's posting. In order to figure out what pages and components to look at when adding my code, I opened them up and used PSIDE helper to drill into the definitions.

Unfortunately, I didn't realize that there were global variables that drove the logic on the pages that I was working with (and those vairables prevented me from finding a couple of design flaws in my approach).

Well, I discovered the error of my ways and realized that I needed to move one component back in the chain with my entry-point (which meant that I needed to add code to two pages instead of one). Try the code on the posting now.

Labels: Drilling, HCM, User

I've recently been spending a bit of time working in HR on a Co-Presentation with Business Objects for their upcoming conference this week. One of the things I wanted to show was how to open up a performance review or a development plan from a report. Unfortunately, the pages in HCM don't allow you to pass in an EMPLID or a Review Type to open it up.

The Solution

The solution is similar in nature to what I did for navigating to values in the Journal Entry page. I used the %Request.GetParameter() function to allow the URL to navigate to the page to contain the extra parameters I needed. I then used the parameters to find the document I wanted to open, and opened it. I ended up need needing to add code to two different locations to make it work (mainly because the performance documents were written as several different components that transfer the user around):

• The first was to add Page.Activate PeopleCode to the EP_LAUNCH page. This code accepts the parameters passed on the URL. Because those parameters will be lost after a transfer event, it puts them into global variables to be used later.
• The second is to modify the Page.Activate PeopleCode of the EP_APPR_SELECT page to use the parameters passed on the command line to walk through the list of documents and open the appropriate one.

In case you were wondering what the navigation would look like, here are a couple of URLS that would open up the appropriate documents.

The first is a URL to open an annual performance document (for employee KU0065): http://www.gsdemo.com/psp/ps/EMPLOYEE/HRMS/c/ROLE_MANAGER.EP_CURRENT_MY_PRF.GBL?&PAGE=EP_APPR_SELECT&EMPLID=KU0065&EP_REVIEW_TYPE=K0ANNUAL

The second URL opens up a development document (for employee KU0119): http://www.gsdemo.com/psp/ps/EMPLOYEE/HRMS/c/ROLE_MANAGER.EP_CURRENT_MY_DVL.GBL?&PAGE=EP_APPR_SELECT&EMPLID=KU0119

Here's the code needed on the Page.Activate program within the EP_LAUNCH page:

Because this page doesn't already have PeopleCode here, you can simply copy and paste the following code(tested against HCM 8.9).

/* ********************************************************** */
/* Grey Sparling - Add Code to Open up an item passed on URL */
/* ********************************************************** */

rem Grey Sparling - Retrieve Parameters to open up employee;
Global string &gsEmplID;
Global string &gsRevType;

&gsEmplID = RTrim(%Request.GetParameter("EMPLID"));
&gsRevType = RTrim(%Request.GetParameter("EP_REVIEW_TYPE"));

Here's the code needed on the Page.Activate program within the EP_APPR_SELECT page:

The first step is to declare the global variables near the top of the program. It already contains the declaration for &EP_GBLKEYS_WRK. You should put the following lines above it.

/* Grey Sparling - Declare Globals */
Global string &gsEmplID;
Global string &gsRevType;

The rest of the code should be appended to the end of the page activate peoplecode on the EP_APPR_SELECT page.

/* ********************************************************** */
/* Grey Sparling - Add Code to Open up an item passed on URL */
/* ********************************************************** */

If None(&gsEmplID) Then


&gsEmplID = RTrim(%Request.GetParameter("EMPLID"));

End-If;
If None(&gsRevType) Then

&gsRevType = RTrim(%Request.GetParameter("EP_REVIEW_TYPE"));

End-If;

rem Grey Sparling - If EMPLID is passed, walk through the rowset to find it;
If All(&gsEmplID) Then

Local Rowset &rsEPApprSelect = GetLevel0().GetRow(1).GetRowset(Scroll.EP_APPR_SEL_VW);

Local number &j;
For &j = 1 To &rsEPApprSelect.ActiveRowCount


Local Row &rowTest = &rsEPApprSelect.GetRow(&j);

rem Grey Sparling - Test passed in parameter against current row's emplid;
If &rowTest.GetRecord(Record.EP_APPR_SEL_VW).GetField(Field.EMPLID).Value = &gsEmplID Then

Local string &rsRevType = RTrim(&rowTest.GetRecord(Record.EP_APPR_SEL_VW).GetField(Field.EP_REVIEW_TYPE).Value);

rem Grey Sparling - If there's no reivew type parameter or if there is and parameter matches for current row, open up document;
If None(&gsRevType) Or

&rsRevType = &gsRevType Then

/* Transfer the user to the selected review sub-document */
&EP_GBLKEYS_WRK.EMPLID.Value = &rowTest.GetRecord(Record.EP_APPR_SEL_VW).GetField(Field.EMPLID).Value;
&EP_GBLKEYS_WRK.EP_APPRAISAL_ID.Value = &rowTest.GetRecord(Record.EP_APPR_SEL_VW).GetField(Field.EP_APPRAISAL_ID).Value;
TransferPage(Page.EP_APPR_DETAIL);
Break;


End-If;
End-If;
End-For;

End-If;

In case you saw this post prior to October 12, 2007, you may have noticed that it's different (and the original posting didn't work). I apologize for that, and now that I understood the way that global variables worked in the design of the application, I was able to figure out that I needed to test by establishing completely new sessions to ensure that prior runs didn't utilize previously set global variables.

Labels: Drilling, HCM, User

So, you have a journal with several hundred lines in it, and you want to find the lines that hit a particular account, fund code, and program code. You have a query that tells you it's there, but you want to pull up the journal entry itself to review it there.

This is the culmination of the last two posts with a few extra tidbits, such as array handling, rowset handling, and using variables to pick the field you want to use.

This posting shows how to pass any chartfield combination on the URL for accessing the journal entry page, allowing the navigation to the journal line(s) that contain those chartfield values. The code is written against Financials 8.9, but should work for previous versions.

Using a variable as a field reference?

Yes. One of the key items in the solution is the ability to evaluate a variable to get a fieldname in a record.field reference (this is how I'm able to take the list of chartfields from the BU_LED_CF_VW and use them). For those who aren't familiar with this, you can use @("RECORDNAME." | &VariableName) to do this. We often wrap this syntax with GetRecord or GetField to make it more error-proof in some of our other products.

Solution to the problem

The code does the following:

• It matches up the valid chartfields with parameter names passed on the command line
• It determines whether the journal is editable.
• If it is editable, then it uses the SetCursorPos function
• If it isn't editable, then it sets the work fields that the adjust_line_scroll() function uses

Here's the code (it should be added in the page activate Peoplecode after the PeopleSoft-delivered page activate PeopleCode in the JOURNAL_ENTRY2_IE page).

/* Grey Sparling - Call function to return journal lines with chartfield values */
Declare Function adjust_line_scroll PeopleCode FUNCLIB_GL.JOURNAL_LINE FieldFormula;

/* Grey Sparling - Code to drill to row with chartfield values passed in as a parameter */
Local Rowset &RS_CFS = GetRowset(Scroll.BU_LED_CF_VW);
Local string &sChartField;
Local number &sNumParameters = 0;
Local boolean &PassedParameters = False;
Local array of string &CF_Params;
Local array of string &CF_ParamValues;
Local number &m;

&CF_Params = CreateArray(&sChartField);
&CF_ParamValues = CreateArray(&sChartField);

/* Grey Sparling - Build Array with all the parameters passed in the Request Object */
For &l = 1 To &RS_CFS.ActiveRowCount;

&sChartField = &RS_CFS(&l).BU_LED_CF_VW.CHARTFIELD.Value;
&CF_ParamValues [&sNumParameters + 1] = %Request.GetParameter(&sChartField);

If All(&CF_ParamValues [&sNumParameters + 1]) Then

&CF_Params [&sNumParameters + 1] = &sChartField;
&PassedParameters = True;
&sNumParameters = &sNumParameters + 1;

End-If;

End-For;

/* Grey Sparling - Use the parameters in the array to find the appropriate set of Journal Lines */
If &PassedParameters Then

If JRNL_HEADER.JRNL_HDR_STATUS = "D" Or
JRNL_HEADER.JRNL_HDR_STATUS = "P" Or
JRNL_HEADER.JRNL_HDR_STATUS = "U" Then
/* Grey Sparling - Journal is read only */
/* Grey Sparing - Loop through parameters array and set them in work record for searching */
For &m = 1 To &sNumParameters;
@("JRNL_PANELS_WRK." | &CF_Params [&m]) = &CF_ParamValues [&m];
End-For;

adjust_line_scroll();
Else
/* Grey Sparling - Journal is editable */
Local Rowset &rsJrnlLines = GetLevel0().GetRow(1).GetRowset(Scroll.JRNL_LN);
Local number &j;

/* Grey Sparling - Loop through Journal Lines in Panel Buffer to find first one that matches combination of parameters */
For &j = 1 To &rsJrnlLines.ActiveRowCount
Local Row &rowTest = &rsJrnlLines.GetRow(&j);
&sFoundCombination = True;

/* Grey Sparling - Loop through fields in array to see if current row matches all passed parameters */
For &m = 1 To &sNumParameters;
If &rowTest.GetRecord(Record.JRNL_LN).GetField(@("Field." | &CF_Params [&m])).Value = &CF_ParamValues [&m] Then
&rowTest.GetRecord(Record.JRNL_LN).GetField(@("Field." | &CF_Params [&m])).SetCursorPos(%Page);
Else
&sFoundCombination = False;
End-If;

End-For;

If &sFoundCombination Then
Break;
End-If;

End-For;
End-If;

End-If;

Labels: Drilling, PeopleCode

For those who are familiar with our demo and posting that discusses how to drill from a report into a page (blog posting here), you may or may not notice a limitation in what was presented. The example showed drilling from a financial report to the journal entry where the number came from. Unfortunately, drilling to the journal is just not granular enough to tell you exactly where the number came from (journals can have hundreds of lines, and a number in a financial report is governed by the chartfield values that are used).

This means that what you really want to do is to drill to items that are at scroll level 1 or greater in the page. Because the standard URLs to PeopleSoft pages are driven by the search records for those pages, you need to be able to (1) pass parameters to identify what values you want to navigate to, and (2) write code to do the navigation.

Sounds interesting, so how do you do it?

Well, the first part was answered in this posting on how to add parameters to your PeopleSoft pages.

The second part can be acoomplished in multiple ways (depending on the following):

• Whether the page or scroll items are read only.
• Whether the data to be navigated to is chunked by application code or by PeopleTools.

Using the SetCursorPos PeopleCode Function

The first approach we will discuss is using the SetCursorPos function. This works by iterating through the data in the component buffer until you find the row you want to be on, and setting the focus (or cursor position) to a field on that row. Because you can't navigate to fields that are grayed out (or are read only), this only works when that occurs. Also, since you are navigating through what's already in the component buffer, if the only loads a subset of the data at a time into the component buffer, then you may be navigating through a small part of the data you want to search. Navigating to a posted journal entry in PeopleTools is a perfect example of where both of these conditions would prevent this from occurring.

Here is an example of code you would use for a page with updatable data where the component buffer contains the full data set you want to search

Local Rowset &rsJrnlLines = GetLevel0().GetRow(1).GetRowset(Scroll.JRNL_LN);

Local number &j;
For &j = 1 To &rsJrnlLines.ActiveRowCount


Local Row &rowTest = &rsJrnlLines.GetRow(&j);

If &rowTest.GetRecord(Record.JRNL_LN).GetField(Field.ACCOUNT).Value = &sAcctNum Then

&rowTest.GetRecord(Record.JRNL_LN).GetField(Field.ACCOUNT).SetCursorPos(%Page);

Break;
End-If;

End-For;


Adding a navigation element to the grid

If all the items in the scroll are read-only (or grayed out), then another option is to put a push button or other element in the grid that isn't grayed out to set focus to. It's actually as simple as that. You add the item, and then set the cursor position to it. Of course, this gets into customizing the page itself, which can be an issue at upgrade time.

Leverage selection code written into the page

This approach can be used very effectively in inquiry pages or even pages where there search logic is used written by application developers to populate the scroll. The journal line page is a great example of this. There's a link in the Financials 8.9 journal entry page that allows you to enter search criteria for your journal lines. This page actually displays fields in the JRNL_PANELS_WRK record, which is in the componenet buffer for the page. By merrely setting the values of chartifelds in this work record and calling the adjust_line_scroll function, you can use parameters to restrict the set of journal lines displayed in the page (ultimately drilling to those values).

Here is the code to do that.

Declare Function adjust_line_scroll PeopleCode FUNCLIB_GL.JOURNAL_LINE FieldFormula;

/* Code to drill to row with account number passed in as a parameter */

Local string &sAcctNum = %Request.GetParameter("ACCOUNT");
If All(&sAcctNum) Then

If JRNL_HEADER.JRNL_HDR_STATUS = "D" Or
JRNL_HEADER.JRNL_HDR_STATUS = "P" Or
JRNL_HEADER.JRNL_HDR_STATUS = "U" Then

/* Journal is read only */
JRNL_PANELS_WRK.ACCOUNT = &sAcctNum;
adjust_line_scroll();

End-if;

End-if;

One last item of note: if there is already Page Activate PeopleCode, you will probably want to put yours at the end for the navigation (this ensures that all other logic has already been executed). The JOURNAL_ENTRY2_IE page is an example of this.

Labels: Drilling, nVision, PeopleCode

So, you want to navigate to a PeopleSoft page, passing the parameters to open a specific item. But now, you want to pass a parameter that does something within the page:

• Populate the search items in an inquiry page and search based on the parameter.
• Set the vendor to be used in the voucher page
• Navigate to a row in a grid in the page

These are but a few examples

You can really do that?

Of Course! It's actually pretty straightforward. The key to this is the Request object (it's actually documented in PeopleBooks as part of the Iscript class, but is available for standard pages as well).

So, all you need to do to add a parameter to your page is to write code in the page activate peoplecode that looks for a parameter in the request object with the name you want (or a set of names). Once you have those parameters, you can use them in different ways (as listed above.

/* Code to do something with account number passed in as a parameter */

Local string &sAcctNum = %Request.GetParameter("ACCOUNT");
If All(&sAcctNum) Then

Rem Do something with &sAcctNum;

End-if;

Because you can look for any parameter, regardless of whether it's actually passed, you have a lot of flexibility (and you can look for as many as you want). Therefore, you could actually go through and look for every chartfield on the URL and use the combination of the ones you found to do some logic.

The next posting will show how this technique can be used to fix limitations in drilling to pages that were glossed over in the following posting.

Labels: Drilling, PeopleCode

Well, it's official. We've expanded our add-in to provide lots of cool new features for excel output in PeopleSoft.

It's always interesting to see how one of your products gets used at a customer site and the value of it. When we added query features to the product, we knew they were cool (otherwise, why would you spend the time, right?). What we didn't realize how much it could change the way people use PeopleSoft.

What do you mean?

Well, we recently did some travelling to spend some time looking at how customers were using the new features of the product, and here's what we found.

• Requests for new Reports went to near zero.
• End-users were able to cut the time the spent looking for information in half.

Although we realized there would be some impact here, the numbers were surprising. However, this is merely an aspect of scale and is limited to the aspect of reporting. What really surprised us is how the feature of drilling to pages from queries changed the way end-users utilized PeopleSoft.

• End-users used queries in place of PeopleSoft search pages in the application, because the queries gave them a more efficient list of items to work.
• End-users also used our Excel drill menu in place of the PeopleSoft menus wherever possible because it allowed them to get to pages more quickly than using the PeopleSoft-delivered navigation (with the added bonus of passing the context from where they were)

And, finally the metrics: Some end-users at one customer were almost 50% more productive with these features because so much of the time spent using PeopleSoft for them was navigating to pages and finding the list of items that they needed to work. Bringing it all together and streamlining the navigation made a dramatic impact for them (and gave them the additional time to tackle other projects that had been languishing).

Want to learn more?

You can learn more about the product here. In addition, we've put together a flash demo that shows it in action.

Labels: Drilling, nVision, Products, Query, User

Although I put together a previous posting on Drilling and nVision, I only included a single nVision drill layout to illustrate this. I already put together comprehensive packages to show:

• Reporting in HCM (Orig Posting, Demo, and Code)
• Reporing in Student Admin (Orig Posting, Demo, and Code)

Code for Financials Drilling to Pages and Queries (among other things).

I decided to put together a package for Reporting in Financials (Orig Posting and Code). As with the other packages, the code includes a working example with our nVision drilling product.

Labels: Drilling, nVision, Query

Over the past 24 hours, I've had lots of folks wanting to learn more about the HCM reporting examples in yesterday's post. I decided to record a flash demo that shows how one would use the queries as well as the nVision reports (and drills).

In order to simplify the navigation in the demo, I did use the nVision Drilling Snap-on (which is separately licensable, but is not required to use the queries and nVision objects in the project). However, it does make it much easier to find and use them together.

Click here to watch the HCM Reporting in action...

Labels: Drilling, HCM, nVision, PeopleSoft, Query, Tree_Manager

This posting goes in the category of "why the $#%$^&$% didn't I do this earlier?!?!".

Yes, I finally spent some time getting myself up to speed on PeopleSoft HCM. One of our loyal blog readers was looking at using nVision to do some analysis without paying the significant costs of purchasing a BI tool and asked for recommendations for reporting against PeopleSoft HR. My only excuse as to why I didn't dig into this earlier was that I had a very strong Financials background (Cullinet Purchasing, followed by Millenium Financials, and finally working with version 1 of PeopleSoft financials and the beta version of AR and AP... I was one of the first 4 Financials consultants at PeopleSoft). Unfortunately, I never got to it.

Yeah... You made your point... You've been busy... But why do it now???

Good (but very cynical) question. Coming off of the success of our Open World presentations, we're gearing up to present at several other conferences this year. In order to make the presentations interesting to both mystelf and those who may have already been to one of our sessions, I decided to switch the subject matter to be HCM. When I got started, I was really excited about this. Then I with the person who put together the reporting demos that the Oracle/PeopleSoft salespeople use. Apparently, she did this years ago.

Oh well, I guess the difference is that I did the work myself, and that I'm making it available to all of you (so you may thank me now... or curse me now for not doing it earlier).

Queries

Let's start with Queries (because although I created some very cool nVision examples, I'm afraid I'll lose many of you HR junkies without hooking you first). I started putting together queries that focused on getting employee lists based on different attributes, such as JobCode, Deptid, and Supervisor.

Here are the queries that are included in the project (again, most of which are built against the JOB and PERSONAL_DATA records.

One of the things that France Lampron, founder of NuvoSoft taught me when we brought her in for use case development for nVision 9 was the importance of analyzing the HR actions in HCM reporting (this tells you whether you need to be worried about a manager that has a large number of termination actions for his employees). In honor of her, I created a query that lists the actions for a given employee. Here is what the results look like prompting against a given employee:

Attached in the Zip file below is a project that includes all these queries for your personal use. Keep in mind that I hadn't focused on whether you want to provide security for the data in them (query security will handle that in general. However, if there are users who should have access to some of the data in these records, you should create views that only show the pertinent fields, grant access to them by adding them to the tree, and build the appropriate queries, using these as a guideline).

nVision Reporting against HCM

For me, this is probably the most important part of this posting. I am going to prove, once and for all, that nVision does work for doing analysis against HCM, and that there are a lot of good reasons to use this tool. As with many other things in this blog, had I spent the time on this earlier, PeopleSoft customer probably would have had much better reporting delivered out of the box (and I'm truly sorry for that).

When looking at nVision reporting in HCM, I focused on three main areas:

• Generating a report that shows headcount and salary information at the organization level. This was based on a delivered nVision report (that hadn't been tested in many PeopleSoft releases).
• Generating a report that shows headcount and salary information by salary grade. Again, this was based on a report delivered as part of HCM, but was also broken.
• Generating a report that shows a benefits summary. This required less work to fix up.

The data in the standard HCM database has good data in the US006 business unit, that's what I used:

Here's what the departmental headcount and salary report looks like:

Drilldown Layouts

One of the key features to nVision is drilling. It allows you to take slices or list out details that support the data shown in a summary report (sometimes called analysis). The most important drills you can include in HCM are ones that list out employee information (either personal data or job information depending what you're looking for). There are also drills that allow you to see how FTE or Salary information is broken out by different atrributes (such as the department tree, jobcode, or location). Here is the list of drills I developed as part of this effort:

When drilling from the Department Salary report to the Employee Salary details (from the Job record), this is what you would see:

Other Information that might be Useful

One thing that is important to note is that all these examples perform analysis as of the date that the report or drill is run. This means that there is no time series analysis (i.e. tell me who's enrolled in benefits right now). For time series analysis, you will need to create a view that segregates the effective dates of the data into categories (such as month and year) for doing that type of analysis. That may come in a future posting.

Excellent! How can I get a copy???

I'm glad you asked. I've put together a zip file that contains the nVision definitions as well as the query definitions discussed above. The nVision reports require the query definitions, because the delivered ones by HCM are broken.

Download the zip file from here.

Copy the .xnv files from the nVision directory to your nVision directory, and copy the projects directory to your projects directory (and use app designer to import the project).

Taking it to the Next Level

One of the other things that I did as part of this effort was to pull all these objects together with our nVision Drilling Snapon. This allows you to organize your nVision drills better as well as enable drilling from your nVision reports to Queries and pages to either get additional information or take action on the contents of your reports.

Here is an example of what the drill menu could look like:

I plan to put together a flash demo that shows how you can drill from a departmental report to attributes, drill to employee detail, query on attributes of the employee, and maintain employee information, all from one place. As my daughter would say, "FANTABULOUS!!".

Labels: Drilling, HCM, nVision, PeopleSoft, Query, Reporting

For those who were interested in downloading the powerpoint to the presentation I gave, here it is.

It contains the tips I gave in the following areas:

• Drilling from and to PeopleSoft Reports
• Applying Data Security to PeopleSoft Reports
• Setting up reporting jobstreams in process scheduler
• Getting Context from numbers in nVision Reports

Labels: 2006, Drilling, Events, nVision

Last week, we had a great trip to the Washington, DC / Richmond, VA area. We had lots of meetings with PeopleSoft customers, helped many of them with some of their PeopleSoft issues, and got lots of great ideas for new products.

Presentation
For those who are interested in looking at the PowerPoint presentation I gave, here's a link to download it. The presentation covered the following topics:

• Adding hyperlinks to your reports (one of our favorite BLOG entries)
• Adding Security to your reports (another favorite)
• Creating production job streams for your reports using process scheduler

Report Validation, Analysis, and Balancing

We had several customers express interest in us building a product for Report Validation, Analysis, and Balancing. This product would address significant business and compliance needs by automating:

• The validation and analysis of similar reports in your system (across different formats and reporting tools)
• The balancing of reports in your system (to ensure that you're reporting accurately across the different parts of your business)
• The tying out of numbers across systems, such as validating numbers in EPM against the source systems, or validating your numbers in an upgrade.

Due to the interest from customers, we expect to have the product ready in September. If you are interested in the product and would like to work with us as an early adopter, contact us at ReportBalancingProgram@GreySparling.com.

Labels: Drilling, nVision

With the recent popularity of our nVision drilling snap-on, we've had the opportunity to talk to many customers about nVision drilling performance, and how to improve it. Based on these discussions, I decided it was time to put a blog posting out that discusses it.

80% Rule
Let's start by discussing the two settings in PeopleSoft that cause 80% of the performance issues. Many DBA's will tell you that there is never a simple fast/slow switch that they can set (otherwise, why would you set it to slow?). We, in our infinite wisdom at PeopleSoft put this switch in place on the process scheduer and application messaging products.

Okay. That's not very fair, because these settings were put in place for something different than nVision drilling, and drilling is merely affected by these settings. However, based on the default values of these settings, nVision drilling can have a latency of 30 seconds or more.

The two settings to look at are polling intervals of the process scheduler and the application messaging dispatcher. For each of them, the default setting is set to 15 seconds, which means that with the default settings, the process scheduler will wait 15 seconds between times it checks to see if you've requested a process to run (i.e. a drilldown request), and the message dispatcher will wait 15 seconds between times it checks to see if there's a message for it to process (such as a report being completed).

Therefore, if it normally takes around 30 seconds to run a drill and you're using the default settings, reducing them will improve your performance (but keep in mind, that the server is doing more work by checking the queues more frequently).

• Process Scheduler Changes: The Server settings page in PIA (PeopleTools --> Servers) is where the Sleep Time is set for a given server (default is 15 seconds)
• Application Server Config Changes: In the application server config file (or using PSADMIN), there is a Scan Interval setting in the PSMSGDSP section, which sets the interval by which the message dispatcher sleeps between picking up requests (default is 15 seconds)

Other areas to look at:
There are several other things that can affect the performance of your drills:

1. The performance of running reports. See the following posting for more information on general nVision performance tuning.
2. Web Server Performance
3. The performance of the PSREPORTS servlet (which moves reports to and from the report repository).
4. The performance of the REN server (the application server process that makes running to window possible).

The Anatomy of running a drill

When you run a drill in nVision (whether you run it through our snap-on, or you use PeopleSoft-delivered functionality), it does the following things:

1. It opens a web page that passes parameters from the report and requests the drill to be run on the process scheduler.
2. The process scheduler picks up the request and invokes nVision and requests it to run the drill.
3. nVision launches and calls the PSREPORTS servlet to move the report you're looking at from the report repository to the process scheduler server.
4. nVision selects the cell you're drilling from, and requests the drill to be run (which opens up the drill layout, generates the SQL and runs the drill in the same way that an nVision report is run).
5. When the report is completed, the process scheduler calls the PSREPORTS servlet to move the drill results into the report repository and publishes a message that indicates that the report is finished (using application messaging).
6. The message dispatcher takes the message and updates the metadata in the report manager tables, so that the report shows up and is available to the right people.
7. The REN server is notified that the report is ready to be viewed, and it notifies the browser of the person running the drill.
8. The browser invokes the URL to view the report, which calls the PSREPORTS servlet to access the drill results and download it to the client.

There! All done! Wasn't that easy and simple? So, you can see how the latency of the polling can affect how long it takes to get drill results. However, the PSREPORTS servlet, web server performance, and the performance of the REN server can also affect the delivery time.

PSREPORTS Servlet

The PSREPORTS servlet is the process that moves content into and out of the report repository. As you may notice, it is called 3 times when drilling. Once to get the report from which you are drilling, once to post the drill results to the report repository, and once to deliver the drill results to the end-user. The PSREPORTS servlet is actually a pretty small and simple program. All it does is authenticate the user, check whether he has access to the report, and copy the file.

Usually, when there are performance issues, it's caused by the file system used by the report repository or by network performance issues between the process scheduler server and the report repository (although network performance between the end-user and the report repository can also be a culprit, you'll see performance issues with anything you access from report manager).

Web Server Performance

The interface between the client requesting the drill and nVision is through the web server (i.e. a PIA page). If there are web server performance issues between the client machine and your PeopleSoft web server, you'll also have performance issues with drilling.

REN Server Performance

The REN Server handles the messaging between PeopleSoft and the browser page. PeopleSoft's internet architecture was designed to allow a stateless connection between the browser and the server. This means that all interactions between the client and the server are request / reply. In other words, you put some data in a page, post it, the server processes it, and replies with a new web page. This means that a page cannot be out there waiting for an event to occur to do something.

In comes the REN server (Realtime Event Notification). What it does is it sends a message to a specially designed page when an event occurs (and the page does processing in it). The run to window page is one of these special pages (but the multichannel pages in CRM are also other special pages).

The REN server generally isn't a performance problem, but can be if the application server machine that it runs on gets overloaded with too much other processing.

In closing...

Now that you know more than you ever wanted to know about process scheduler, REN Server, and drilling, you can impress your co-workers at your next company function (or just annoy them).

Labels: Drilling, nVision, Performance

Yesterday, I participated in a very interesting conference call with a PeopleSoft customer who has been struggling with certain aspects of drilling in nVision. Although I referred to several of the components of this previous posting on drilling in nVision, I realized that there are other aspects that weren’t discussed that warrant a follow-on posting.

Drilling to a PeopleSoft page

For those who read the previous drilling posting and downloaded the Journal drill layout that was part of that posting, you will see a "working" example of this. I forwarded a similar layout to the PeopleSoft Sales Support organization, so that they can use it as a template for building out examples of drilling to a peoplesoft page. One thing to note is that this drilldown layout is dependent on a defined name that is only populated when a report is run through the web (not on the client), so it will not work when you drill in 2-tier.

When enabling a layout to drill to a page, you will be using a feature that the PeopleSoft Portal uses for navigation: the URL syntax for opening a page with a piece of data. When scoping PeopleTools 8.4, we realized that although the syntax existed, it was relatively cumbersome to come up with this URL on your own. Therefore, we added a "copy URL" icon to the upper right of every PeopleSoft page (which shows up as an "HTTP" icon on the upper right). When you open a PeopleSoft page with a value (such as a purchase order), you can click on the image and the URL will be copied to the clipboard. You can then paste it into notepad or another editor to see what is needed to access the page with that piece of data.

When reviewing the URL, you will see the standard web server information (or URI), as well as the query string. Any needed parameters will show up as something similar to "&BUSINESS_UNIT=US001&PO_ID=10122274". When setting the layout up to drill to a specific value, you will be substituting cell references that contain the values you need in the right side of the each = sign.

Now that you know what the URL needs to look like, it is now time to add the links to your drilldown layout. The easiest way to do this is to follow a technique similar to that posted in the "enabling macros" posting in this blog. You use the drilldown layout to get real data in it (so that you can ensure that the references will give you the right values). When adding the links, you will be using the =hyperlink() function in excel, and then concatenating the string for the hyperlink.

An example of this is the following:

=hyperlink("http://servername.peoplesoft.com/psp/...
&q=BUSINESS_UNIT="&C5&"&JOURNAL_ID="&F5,"Journal Inquiry")

Keep in mind that the function must be put in the total row of the layout, and that you need to make sure that the column that contains the hyperlink function has the copy formulas setting in the nVision dialog, so that your formula will be copied to each row of data.

Obviously, if the drilldown result doesn’t have enough information to pass to the hyperlink, you may have to create a new drilldown layout (or extend the existing one). This may require adding fields to the query supporting the drill if the query doesn’t have that data.

Drilling from nVision to a query

Drilling from nVision to a query is almost the same as drilling from nVision to a page. There is a URL syntax for running PS/Queries, just like there is a URL syntax for opening pages. Therefore, you can drill from an nVision report using the same techniques listed above, as long as you know how to construct the URL for running a query.

This technique is put to use in PeopleTools 8.4 in the security queries. When navigating into the Security administration pages, there’s a page that has hyperlinks to run queries to tell you information about what users are in what roles, what permission lists have what pages and roles, etc.

The easiest way to find these hyperlinks is to use the Query Viewer, and right click on the "view" link for one of the queries in a search result to copy the shortcut. You can then copy it into notepad or another tool. This will give you the foundation for the hyperlink, but is missing the components of the URL that contain the runtime parameters (or prompts) for the query. In order to add the needed parameters, you merely append to the URL those parameters. In query, these parameters are identified by a BIND#, where the # is a sequential number. In other words, a query with 3 parameters will have the following appended to the end of the URL: "&BIND1=value1&BIND2=value2&BIND3=value3"

Therefore, an example of this is the following:

=hyperlink("http://servername.peoplesoft.com/psp/... &BIND1="&C5&"&BIND2="&F5&"&BIND3="&H5,"Drill to Query")

Limitations to this approach

The general limitations in this approach are as follows:

You must hard code the URLs into the layout (which can be an issue when you have both a development and test environment… A technique to minimize this issue is to put both URLs into each spreadsheet in different cells, and then switch the one you use as part of the move to production (using a defined name to accomplish this will minimize the number of cells you have to touch).

You need to have a different column for each target you want to go to (and, again, this is hard-coded into the report).

Products we've created to address these limitations

Due to the interest in this posting, we've created a product that dramatically improves drilling in nVision with minimal impact to your environment. Our nVision drilling snap-on allows drilling to pages and queries without requiring you to modify existing reports as listed above.

Take a look at the product page and even watch a pre-recorded demo of it in action.

If you want tighter integration between pages and reports, you're probably interested in our ERI products. The linkage is completely external from the drilldown layouts, and the menus are generated dynamically, based on the context. In addition, the ERI toolkit allows drilling from a PeopleSoft page back into reports without requiring modification of the PeopleSoft page. However, if you have a limited number of places to drill, and a limited number of drilldown layouts to use, ERI may be overkill.

Labels: Drilling, nVision

With the move from Client/Server to web, the drilling functionality in nVision has suffered. There are two main issues that were introduced:

1. the fact that when invoking a drill, the user had to navigate himself to determine when the processing has completed, and then navigate to open the report.


2. the fact that there was no way of organizing the drilldown layouts to make them easier to find.

The first item was addressed in PeopleTools 8.42. However, the second one still remains.

Improving the drilling usability

I'm going to start this part of the posting by providing some information that will cause me to lose all credibility to the loyal readers of this blog: I'm the person who developed that god-awful drilldown page (and I'm very embarrased that it hasn't been replaced). As product manager at the time, I had a minimal development staff that I could dedicate to the development. Thus, I had two difficult choices available to me:

1. Not move nVision drilling to the web.


2. Move it to the web with limited functionality, pitching in wherever I could.

Therefore, in addition to managing the development of tree manager, query, cube manager, and supporting infrastructure to EPM, I jhad to develop the nVision report request page, the Scope page, and the Drilldown page.

So, you got me into this mess, tell me how to improve it

Well, I didn't quite get you into that mess completely. Those familiar with how drilldown navigation works in the client/server environment, you'll know that the navigation was an issue there, but that there were some options to give users control over the sequence of the layouts in the drilldown menu in excel.

Quick and dirty solution...

We'll start with the quick and dirty solution (i.e. no changes to the god-awful pages I created).

When looking at this problem, the best way to improve the drilldown navigation is to minimize the number of items displayed in the drilldown list page. Unfortunately, the financials application ships with lots and lots of drills. This is because they deliver drilldown layouts that nPlode two differen axes and hard-code the metrics that they display in the drilldown layout. This causes layout explosion.

One technique I demonstrated to the PeopleSoft salespeople was to throw away those layouts and develop layouts that did two major things:

1. nPloded on only one axis. Because nVision allows drilling from drilldown results, instead of nPloding on two axes, you can drill into one field, and the drill into the other. This reduces the permutations of layouts, because you're not creating a layout for every combination of fields, you're only doing 2 for each (one for going to details, and one for drilling down 1 level using the tree nPlosion nPlode to immediate children).


2. Maximize the inheritance of those layouts. Most people create drilldown layouts that have the amount field hard coded in it. This is because in order for nVision to know where to put numbers, there has to be an intersection between row specifications and column specifications. Instead of picking a field, like posted total amount for a column, my recommendation is to add business unit criteria, but to pick "selected field all values" for the criteria. Just about every data source for nVision has business unit in it, and by saying all values, all you're doing is identifying where to put the numbers.

In the end, my recommended methodology is as follows:

1. Create 1 layout per chartfield that nPlodes the rows for that field at the detail level. Here is an example of one.


2. Create 1 layout per chartfield that nPlodes the rows to the next level of your primary tree for that chartfield. Here's an example of one.


3. Create 1 layout per tabular listing you want to drill to (such as journal lines, employees, voucher lines). This will allow you to drill to the lowest level of detail for them. Here's an example of one.

More involved solutions

Another set of solutions is to modify the drilldown pages delivered by PeopleSoft. One solution could be as simple as giving users the opportunity to personalize the order of the layouts. Others could be more involved (such as adding functionality to identify whether a drill makes sense with a report and prune the list, and using metadata about the drill layout to categorize them).

We believe that this is the right solution in the end. This is why we have defined a set of nVision drill enhancements like that above. Our methodology is to capture the information that's in the excel nVision layout and store it in tables in the PeopleSoft system. We can then use that information to prune the drill list and organize the drill layouts. As part of this we also add the ability to personalize the set of drills. From the feedback we've received so far, this seems to be a very valuable thing for most PeopleSoft customers.
    

Labels: Drilling, nVision