Custom Reports

Writing customized reports for WAM


Custom reports can be added and integrated into Oracle Utilities Work and Asset Management (WAM) without changing any of the vendor code. This post explains how to add an Oracle custom report and integrate it into the application.

WAM uses a defined parameter form to call each report. The parameter form builds the ‘where clause’ and the ‘order by’ parameters and passes them to the report. The report then appends them to the SQL statement when running the report. If the report is called from a custom menu (defined in the Module Administration – Forms function), then the menu will pass a ‘where clause’ (consisting of the primary key equaling the current record) into the report. All of this functionality resides in the libraries srpt.pll (for the reports) and srpi.pll (for the form).

When the report is run, the form saves the parameters into the table sv_report_queue. This gives a log of what each user has ran, as well as the status of the report.

Setup of development environment

Before building customized reports for WAM, you need to install Oracle 10g Developer. The system is written using the latest verson, so, while other versions of the report developer may work, it is best to use the latest version.

1 – Install Latest version forms and reports (10 not 6)
2 – Create a directory for forms and reports development (j:\spl)
3 – Create a shortcut for the report developer and form developer.
4 – For each shortcut, change the ‘start in’ to the directory you created in #2.
5 – Copy all forms and reports you will work on, as well as srpt.pll, srpi.pll and rptmstr.fmb found on the CMMS server to the directory you created in #2.

It is very important that all your forms and reports development be done in one directory. If you use multiple directories, you will need to create a shortcut for the Forms and Reports developer for each one. You will also need to copy srpt.pll, spri.pll and rptmstr.fmb to the new directory. If you don’t, the libraries and other files will not attach correctly.

Developing reports with Oracle Reports

When developing a custom report, you can start with an existing seeded report, or another custom report. By doing that, you get all the parameter and other information needed to run the report. You can do this be copying the existing report to the new custom report.

Before starting to change the report, you first want to make sure that it will compile. Do this by going to file/connect and then connect to the CMMS database. Then go to Program/Compile/All to compile the report.

The first thing to change is to update the filename of the new report in two places inside the report developer. First, change the report ID parameter default value to the new name. Secondly, change the name in the top level of the navigator.

You then can start to customize the report. To change the SQL for the report go under ‘Data Model’ in the navigator. To change the layout, go under ‘Paper Layout’ to change it.

For the report to run from the form, you will need to make sure that each parameter in the parameter form is a valid column in your SQL statement in the report. If the where clause or order by are invalid, you get the generic error FRM-41214 ‘Unable to run report’. Please see the section under forms for more on the parameters.

You can run the report from your desktop to test your changes.

NOTE: It is very easy within Oracle Reports to invalidate the Report especially when doing the ‘Paper Layout’. If you do this, you may not be able to undo or recover the invalidated version. You will need to restore a backup. To deal with this, create a backup directory and make FREQUENT backups of the report you are working on. You can rename them name_v1.rdf, name_v2.rdf, etc. to keep multiple backups. I would recommend you backup at least every half hour if you are working on a report.

That being said, 10g is much more stable than report 6, which EBS uses.

If you do the steps above, the following about attaching the library will not apply.

If you get errors about SRPT.PLL missing when loading the report, then you will need to attach SRPT.PLL You do this by selecting ‘Attached Libraries’ and clicking on the green plus sign and choosing SRPT.PLL. Say ‘YES’ to the question about removing the path.

Compiling and uploading

When your report is complete, you will need to compile and export the report. To compile, select program – compile – all from the menu. To export your report, select file – export. Export the report as a .REP with the same name as the RDF. You will need to move the REP to the server in order for CMMS to run the report.

If the report does not run from the application, you will get the FRM-41214 error. To see the actual error being returned by the report, you will need to run it directly from the browser. The URL to use is:

http://SERVER:PORT/reports/rwservlet?report=D:\SYNERGEN\CMMST\forms\c_rpt000.rep&destype=cache&desformat=html&userid=student15/password@cmmst

Where c_rpt000.rep is your report and the userid information is your login and password to the database. This will return the actual error that caused the report not to run.

Parameter Forms

The parameter form is the screen that shows up when you run the report, and is what calls the report. The form uses the field names and data to build a ‘where clause’ and ‘order by clause’ and sends them to the report as parameters.

A single form file (FMX) can store multiple parameter forms. Each parameter screen is a separate canvas. You can use rptspl1.fmb (comment on this and I will send) as an example of a parameter form. You can either add parameter forms to rptspl1.fmb or create a new parameter form.

To create a new parameter canvas, do the following:

1 – Go to the data blocks section and create a new data block. Do not use the wizard.

2 – Ctrl-click and drag each of the items under the REPORT_TEMPLATE data block to the new data block. Make sure to subclass B_RUN_REPORT and copy the others.

3 – Add any parameters you need to the data block.

4 – Create a new window object. Name it the same as the form

5 – Create a new canvas using the wizard. Choose the correct data block. When you get to the list of fields, choose them all but the plant. Select the B_RUN_REPORT parameter and make sure that it is a push button and has a size.

6 – Arrange the parameter form so it looks good.

7 – Open the property pallet for the canvas and make sure the window is the same as created in #4.

8 – Save, compile the PL/SQL and compile the module.

Field naming conventions

The libraries use the field names on the form as column names on the form, and to identify the order by field. You need to name the fields in the data block and canvas as they are in the database. The field names can be prefixed to identify the operator to use. Below are the prefixes you can use

Prefix on field name
Operator

No prefix
=

OPER_
Drop down list of operators

ORDER_BY_TEXT
The Order by clause

LE_
<=

GE_
>=

EQ_
=

LIST1_
Like

For OPER_ the data is the operator, you need to have another field for the parameter. The OPER_ field type needs to be a pop list.

For Date fields (end in _DATE) the library will set it to get the whole days for LE_ and GE_. So, if you enter 9/1/2007 as a start date and 9/30/07 as the end date, the system will add the code: Date_col >= 9/1/2007 and Date_col < 10/1/07. This way, it gets the whole last day.

To test these, use the dummy report (c_rpt000.rdf) (comment and I will send to you) that does not process the parameters, but prints them on the first page of the report. This way you can make sure your parameters are correct.

You cannot run the form on your desktop; you will need to load it to the test server.

Reports Admin

Report ID: This is the report (RDF) Name.

Select Block: This is the Canvas name in the parameter form.

Location: This is the Parameter Form (FMX) Name. The LOV is in Code table 95.

Type: Does not seem to make any difference. Set it to Listing for now.

Group: This is used to help find things in the responsibility form. Choose the most appropriate.

Custom Menus and Reports

Reports can be put on custom menus and ran using the ‘Use Primekeys in where clause’. This skips the parameter form and runs the report using the information contained in the form that calls the report. In order to use the primekeys and bring up a parameter form, you would have to create a custom version of srpi.pll and use it with the form.

To make this work:

1 - Make sure that the from clause of the select statement of the report contains the table that is being viewed in the module.

2 – Register the report.

3 – Create a custom menu for the form. Go to ‘Module administration – Forms’. Find the form you want to add the menu to, click on it, click on custom menu. Add the menu name in the ‘menu title’ field. Enter a sequence number (use 10, 20, etc.). Enter the submenu name into ‘Submenu Title’. Click the ‘display’ box. Choose ‘Report’ for the ‘type’ and enter the report name for the Action. Set ‘Use Primekeys in where clause’. Save your work.

4 – Grant access to the custom menu for the responsibilities that are to run it. Goto Responsibilities, Select the responsibility, choose Custom Menu for the type and add the form to the list.

5 – Test the report. It is helpful to put the raw generated where clause in report to validate that the where clause is passed correctly.