20 October 2010

Simulate Report Alerts in Crystal Reports 2008 Server

Working with Crystal Reports can be tricky.  Anyone who publishes the reports on a BOE Server or Crystal Reports 2008 server has to relearn some of the aspects of the reports to create similar functionality to the desktop application.

Recently I was asked to implement an alerting scheme in a crystal report that was published to the server.  Since online reports do not support alerts the same way desktop reports do, this is a tricky problem.

The way we decided to do it was to put the alert in the report header.  We then tested for the condition that would trigger an alert.  If the condition was triggered, we made the report header visible.  Otherwise, we hid the report header.

This is accomplished with three formulas:
  • The first formula declares the test variable and initializes the value to false.
  • The second formula is placed in the details section of the report.  This formula tests for the condition and, if triggered, sets the test variable to true.  This formula uses the Crystal WhileReadingRecords function. 
  • The final formula evaluates the test variable.  If true, the header is made visible (with a warning message), otherwise it is hidden.  This function uses the Crystal EvaluateAfter function.
Here's exactly how it works.

Create a new formula called Declare Globals.  The code in this formula is as follows.  Place this formula in the header and make it invisible.  T is the test variable, and the test is initialized to false.

@Declare Globals code - placed in header

global booleanvar t;  // t is for test
t:= false;

Next, create another formula called Test Records.  In this example, the test is done in SQL, but it could be done in the report.  The only result of the test is to set the test variable to true if the test is triggered.  This code should be placed in the details section of the report and again made invisible.

@Test Records code - placed in details

global booleanvar t; // reference the variable declared in the header

if ({tblData.bad_data} = -1) then t := true;

Finally, go into Section Expert on the report and choose Report Header > Common tab > Suppress (No Drill Down).  Un-check Suppress and click the formula editor button next to Suppress.  Evaluate t and show/hide the report header based on the value of the test variable.  The report header should have the warning in it in big red letters to alert the user.

Code placed in the Report Header

global booleanvar t; // reference the variable declared in the header

evaluateafter({@Test Records});
not t;