Alerting You to Alert Reporting

By Susan Trommer

For a moment pretend you are a product manager for some of the latest and greatest technology accessories. Each month you receive an inventory quantity report containing information on product inventory and whether the quantity for each product is above target limits.

You don't know if your products exceed the inventory quantity limit until you open the report (Figure 1) and analyze the information for the products for which you are responsible. This is one of the business intelligence reports you may dread receiving and opening.

I think most of us can relate to this scenario one way or another within our job responsibilities. In today's business climate, cutting costs and increasing profitability is critical. Organizations create and distribute reports for evaluating and taking action when management-defined conditions are not met. The evaluation can largely be automated using "alert reporting."

Alert reporting automates the evaluation of a defined condition to determine whether a report should be distributed. Individuals receiving the report know before they open the report that there is an exception in their area of responsibility.

Alert reporting is an integrated feature of WebFOCUS Managed Reporting and ReportCaster. An alert report is a type of Standard Report within Managed Reporting that can be distributed by ReportCaster. In addition, ReportCaster's burst capability sends targeted information to the people who need it. So there is less information for each person to analyze.

So how do you get started with alert reporting? First you need to be licensed and have installed and configured both Managed Reporting and ReportCaster. Once you have Managed Reporting and ReportCaster installed and configured, an alert report can be created from within Developer Studio or the Managed Reporting (applet) user interface using the Alert Wizard.

The Alert Wizard's first screen is a summary of the steps the Alert Wizard will take you through. The steps are as follows:

1. Create a test to determine if the alert should be triggered.
2. Create the report to be distributed.
3. Optionally attach a Two-Way Email.
4. Specify distribution options for the alert.

Screen 1 shows the first step to create the alert test. An alert test is either a WebFOCUS Test that is a simple TABLE request you create to define the condition to test for or a Test for File Existence of a file accessible to the WebFOCUS Reporting Server to which the alert test is submitted.

If you opt for a WebFOCUS Test and select Next, the Alert Wizard asks you to create the alert test using Report Assistant or the editor. The version of Report Assistant invoked does not have field styling, heading, footing or report options (such as OLAP and On-demand Paging) because the alert test determines whether a specified condition is true. It is not the report to be distributed.

The most important component of the alert test is the condition (WHERE statement) you create. The condition will determine if the alert is triggered (true). If you elect to use the editor keep the TABLE request simple. The TABLE request should contain the fields to evaluate the condition, the condition (WHERE statement), and ON TABLE HOLD. That's it. When you save the request, validation will be done to make sure you have included the ON TABLE HOLD statement.

Once the alert test is created, the second step is to create or select the report to be distributed. Again you have the option of using Report Assistant to create the report and this time you have all the functionality available for styling and report options. You also have the option to select an existing Managed Reporting report that resides in the same domain.

One thing to keep in mind is the flexibility of editing the request for the report to be distributed. If you choose to create the report to be distributed using Report Assistant from within the Alert Wizard you have to go back through the Alert Wizard to edit the report. This is a little tricky because you need to select the New WebFOCUS Report option again to edit the request you previously used Report Assistant to create. This is because the alert test and the report to distribute are within a single Standard Report (FEX).

Figure 2 shows code sample of an alert test created with the editor and the report to be distributed created with Report Assistant. To keep the code example simple I didn't include any report heading or conditional styling as in Figure 1. The alert tests for products that have an inventory quantity greater than 20,000.

Figure 2 -* Created by Alert Wizard -* version 1.0 -* start of the test report SUM CENTINV.INVINFO.QTY_IN_STOCK BY CENTINV.INVINFO.PRODCAT BY CENTINV.INVINFO.PRODTYPE BY CENTINV.INVINFO.PRODNAME BY CENTINV.INVINFO.PROD_NUM WHERE (CENTINV.INVINFO.QTY_IN_STOCK GT 20000); ON TABLE HOLD END -* end of the test report -* start of the test -RUN -IF &LINES EQ 0 GOTO ALERTEXIT; -* end of the test -* start of the output report -* Created by Report Assistant -* HTML Tool -* Created by Report Assistant -* FF Line do not change this line! Field Name -* FF Line do not change this line! Alias -* FF Line do not change this line! Format -* FF Line do not change this line! Segment -* FF Line do not change this line! displayTree=0 TABLE FILE CENTINV SUM CENTINV.INVINFO.QTY_IN_STOCK BY CENTINV.INVINFO.PRODCAT BY CENTINV.INVINFO.PRODTYPE BY CENTINV.INVINFO.PRODNAME BY CENTINV.INVINFO.PROD_NUM WHERE (CENTINV.INVINFO.QTY_IN_STOCK GT 20000); ON TABLE SET HTMLCSS ON ON TABLE NOTOTAL END -* End Report Assistant -* end of the output report -* start of the options -* TWO_WAY_EMAIL= -* TWO_WAY_EMAILDOMAIN= -* TWO_WAY_EMAILDESCRIPTION= -* TWO_WAY_EMAILFOLDER= -* end of the options -ALERTEXIT -* End Alert Wizard

After the first TABLE request in the code sample there is an -IF statement checking if &LINES is GT 0. This is the check that tests if the Alert test is triggered (true). If the number of lines is greater than 0, there are records that satisfy the condition and the report is distributed. If the number of lines is 0, there are no records that satisfied the condition and the second TABLE request for the report to distribute is not run.

Figure 3 shows a code sample for an alert with the alert test created with Report Assistant and an existing Standard Report is selected for the report to distribute. Note the -INCLUDE of the existing Standard Report. This Standard Report can be edited without having to go through the Alert Wizard.

Figure 3 -* Created by Alert Wizard -* version 1.0 -* start of the test report -* Created by Report Assistant -* HTML Tool -* Created by Report Assistant -* FF Line do not change this line! Field Name -* FF Line do not change this line! Alias -* FF Line do not change this line! Format -* FF Line do not change this line! Segment -* FF Line do not change this line! displayTree=0 TABLE FILE CENTINV SUM CENTINV.INVINFO.QTY_IN_STOCK BY CENTINV.INVINFO.PRODCAT BY CENTINV.INVINFO.PRODTYPE BY CENTINV.INVINFO.PRODNAME BY CENTINV.INVINFO.PROD_NUM WHERE (CENTINV.INVINFO.QTY_IN_STOCK GT 20000); ON TABLE HOLD ON TABLE SET HTMLCSS ON ON TABLE NOTOTAL END -* End Report Assistant -* end of the test report -* start of the test -RUN -IF &LINES EQ 0 GOTO ALERTEXIT; -* end of the test -* start of the output report -* Description at creation time -**Inventory Quantity Exception Report -INCLUDE app/inventor.fex -* end of the output report -* start of the options -* TWO_WAY_EMAIL= -* TWO_WAY_EMAILDOMAIN= -* TWO_WAY_EMAILDESCRIPTION= -* TWO_WAY_EMAILFOLDER= -* end of the options -ALERTEXIT -* End Alert Wizard

I recommend selecting an existing Standard Report because it is easier to access Report Assistant to make changes and it is also available to run interactively or schedule independently of the alert.

The next two steps the Alert Wizard takes you through are the distribution options. If you do not have Two-way Email installed and configured, the options for attaching a Two-way Email are disabled. Select Next and you have the option of scheduling the alert test now or later.

To schedule the Alert test now, select the Schedule button to launch the ReportCaster Developer and Administrator interface. Select the Create a new schedule option to call the scheduling tool. Within the scheduling tool's Task tab, select to schedule a Standard Report and select the alert report you created.

In Screen 2 note that there is an Alert button to the right of the Advanced button. The Alert button invokes the Alert Schedule options to specify how to reset the alert after it has been triggered. The Automatically Reset option continues to run the schedule at the specified time and checks to confirm that the alert test is no longer true before the alert test is evaluated again. It is best to use this option when you do not want to receive the alert report again until after the exception condition has been addressed and occurs again.

For example, when the quantity in inventory is greater than 20,000 the alert is triggered. You do not want to receive the report again until after the inventory is less than or equal to 20,000 for all products and then goes back over 20,000 again.

The Continue After Alert option will continue to run the schedule at the specified time and distribute the report for each time the alert condition is true. It is best to use this option when you want to be notified on the interval specified in the schedule that the alert condition is true.

The Deactivate Schedule After Alert will deactivate the schedule after the alert is triggered. The schedule has to be activated again for the alert test to run after the alert is triggered. It is best to use this option for one-time alert tests.

The last option is to Delay the alert test for a specified period of time. It is best to use this option when you want to allow a specific period of time to address the condition but want to be notified again if the condition has not been addressed. As you complete the creation of the schedule, remember to consider the burst option to distribute specific report values instead of the entire report. When a report is burst, the distribution information must be provided in a distribution list, distribution file or dynamic list for e-mail distribution and an access list for library distribution. The e-mail and library distribution options are the most effective for alert reports because of the push vs. pull notification via e-mail.

I hope this article has effectively alerted you to alert reporting and you can identify areas within your organization were alert reporting can be useful. There have been some new feature requests in the alert functionality area including extending this capability to WebFOCUS Server Procedures and Managed Reporting end users. If you are interested in these or other enhancements related to alerts, please let me know by either opening a case with Technical Support or sending me an e-mail at Susan_Trommer@ibi.com.