Peoplesoft BI Publisher too Pricey? Replacing Crystal Reports with Apex

Have your legacy Crystal Reports for Peoplesoft become an obstacle for your upgrade? Oracle has named Peoplesoft BI Publisher as its strategic direction. Here are some pros, cons, and alternatives.

With the release of PeopleTools 8.55, Oracle has deprecated the long running SAP Crystal Reports, in both Peoplesoft and Campus Solutions. While it comes at no surprise that Oracle would discontinue and SAP feature of one of its ERP suites, customers are still left with a major decision about how to proceed.

Oracle Apex Reporting

Interactive reports are one of the key reasons Oracle Apex is a viable alternative to Crystal Reports for Peoplesoft or PS Campus Solutions. These interactive reports allow you to delegate the task of tailoring, filtering, and customization onto the business users. Check out some of the interactive charts available via Apex here…

In the past, most companies used the same tool for operational reporting as they did for printing Invoices and PO’s. This led to the permutation and to creating many many physical paper based reports that all have very similar information with only minor differences like filtering, pagination, hidden columns, and summarization. This paradigm led to the “many unique reports for every manager” problem.

Interactive Reporting

The new paradigm for operational reporting is the interactive report as well as broader exploratory reporting through business intelligence. Most line-level managers simply need timely access to the information and a shared interactive report is enough.

Interactive reports can be customized bye users, which means filter, break, pivot, un-pivot, summarize, etc. The philosophy is simple, by putting more power in users hands the report becomes more functional and useful, wile reducing the time and effort it takes to develop. It also allows a single interactive report to be used by many different users for different purposes whereas each of these users would have their own custom report with Crystal Reports.

Peoplesoft Reporting

Before we discuss specific alternatives to licensing BI Publisher for Peoplesoft. Lets first define the kinds of reporting requirements that todays enterprises have.

Financial Reporting

Financial Reporting encompasses the external financial statements that a company must generate. Some examples are the cash flows, balance sheets, income statements, and shareholders equity statements. This reporting also includes the notes, press releases, and conference calls regarding periodic earnings. Financial reporting is regulated and already included in PeopleTools so for our purposes it will not be in scope.

Pixel-Perfect Reporting

Pixel Perfect Reporting are those reports we send to customers, suppliers, governing agencies and employees. These reports include Purchase Orders, Sales Orders, Quotes, W2’s, and Pay Stubs. With these reports, we must do page level formatting so that our reports align to pre-printed forms, have headers and footers as well as pagination (or page numbering).

Operational Reporting

Operational Reporting is everything else. With these reports the look and layout is not so important as the information and giving our managers access to the information they need is paramount.

Peoplesoft BI Publisher

Oracle has defined it’s BI Publisher product for pixel perfect reporting as the recommended replacement for Peoplesoft Crystal Reports. For good reason, formerly known as XML Publisher, it provides a powerful and easy to use tool for enterprise reporting. That said, customers using the inexpensive Crystal Reports may be in for sticker shock.

According to Oracle’s periodically published price list, the list price of BI Publisher is $46,000 per processor. Oracle defines the processor as just 2 cpu cores for most purposes. Install BI Publisher on a single, 22 core machine, and the price becomes $506,000 (+22% for Support) before discounts. That price doesn’t include the WebLogic server to run it on either.

Converting Peoplesoft Crystal Reports to Oracle Apex

Step 1: Take Inventory

Analyze your Crystal Reports and rank them by size and complexity. Tag them by the data domain they are accessing and how often they are used.

Step 2: Categorize and Rationalize

Group these reports by similar data sources or queries. Note the layout and filter conditions that are required as well as any parameters required. Remember that in the Apex world you can rationalize many reports into a single, Interactive Report.

Step 3: Prioritize

Prioritize your reports that need to be converted. A seldom used report, or one whose data is already accounted for, can be placed near the bottom of the list.

Step 4: Structure your Queries

Modify your Peoplesoft queries to be useful to the broadest amount of reports. Remove any hardcodes and filter conditions that limited the report in Crystal. This is also a great time to convert your reports to ANSI SQL and to optimize their joins for performance.

Step 5: Add the SQL Data Sources

Once you’ve optimized your query, create the Apex Interactive Report and add the query to the data source. Make sure to preserve any parameters you know you’ll need as Apex page items.

Step 6: Format your Reports

From the Crystal report, note any suppress conditions or conditional styling and use conditions within the Apex Interactive Report. Also, remember to copy any numeric formats and alignments of character formatting.

Step 7: Validation

Validate your reports with test cases from both your source and target systems. Expert the report data into spreadsheets to make sure the totals (amounts and counts) match.

Step 8: User Acceptance

Migrate (with one click) your finished reports to a UAT environment so the business users can perform their validations and acceptance.

Oracle BI Publisher

Oracle Business Intelligence Publisher (BI Publisher, formerly XML Publisher) is an enterprise reporting solution that streamlines report and form generation. One of many primary advantages to using Oracle’s BI Publisher product is the separation of the data extraction procedure from the report layout.

Oracle BI Publisher enables users to create, handle and distribute polished and interactive reports. Oracle BI Publisher also allows users to create authorities forms and electronic funds transfer documentation. BI Publisher makes creating reports efficient and easy, enabling users to set up reports exactly as they would like with the flexibility to export it in a number of different formats.

Top Issues when Converting Crystal Reports to BI Publisher

Heterogenous Data Sets: BIP supports multiple data model types and data sets. You need to recognize the best strategy for each scenario. This would depend on the query kind, the amount of datasets and the links between them.

Report Layout: Each template (RTF, XSL-FO, XPT, Excel ) has its limits – while XPT had limitations in group, RTF has size limitations (21 inches), Excel doesn’t support pivots. It is very important to understand the constraints to pick the appropriate template to satisfy the end-user requirements.

Report Distribution: Operation could be improved by the way how we define the query date set sort and we manage queries. Caching the result of a query and segregating the queries in the template will improve the performance.

Report Parameters: Binding Labels to values would make the report user friendly and reduce errors.

Pivot Grids: The templates have limitations in supporting Pivots/Charts. So good grasp of the chart functionality and XML coding knowledge would be asked to mitigate a number of the constraints.

Top Features for Peoplesoft BI Publisher

The BIP tools (Pivot Grids, Simplified Analytics) that will replace many of your old reports will provide you with fresh, actionable data to make better choices to run your company. For those situations where you need static data, month-end as an example, BI Publisher is your tool of choice.

PeopleTools has created a BI Publisher report user role (XMLP Report User) that enables users to schedule and run reports. Users with this specific function can also open most BI Publisher report definitions in read only manner.
Report developers can delete report definitions from the results grid of the report definition search page.
PeopleTools delivers a copy report definition page that simplifies report development by empowering report programmers to copy report definitions.
BI Publisher for PeopleSoft now enables you to develop PDF reports that users can edit and then submit the changes to be saved in a database. The submittable PDF report functionality is accessible for delivered self service reports and for customer-created reports.
You can shift the data source of an existing report definition. ID area becomes editable when a user clicks the link the data source kind and data source. A choice to cancel info source changes is available, enabling a report programmer to revert back to initial state.
PeopleTools empowers you to include attachments from outside sources with BI Publisher PDF report output. The source attachments are converted to PDF format and then united with the primary report.
Distribute reports using e-mail to recipients that don’t have PeopleSoft user profiles. This feature allows you to distribute BI Publisher reports to outside users whose email addresses are saved in the database, file server, or other place.