Using Crystal Reports For Automated Alerts

Have you ever been asked to automate the identification of errors in data? As an administrator, what if you could automatically identify reports in the SAP Business Objects CMS that exceeded 100 MB in size or users with more than 15 instances of any given report? What about from a reporting integrity perspective…it would be fantastic to identify reports with orphaned objects before a user encountered an error.

Using the inherent capabilities in SAP Business Objects Enterprise, organizations utilizing Crystal Reports can easily take advantage of Alerters to automate these types of questions. This capability, coupled with the rich metadata provided by the Sherlock suite of products, creates an awesome tool for admins to get proactive about monitoring the platform.

Let’s get started by building a new Crystal Report. In this illustration, we are going to leverage the Sherlock universe to identify the number of reports stored in the file repository server in excess of 100 MB in size. It doesn’t matter if it is in the input or output file repository server….but it could if we wanted it to.

blog001001

In this query, I simply added the report’s object ID (and the report’s object name) for fun, also known as the SI_ID when using the Query Builder. Use the Total Object Size in MB object as a filter, effectively building a HAVING clause, to identify all report object IDs greater than or equal to the numeric value 100.

blog001002

Run this query. The great thing here is, there is really no need to add any results or aggregations to this report. No need for the server to do any thinking to format this report. So with a result set that now ultimately lists all unique report object IDs that are larger than 100 MB in size, we need to write the rule to tell us what occurred. With the report open, click on the Report menu item and select Alerts/Create or Modify Alerts.

blog001003

Click the New button and provide a simple name and message for the alert. In this case I’ve simply named it “Number of Reports” with a message “There are reports in excess of 100 MB in size”. Next, click the Condition button to actually set the formula which will determine if an alert should be thrown.

blog001004

The formula for this condition will simply be:

Count({SherlockCMSInspector_query.Object ID}) > 1

Since the query was developed to return anything greater than 100 MB in size, and the alerter formula was written to warn if anything was returned, the Alerter can now determine if it should throw an alert. Save the formula and ensure that the check box to enable the Alert is checked. and close the Create Alerts dialog box.

blog001006

When finished, save and upload this report to the CMS to a location suitable for sharing with your other admins (no need to keep it all to yourself). With the report in the CMS, log into the Central Management Console and browse to the folder that contains this report. Right click it and choose Schedule. Set the schedule up like any other schedule, reflecting how often you want to check to run this alert. Before you commit the job, click the Alert Notification section of the scheduling page and Enable alert notification. Choose the settings appropriate for your message.

blog001007

Almost there! At the bottom of the Alert Notifications screen, note the Alert Name is populated with the one from the report. Set a Viewer URL if you want to drill down to a detail report (maybe one that actually shows the reports and owners of those reports exceeding 100 MB in size) , and finally, schedule the report.

blog001008

The possibilities here go on and on as I hinted at in the beginning of this post. Automate system monitoring, data validation, and more to save precious cycles from your day.

Leave a Reply