Enhancing an O4W Report with the "Run First" Option (Reporting and Printing,Web)

The O4W Report tool, in addition to all the flexibility it normally provides the user when generating a report, also allows you to specify a statement to "run first" before any other sort/selection is performed.  This can be any R/List command that generates an active list, so (for example) the report designer could pre-select data for only particular users, or for a particular month, or activate a previously saved list, before the O4W Report runs any user-entered selection criteria.

What is less well known, however, is that the "run first" statement could be the name of a stored procedure; in the O4W Report Wizard, specify the stored procedure as the 'run first' command, preceded by the "@" symbol (for example, "@MyRunfirstRoutine"). The stored procedure must be defined to accept three parameters - the sort statement that the O4W report would have run, the selection statement that the O4W report would have run, and any user-entered parameters.  The stored procedure should then generate an active select list, which the O4W Report tool will use to build its output.

By creatively using a stored procedure as the 'run first' command, it is possible to have a single O4W Report that performs several different, but related, functions.  The 'run first' procedure is not limited to just the values passed in to it; in fact, since it runs in the O4W context, it can use any O4W commands. This includes the ability to retrieve any passed values in the invoking URL, with the O4WGetValue API call.

For example, assume you have an O4W Report that lists information about orders on your system.  You may wish to have separate reports to list open orders, closed orders, backorders, and 'hold' orders; these all should display the same information, but select different sets of data.  Normally, you would have to create 4 different O4W Reports, or prompt the user in the O4W Report for the type of report (and then use the O4W Report's selection process to select only those appropriate records); however, you could alternatively use a single report, without any user prompts, and invoke it with 4 different parameters on the URL, which the 'run first' procedure would use to determine what to select.

The URL for your report might look like:

http://www.mysite.com/o4w/oecgi3.com/O4W_RUN_REPORT?REPORTID=ORDER_REPORT

And you could append a new parameter, named "TYPE", with a different value for each type of report:

http://www.mysite.com/o4w/oecgi3.com/O4W_RUN_REPORT?REPORTID=ORDER_REPORT&TYPE=OPEN

http://www.mysite.com/o4w/oecgi3.com/O4W_RUN_REPORT?REPORTID=ORDER_REPORT&TYPE=CLOSED

http://www.mysite.com/o4w/oecgi3.com/O4W_RUN_REPORT?REPORTID=ORDER_REPORT&TYPE=BACKORDER

http://www.mysite.com/o4w/oecgi3.com/O4W_RUN_REPORT?REPORTID=ORDER_REPORT&TYPE=HOLD

A 'run first' procedure (OrderRunFirstProcedure) can examine the 'TYPE' value and select the appropriate records based on what has been passed:

SUBROUTINE ORDERRUNFIRSTPROCEDURE(SORTSTMT, SELSTMT, PASSED_INPUT)

$INSERT O4WCOMMON

$INSERT O4WEQUATES

*

whichType = O4WGetValue("TYPE")

STMT = SORTSTMT

BEGIN CASE

            CASE whichType = "OPEN"

                        STMT = "SELECT ORDERS WITH STATUS = 'O' BY ENTRY_DATE"

            CASE whichType = "CLOSED"

                        STMT = "SELECT ORDERS WITH STATUS = 'C' BY FILLED_DATE"

            CASE whichType = "BACKORDER"

                        STMT = "SELECT ORDERS WITH STATUS = 'B' BY ENTRY_DATE"

            CASE whichType = "HOLD"

                        STMT = "SELECT ORDERS WITH STATUS = 'H' BY ENTRY_DATE"

END CASE

CALL RLIST(STMT, 5)

RETURN 0

Thus, four different reports can be output from the same O4W Report definition.  Note that, for more advanced users, it is even possible to add output to the generated O4W page (for example, loading additional scripts).  In fact, through creative use of the 'run first' hook, the O4W Report can be customized in any number of powerful and unusual ways.