The Attivo Blog

In addition to being "The Event Manager" here at Attivo, I also write many Crystal Reports, most commonly for ISOMan.  ISOMan is always asking me for a new Crystal Report displaying a different collection of Product Information for him to present to our clients' Quality and Product Managers. Sometimes we deploy these reports via some means of on-demand distribution, like the Crystal Enterprise Suite or the HTML Wizard Recrystallize.  And sometimes we deploy the reports via a scheduled job in Event Manager, sending 1 or more daily reports each afternoon at the close of the workday. Event Manager, as you may know, is fully capable of automatically running any Crystal Report and attaching them as .pdf files to an email, before spinning that email off to a predefined recipient. But what if you ONLY want that report to send off when there is data to display in the report? What if the range in data for the report varies from 0 to 100's of results? In this instance, you will need to create a conditional/scheduled report. Anyone with any experience in Event Manager knows that the first step is to create a query. We need to know WHEN to run the report.  So we begin by creating the query, adding/ linking the tables, and adding the columns and filters.  However, do not spend too much time on the columns. Why? Because we won't be needing any database columns beyond the unique ID to tell our scheduled report to run.  The columns you pick will soon be deleted once we have verified our data.  Once you have created your filters and your preview displays only those results you are looking for, click the SQL tab and check the box stating that you will manually edit the SQL for this query.  First, break your SQL up into it's three main components, the 'select' statement, the 'from' statement, and the 'where' clause. Next, delete everything in the select statement EXCEPT the unique column you selected earlier.

select imitmadt_sql.id

We want to COUNT the amount of times this id comes up. Remember, our REPORT which will get triggered by this query will display all the required columns. This query is to determine IF the report should be run ONLY. Our short query becomes:

select count(imitmadt_sql.id)

Skip the 'from' section and proceed to the 'where' filters you added earlier.  Assuming you created the filters correctly using the Event Manager Filters tab, you will just need to add one more 'filter' to the very end of your SQL statement.

where... imitmadt_sql.activity_cd='F' AND imitmadt_sql.aud_action='A'

Becomes

where... imitmadt_sql.activity_cd='F' AND imitmadt_sql.aud_action='A' having count (imitmadt_sql.id)>0

Note that there is no AND between the last 'where' clause and the new 'having' clause which includes the same count formula we used in the select statement earlier. This keeps this query from triggering when there are no results! Click Preview and confirm that your results should look something like this where '3' is the number of hits that came up the FIRST time you Previewed this query:

"Column1" 3

Click Save and Close and go to Events. Create a new Event that uses the query you just made and select the Reports tab. Add the report that you wanted to be conditional but didn't want to send with zero results. Remember to set the Output Type/Style. Finally, draft your Email in the email tab, add subscribers in the subscriber tab, and schedule the report. That's it, no more reports without data! If there is no report sent, your recipients will know that there was no data generated by that report for that day.