Automated Email Alerts with Crystal Reports

Quite some time ago, I wrote a blog about using Crystal Reports Alerts to get automated notifications in the event something significant happened in your data. I was recently working on a similar challenge, but when that encompassed not only bursting to specific users in this manner, but also giving them their own alert and target via email AND formatted for their mobile devices, I had to cook up something different. Crystal Reports + Publications + Profiles + Bursting + Alerts = a handy solution with only a little set up overhead. Fun! But not for the faint of heart.

The Crystal Report

For starters, my example here is based off old-fashioned eFashion. Your mileage can vary in so many ways. However, I hope this gives you enough guidance to walk away and set up your own alert mechanism. I’m basing my example on a group of Store Managers that need to get alerted on a monthly basis if their store’s revenue is less than $150,000 for a given month. I’m going to simplify the example a little bit and only use a few of the Store Managers from the list.

Mark, Steve, Tuttle, and Richards are not the best managers. We’re going to watch them closely with a simple Crystal Report. By itself, I’m only going to come back with some high level data, prompting the report for the store name, but then also coding for anything less than $150,000 in Sales Revenue.

It’s worthwhile to note here the parameter is defined then based upon my prompt. He’ll matter later.

From there, I’m not going to repeat the steps (see the other blog post), but having your Alert defined correctly is important. I wrote a simple formula to return a 1 or 0, and if 1, throw the alert. With the Crystal Report all set up and ready to alert if a Sales Manager has a store with less than $150,000 in revenue, we move on to setting up things in the Central Management Console.

User Security

I’m going to begin by creating a group to isolate my Sales Managers. After all, they are the only targets of this alert messaging.

With the group created, I’m going to create an Enterprise account for each Sales Manager. It is a terrible oversight that they are not already SAP BusinessObjects users, but that is about to change. Note here that I’m paying attention to get their email address right.

Now that each of our Sales Managers exists, add them to the eFashion Sales Managers group we created for easy reuse later. You’ll also need to pay attention to granting access to universes, connections, and reports as required to run this stuff.

Profiles

Profiles are an important part of telling the running report which prompt values to supply for each Sales Manager. Visit the Profiles section of the CMC and create a new Profile for eFashion Sales Manager Stores.

The new Profile needs target for each Sales Manager. Start by creating a new Profile Target.

Click Add, and select the eFashion universe, the parent class of the object used in the report condition, and then lastly, select the actual object.

When finished, you should see one referenced object to override with this Profile.

Next it’s time to assign the values for the Profile Target. Click Add, select the first Sales Manager, and enter a New Value for his store name, as is to be provided to the prompt.

Once all Sales Managers are created and their Profile Values are assigned for the Profile Target, we are ready to forge ahead. These values can now be used to override prompt values in any report type based on the logged in user.

The Publication

The publication is our automation component. It will help to define the scheduled instance, all the parameters it needs, and who to email when we hit the Alert. Start out by creating a new Publication, for simplicity where you saved the Crystal Report.

I gave the Publication a simple name. Mark it up as necessary to keep track of it later.

Choose the Crystal Report as the Source Document for this Publication and ensure it set to Refresh at Runtime.

Publications really want to have at least one Enterprise Recipient. To keep it simple, I’m just going to dump it to Administrator.

Now an important piece is in defining the Personalization piece. Rather than using the default value for the parameter that the Publication has detected in the report, we need to use the last combo box on the right to select the Profile we just created, effectively telling it to use that target.

The Formats screen now allows us to set up the formats the Publication should enable. My only requirement right now is to send the body of the report via HTML. To do so, select the report, and choose mHTML from the format option. Note: I removed Crystal Report, but you could just as easily include a PDF copy of the report as well.

In Destinations, since I want to email the alert…well…use email. However, remembering we are sending to each Sales Manager, and remembering you took care to specify each of their email addresses on their accounts when you created in Enterprise, the system knows their email addresses.

Also remember to specify the variable in the email body to embed the HTML content of the report into the email. I also chose to not attach any documents to my email.

 

Before you save the Publication, there is an advanced option in Delivery Rules, that you need to turn on. I told my Publication to only “Deliver all documents only when all conditions are met”, and noted that it was smart enough to see my “Revenue Alert” that I created way back in my Crystal Reports Alerts. Cool! I only want it to deliver the report to the user IF the Alert is true.

Schedule it

You are almost there. With the Publication saved, you can now schedule it. When setting up the schedule options, all the defaults you defined should be set in the schedule already. However, to burst it, let’s use the Schedule For option. On that page, tell the Publication to “Schedule for specified users and user groups”.

Select the eFashion Sales Managers group.

Run it! You made it! The magic here is you now can see that the report ran for each of the Sales Managers, which means it fired off each alert individually. And since we set the personalization options to only send it if true, only the Sales Managers with a month less than $150,000 in sales revenue get the email alert.

That was a lot, I know. But I think the payout is really there in being able to create some automated monitoring with the tools you already own. Let’s review:

• Needed some automated email alerts when something that should be noted happened, without making Sales Managers log into SAP BusinessObjects
• Created a Crystal Report that only returned data if the exception was met
• Created an Alert in that Crystal Report that registered the Alert if any data was present in the report
• Created a group of users to run the report for
• Create a Profile for those users to run the report for their specific stores
• Created a Publication to handle the distribution and conditional execution of the report
• Scheduled using the Schedule For option to manage the bursting

In closing, formatting for mobile is still challenging. Go for long/skinny reports vs. wide. All devices look different in the final format as well, but you can come up with something manageable. Turn off headers, footers, etc. Keep the format simple and the emailed alert concise. You can even embed links to the doc to open in InfoView for connected organizations.

I would have loved to have shown you the final output of this solution, but I do not have email hooked into my demo server here. Regardless, I hope the effect is not lost.