import PT_MCF_MAIL:MCFOutboundEmail;

/*
 * Custom function for sending scheduled queries via email. 
 * 
 * This gets called from the PSQUERY Application Engine program.
 *
 */
Function EmailQueryResult(&qryFile As string, &queryName As string, &fmt As integer, &to As string) Returns integer;
   Local string &CR = Char(13) | Char(10);
   Local PT_MCF_MAIL:MCFOutboundEmail &email;
   &email = create PT_MCF_MAIL:MCFOutboundEmail();
   &email.Recipients = &to;
   
   rem Calculate our file attachment information;
   Local string &ext = "txt";
   Evaluate &fmt
   When = 2
      &ext = "pdf";
      Break;
   When = 5
      &ext = "html";
      Break;
   When = 8
      &ext = "xls";
      Break;
   When-Other
      Error ("Don't know how to handle output type " | &fmt);
   End-Evaluate;
   
   Local string &attachName = &queryName | "." | &ext;
   Local string &attachDescr = "Query Results for " | &queryName;
   
   rem Tailor these for your own use;
   &email.From = "process.scheduler@example.com";
   &email.Subject = "This is the subject line";
   &email.Text = "The query is attached." | &CR | &CR | "I hope you like it.";
   
   &email.AddAttachment(&qryFile | "." | &ext, %FilePath_Absolute, &attachName, &attachDescr, "", "");
   
   &email.SMTPServer = "localhost";
   
   Return &email.Send();
   
End-Function;

/******* 
 PSQUERY changes - these lines go directly after the &aQry.RunToFile call
 *******/

REM Should test result and/or file existence before emailing;
Local string &to = "comma.separated.list, of.recipients.goes.here";      
Local integer &res = EmailQueryResult(&sOutFile, PSQUERY_AET.QRYNAME, %OutDestFormat, &to);

Labels: 2009, AppEngine, PeopleCode, Process_Scheduler, Query

Labels: 2009, Process_Scheduler, Security, Sysadmin

So, does one size fit all with retaining the reports you're running and sending to the report repository? Based on the discussions we've had with customers over the past several years, the answer is definitely not. Unfortunately, that's exactly how report manager handles it. Every report, regardless of whether it's a drill, ad-hoc query, or financial statement has the same retention period. Obviously, this is not ideal, because not all reports are created equally.

Believe it or not, the options for setting the retention period are very similar to several other blog postings we've made. For example, our posting on XMPP integration with PeopleSoft uses one of the hooks, the PSRF_REPORT_CREATE application message. Another option is to use application engine to periodically set the retention period for you.

This blog entry will cover both, and you will probably want to do both as well. This is because using the application message is an ideal way of accomplishing this because it keeps track of what you haven't processed for you and your code is invoked a little bit over time (as reports are run).

Because it's difficult to debug subscription PeopleCode, I generally do both together: write my code to do the processing I want, and test it first using application engine (with warning messages causing data to show up in a log file). When I've got it done corretly, I take my logic and move it to my subscription peoplecode, hooking the values up to the contents of the message (and remembering to comment out my warning messages).

One of the first things you need to consider is how you want to set your retention periods. For the purposes of this blog entry, I've put in the following rules:

• If it's an nVision drill, set the expiration date to be 1 day from the date the report was run.
• It it's an nVision report, set the expiration date of the current report to be 1 year from the date it was run, and then set the expiration date of all reports using the same set of parameters to yesterday.
• If it's any other report, keep the default expiration date for the current report, and set the expiration date for all reports using the same set of parameters to yesterday.

You can obviously use other rules and lookups and go as crazy as you want. In fact, this is why we have plans to build a report retention product, because we see a lot of options that people won't necessarily want to develop for themselves.

The first thing to know is the PS_CDM_LIST table. This is the primary table used by report manager, and will be the one you'll be updating with the new expiration date. It also contains most of the information you'll be using to attach retention rules (such as process type, process name, and report description).

The second thing to know is the PSPRCSPARMS table. This is the table the process scheduler uses to store the runtime parameters used by any process run by it. It's also important to know that the process instance is often embedded in the parameters, which means that if you want to find all reports with a common set of parameters, you need to exclude this one from your comparison.

Although I have attached a project to this entry with both the app engine program and the subscription peoplecode, I also wanted to include the following in-line:

Here is the App Engine PeopleCode

This will need to be beefed up to do more than testing, because it only processes a single process instance (either the max one or one that you set manually). Here's it's code:

Click here to download the project that can be imported into application designer. Keep in mind, though, that there are several limitations to this code:

• It only supports a subset of the types of retention periods you would want to set
• The SQL for updating an item isn't constrained by date
• For app engine-only setting, you will want to use set logic to select and update in one fell swoop. Because I'm leveraging application messaging, it automatically keeps track of reports I've already processed.

Labels: Process_Scheduler, Report_Manager, Reporting

• It only works when a person runs processes for himself (and only when he requests the output to be run to window).
• It doesn't work for distributing to multiple people at the same time.
• If the browser window receiving the results is closed, the automatic notification is lost, even if another window is started.
• It doesn't work for process scheduler jobs.
• It doesn't work for scoped nVision reports.
• You must have the REN Server configured for it to work.
• You must be on PeopleTools 8.42 or greater.

• You are notified when any process is completed, not just the ones you run to window
• Users are notified any time output for them is ready, regardless of whether they ran it or how many people are on the distribution list.
• Output from process scheduler jobs is notified.
• This works regardless of whether the REN Server is available or set up.

Labels: Email, Process_Scheduler, Reporting

1. For nVision, we know we want to join PS_NVS_REPORT to PSPRCSRQST to allow us to determine how a given nVision report is run and the statistics of that run.
2. There are no direct common keys between these tables, but there is a 3rd table that does contain enough information to allow the joining to occur (PSPRCSPARMS).
3. However, the parameters are concatenated into a single string, which complicates the join SQL.

Okay, now let's figure out how do do this 3-way join. Probably the easiest thing to do is to use the like verb (although a purer solution would be to use substring functions in the SQL instead). The Business_Unit is the -NBU parameter, and the Report_ID is the -NRN parameter, so our SQL will look for both of those parameters and see if that parameter concatenated with the appropriate key value in the PS_NVS_REPORT record is found.

Here's the resulting SQL (which uses PeopleSoft meta-SQL to ensure platform independence... if you paste this into a view in application designer, it will work).

SELECT PRMS.PRCSINSTANCE, NVS.BUSINESS_UNIT, NVS.REPORT_ID, RQST.OPRID, RQST.RUNSTATUS, RQST.BEGINDTTM, RQST.ENDDTTM
FROM PS_NVS_REPORT NVS, PSPRCSPARMS PRMS, PSPRCSRQST RQST
WHERE PRMS.CMDLINE LIKE '%PSNVS.EXE' AND PRMS.PARMLIST LIKE '%NRN' %CONCAT NVS.REPORT_ID %CONCAT '%'
AND PRMS.PARMLIST LIKE '%NBU' %CONCAT NVS.BUSINESS_UNIT %CONCAT '%'
AND RQST.PRCSINSTANCE = PRMS.PRCSINSTANCE

What you can do with this...

Now that we have the SQL or view determined, we can put it to use. Again, what we've done is to identify which processes in the process scheduler table are associated with a given run control or report request (and all the pertinent values stored in each). You can now see all the parameters in the run control as well as the process scheduler stauts and runtimes for each time it was run.

Although this example is specific to nVision because it joins the nVision report request table, you can substitute the query run control record (PS_QUERY_RUN_CNTRL) for the PS_NVS_REPORT table (or other run control records) in its place to do the same thing for other process types.

Here are some things you can do with the results:

• See how often a given run control is run, who ran it, and the run times.
• See which processes are the slowest processing to identify which ones to tune.
• See whether a common set of parameters are frequently run by different people multiple times (to identify whether it would be better to pre-run the reports for them).

One final thing that came up at the UKOUG is to use this as the basis for better managing the running of reports in the scenario where a user kicks of the same report while a report with the same parameters is still processing on the server (this can happen when running to window and the report is slow... the user doesn't have patience, or can happen when with other scenarios when a run control is re-used for multiple sets of parameters). I plan to write a follow-on posting to this.

Labels: nVision, Process_Scheduler

1. Move all reports into the PeopleSoft report repository.
2. Register all reports to PeopleSoft report manager.
3. Move all reports to a 3rd party tool.
4. Register all reports to a 3rd party tool.

So, the first question is: do you want PeopleSoft to be the place where people go to access their reports or another tool? There are several things to consider when determining whether to use PeopleSoft or another tool:

1. With PeopleSoft, the users, passwords, and roles are the same as the application users, passwords, and roles.
2. PeopleSoft does not charge additional licensing fees to use its report distribution framework, whereas 3rd party tools generally charge on a per-user basis.
3. The UI for report manager is generally more limited than other report tools. However, these areas can be addressed through the following packaged services (warning... shameless marketing at the other end of this hyperlink).
4. You should determine which is the primary environment for your users. If the majority of your users aren't PeopleSoft application users, then it may be better to use the 3rd party tool.

The second question is: do you want to move the reports or register them in place? Here is what you should consider when making this decision:

1. How important is interacting with the output? In other words, if you want to provide drilling or page on demand (features that require viewing functionality generally only available when the report is kept in its original repository), then you will want to keep the output in place and register the URL for accessing the instance.
2. How important is it to have a single repository of all your reports? If you register in place with several different reporting environments, then each environment is responsible for storing, securing, and archiving its own content. This can cause an administrative overhead that you may not want to deal with.

Okay, so let's look at how one would accomplish each.

Move all reports into the PeopleSoft repository.

There are a couple of techniques you can follow to accomplish this (and the one you take is primarily dependent on the tools release you are on). Here is a list:

1. PostReport() PeopleCode function.
2. Utilizing process scheduler distribution agent functionality

The first is the easiest one to use, because it is well documented. The PostReport() function (which is only available on PeopleTools 8.4 and greater) allows you to specify a location of a file, as well as the metadata needed to organize it in the report repository (such as who has access to it, and the folder it should be placed in). You can use PostReport() in app engine or in a PeopleSoft page. When used in app engine, you can set up a daemon process to look in a directory for a file and then move it when it finds it (PeopleSoft delivers a sample daemon process out of the box that is also well documented).

The second method is to use the way process scheduler manages output to your advantage (and is the way you need to do it for tools releases that don't have PostReport(). Let's talk a little bit about how process scheduler works.

When process scheduler starts a new process, it creates a unique temporary working directory for it (it containes the process instance ID, so that all running processes get their own directory). All output of programs get put into that directory by default. When the process has completed (either by ending normally and updating the status to either error or successful, or by having the process scheduler detect that the PID of the process doesn't exist any more), all output in this directory is collected and copied to the report repository (which is what happens when the state of a process is "posting").

Therefore, one can move output to the report repository by merely running a process in the process scheduler and copying a file into the default output directory of that process. The distribution settings of the process would be used and the file would be moved to the report repository. This physical act of copying can be done through any of the following methods:

1. Creating a shell script that is registered as a process definition in process scheduler.
2. Creating an app engine program
3. Creating an SQR.

Each of the different tools has its own substitution variable for the standard output directory, and is documented in the process scheduler documentation (but for example, %SQOT is the output directory for SQR).

Now, it's important to note that the distribution rule of the process that does the copying is the rule that is used for putting output into report manager. This means that the list of users who have access to it as well as the folder it shows up in comes from the calling program and cannot be overridden. Therefore, many organizations will write a separate program in application engine that uses the ScheduleProcess PeopleCode function to set the distribution rules and then run the program that does the copying. This calling program is often implemented as a Daemon, which also has logic to figure out which settings to apply to a given file (which could merely be a second file in the same directory with that information in it).

Register all reports into report manager

This is actually relatively easy to accomplish, if you're willing to use integration broker. The report distribution infrastructure was designed to work in an environment with multiple PeopleSoft environments (such as Enterprise Portal, HR, and Financials). To do this, we implemented messages to publish information about a new report when it is posted. This allows the different PeopleSoft environments to subscribe to those messages to have reports from the other PeopleSoft system registered to it. Because PeopleSoft messages are exposed as XML, you merely need to generate the appropriate set of XML messages to register external content.

The messages you need to look at are the following:

• PSRF_REPORT_CREATE
• PSRF_REPORT_DELETE
• PSRF_FOLDER_CREATE

There's also a message for updating a report, but I forget the name (just look for the PSRF prefix and you'll find it). When generating the XML, the most important things to provide are the URL for accessing the content, and the list of users or roles who have access to it.

When the 3rd party system generates the XML message, it merely needs to send it to the integration gateway URL you set up with integration broker.

Move all reports to a 3rd party tool

If you want to move your PeopleSoft reports to a 3rd party tool, there are also several techniques available.

1. Run the report to a directory. Many customers give access to windows directories and secure the directories at the operating system. Others will copy the reports from the directory into the other system.
2. Print the report using a special print driver. This is a technique used by Vista Plus and Cypress. The process scheduler prints the report to the print driver, and then the print stream is consumed by the 3rd party tool, parsed, organized, and stored.
3. Subscribe to the XML messages generated by PeopleSoft distribution agent, and then call the URL to copy the file. Again, this is standard integration broker functionality.

The approach you take is primarily dependent on the functionality available in the 3rd party system.

Register content in the 3rd party tool

There are two techniques you can use to register the URL to access a report from the report repository using a 3rd party tool. They are as follows:

1. Subscribe to the XML messages generated by PeopleSoft distribution agent and register the URL and users.
2. Write a program that reads the data from the PeopleSoft report manager (which are PS_CDM_LIST for the list of reports, and PS_CDM_AUTH for the list of users).

Labels: Process_Scheduler, Report_Manager