Day 5 2007 OOW – Advanced Reporting Techniques for PeopleSoft Enterprise

By Larry Grey • November 16, 2007


This was the same session I gave last year at Open World. Jodi Runstadtler, the development manager for Reporting Tools gave it this year.

Instead of providing a set of development tips and techniques, Jodi went through the most asked questions she saw from the support as well as other things that she felt that people should know. For those who already read my Reporting Tools Roadmap session entry, you’ll see several circumstances where I deferred providing more details to this blog entry.

XML Publisher Data Sources
Is there a difference between different supported data sources for XMLP?

  • PS/Query-Rowsetsd
  • XMLDoc
  • XML File

Relational data services, such as PS/Query and Rowset will involve processing to align relational with XML structure. (recommend to extract data into XML yourself). Query does create the XML for you, but rowset can be an issue. Even XML doc goes to XML file before going into XMLP.

Bursting in XML Publisher

How do I do Bursting in XML Publisher? A common use case is needing to run reports for every department in a global operations.

You will want to design your report for bursting. The fewer transformations you have to do to the data to get it in the better it’s going to perform. Definitely make sure you sort by the fields you’re grouping on, so the XML file will be easily generated.

When bursting, XML publisher allows you to select a tag in your XML data for spliting up a single report into multiple. This tag must be at the highest level repeating group in the XML data. If you are using Query, this means that you are limited to bursting on a single field. Workaround is to concatenate in an expression.

One additional feature is that you can have different templates by values you’re bursting on, so that different sets of data can have the results formatte specifically for them. One nice thing is that you don’t have to enumerate every value you burst on to identify which template you want to use. You can set a default template and override where you want to.

Query Performance

After upgrading from 8.3 to 8.9, many customers saw that their Query security joins started causing performance issues.

Prior to 8.44, the security record was joined once for two records with the same one. This caused data to be missing in some circumstances. If you do not want multiple inclusion of security join (insert into psversion(Iobjecttypename, version) values (‘qryselfj’,1))

XML Publisher Templates

XML Publisher for peoplesoft means we have more decisions regarding report templates. How do I choose?

PDF templates are more limited than RTF templates. PDF templates don’t support sub-templates, runtime paramters, translation files, output formulas, flexible field-data mapping (xpath vs simple name matching), charts, or custom/dynamic output where RTF does.

PDF performinga better, though, for large reports. Keep in mind that you need professional version of adobe to create/modify a pdf template that you’re not getting from an external source. Mentioned can generate PDF output from RTF, though. Finally, 4 charts are available with bi publisher.

Subtemplates in XML Publisher

What are subtemplates?

Subtemplates allow defining re-usable templates that are imported into a parent template (similar to an include statement) . Each primpary template can import multiple subtemplates and they can include images, text, xsl templates, etc.

They are stored in content library, so there is no definitional link to primary templte (imported).

When previewing, you need to know that there are two places to do this. One is from within PeopleSoft PIA and the other is directly in the template builder within XML publisher. This is because the template designer works on a sample of the data and is stand-alone when it is launched (it doesn’t know about PeopleSoft). Whereas the preview in PIA is the run-time environment within PeopleSoft.

Using Subtemplates as Headers

This is probably the most important reason to use subtemplates, because organizations will want to define a standard report heading. Unfortunately, word headers and footers do not support variables, which is how this is done. To do this, you need to code the highlighted area, which is the string that pouts the form/field in the header/footer. This is a manual coding step.

The following blog entry shows the sort of thing you need to go through to embed external data into word when word doesn’t automatically handle it from a UI perspective.

Production Reporting
We need production reporting, but the report processing is consuming my server.

For Query, you should look at Query administrator. If you have administrators who don’t want to use a PeopleSoft page, you could use PeopleCode APIs to examine the query statistics. These API calls mirror the fields in the PSQRYSTATS table (which you could also query to get this information). However, delete is an API call available, which isn’t in the table and you wouldn’t want to do yourself in SQL, because PS/Query has a lot of references.

Finally, Jodi mentioned performance monitor, where teh 355 event shows real-time and historical performance.

Although this is valuable information, I don’t consider it Production Reporting, where you have reports run automatically on a schedule and you have to distribute results to end-users. Did I mention we have a product that does that?

Office 2007 and nVision

Oracle is now supporting Office 2007 with nVision. However, there are some issues with it.

Microsoft has changed the file format to be a standard XML format. Once a file is saved in this format, it is not compatible with older versions. Because of this, when you migrate to Office 2007, you will have to make sure the version of Excel on your process scheduler servers matches those on the desktops of the end-users. Office 2007 save this as XLSX.

Organizations will also need a specific version of XMLP for office 2007.

Migration of XMLP between environments

When I migrated my XMLP reports from test to produciton, the translation files were missing.

You will need to follow sequence for doing this. Need to include both the data source defin query. Select XMLPDataSrcDefn (also select query) Select XMLPReportDEfin to include all components. A report may incorporate multiple FileDefnobjects… tempalte data, translations, pdf maps.

Deletion and cleanup. To delete XML Publisher metadata objects, use project delete. Sometimes can cause orphaned definitions, so they created an app engtine progrm to clean it up. Identify orphans, and clean up orphans.

Picking the Right Reporting Tool

This is a common question that I used to get hit with all the time as well. When should one use each reporting tool?

The criterion for this falls under the following categories:

What is the structure of the Report?

  • Forms-based?
  • simple tabular?
  • sub report?
  • matrixed?
  • Does it include application metadata?

What are the formatting requirements?

  • Pixel Precision?
  • MICR?
  • Graphics/logos?
  • Charting?

How is the report being run?

  • reporting with logtic/processing?
  • scheduled on temand?
  • ad-hoc?
  • bursing?
  • drilldown,?

What platforms will you use?

  • Windows
  • Unix
  • OS/390
  • Excel Integration?

She showed a matrix of tools and which of the above fe atures are suppported to help through the process.

Crystal and Hyperlinks

After the session, Sarah Hahn from the Benton Public Utility District recognized me from presenting this session at the Puget Sound Oracle Users Group meeting earlier in the year. She wanted to know more about drilling from Crystal.

Although I plan to record a demo of this and write a blog entry, here’s what you would do:

  • FInd the element in the crystal reort that will have the hyperlink attached to it.
  • Select it and use the Insert Hyperlink menu in Crystal.
  • Instead of typing a Hyperlink into the edit box on the dialog, you will want to click on the button next to the edit box to open the formula editor (it has an X>)
  • Finally, you compose the text of the hyperlink by using field references, string functions, and text.

Labels: ,

Stay Updated

One Reply to “Day 5 2007 OOW – Advanced Reporting Techniques for PeopleSoft Enterprise”

  1. “To delete XML Publisher metadata objects, use project delete. Sometimes can cause orphaned definitions, so they created an app engtine progrm to clean it up. Identify orphans, and clean up orphans.”
    ==> could you clarify? Which app engine? Is it the same as the “Delete orphan File Attachments” under PeopleTools > Utilities > Administration > Copy File Attachments ?

Comments are closed.

Request a Demo