How to address advanced reporting requirements

This topic provides additional tips, guidance, and information to help you meet unusual or demanding reporting requirements.

Process Commander provides multiple facilities to support reporting:

• Report Definition rules, for creating reports that can be customized through direct interaction with the Report Viewer
• Report Wizard, for guided assistance in creating V5 reports covering assignments, work objects, or data objects
• List view rules, for list-based reports covering instances
• Summary view rules, for two-level reports with selected counts or computations at break levels
• The Obj-Browse method, for retrieval using SQL criteria
• Personalization and customization of existing reports
• An optional reports database, a full or partial copy of PegaRULES database tables dedicated to reporting, to reduce demand on production database tables
• Charting capabilities

However, management requirements or external requirements may require formats, layouts, computations, or interactivity that can be difficult or impossible to achieve with these tools.

1. Process Commander uses a Standard Query Language database.

A PegaRULES database is hosted by Microsoft SQL Server, Oracle, IBM DB2 or other popular database software. As initially installed, the database contains about 50 tables, each with a straightforward layout. Except for the Storage Stream column (pzPVStream), column names match Process Commander property names.

You can meet reporting needs with stored procedures, Microsoft database tools, Oracle tools, or third party vendor products such as Crystal Reports from Business Objects.

This database is sometimes known as a rulebase, although it contains more than rules.

2. Use list-based reports support operational needs.

Reports that support first-line and second-line managers in their work typically emphasize currency and speed rather than complex layouts, complex calculations, or complex formats.

Use the Report wizard or list view rules to meet operational needs for near-real time data.

3. Exploit the retrieval and sorting power of database products.

Execution of a list view or summary view rule requires processing by both database software and Java programs. The database software is more efficient at almost every task. Even when one server computer hosts both the PegaRULES database and the Process Commander server, it is important to maximize use of database software, and reduce the JVM workload.

Process Commander converts information on the Content tab directly to SQL; processing specified on the Organize tab or other tabs occurs within the JVM. Although SQL processing is limited to exposed properties, utilize SQL selection and sorting over JVM sorting in your reports when possible.

Oracle and competing database products reflect a large vendor investment in power, speed, and sophistication for searching, retrieval, and sorting. In addition, these products let a database administrator tailor a database with caches, indexes, operations, translations, and so on. An experienced database administrator can use these tools to improve performance and simplify report generation, especially for reports involving thousands of rows.

4. Declare Index rules can help.

Exposed columns correspond to properties at the top-level of a saved object. Properties within a repeating group or embedded page are not exposed. In many such situations, a Declare Index rule can support a new index structure that directly supports a reporting need.

For example, assume your application includes a customer master file that includes up to three mailing addresses for each customer, stored in an embedded page. A report request to retrieve all customers in Zip Code 90210 cannot be processed as SQL. However, with one Declare Index rule, Process Commander can maintain this information in a dedicated concrete Index- class and a corresponding dedicated database table, making retrieval simple and fast.

5. Database views can help

A database view can simplify reporting, especially with properties from two or more classes that are in separate class groups.

6. Use a reports database

Using a reports database reduces the processing demand of reporting on the production PegaRULES database, and allows your database administrator freedom to implement optimizations for reporting access that do not affect application processing.

7. Use BIX (Business Intelligence Exchange) for additional reporting needs

BIX allows rapid data extraction from the PegaRULES database, for later import into a data warehouse or other reporting facilities.

8. Excel formulas or macros can help

Reports from Process Commander are based on a single pass over the row details. Averages, trends, maximum values, and minimum values can be computed in a single pass. Many other computations require multiple passes or multiple sorts. As Process Commander reports can be exported to Excel for printing and advanced formatting, consider using Excel for such final computations.

9. Reports work best with 10,000 or fewer retrieved rows

If your business requires reporting on larger numbers of rows, consider using the Business Intelligence Exchange facility to export data from the PegaRULES database into a data warehouse. GRP-446

	Business Intelligence Exchange, reports database, schema, Storage Stream
	About Declare Index rules About List View rules About Report Definition rules About Summary View rules How to report on multiple rule types How to use a reports database Obj-Browse method Working with the PegaRULES database

Reports category