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