Creating Report Alerts in Crystal Reports

In Crystal Reports, a feature called Report Alerts has become more important the last few years. It has to do with the fact that there are at least occasional exceptions to many kinds of report data. “Problems or conditions outside the norm” is a good way to phrase it, and when those happen, we often need to know about them ASAP. Hence Report Alerts.

Creating one isn’t hard—we just have to know three things going in. [a] What to call it, [b] what condition triggers it, [c] what message to show when it kicks in—this last is optional but strongly recommended.

Report Alerts box

Looking for a low order count is a good example. Having set up a field to count orders per company, we can now set up an alert to see who placed less than, say, thirty orders for the year in question. In the Report menu, we slide to Alerts, then click Create or Modify Alerts.

Create Report Alerts

Next thing is to set up the actual alert. We click New on the right side of the box, give the alert a name, and write a user message—some people skip this, but having at least a brief message is important, as you never know who’ll be using the report.

Report Alerts condition

Then, most important, set up the condition which triggers this alert. (Back to our old friend, the Formula Editor.) In our case, we’re looking for suppliers who placed fewer than thirty orders this year, so the formula reflects this.

Refresh for Data

When done, we OK or Close out of all the boxes, save (desirable), and refresh (F5).  We should see the alert kick in, with the option to let us know which records triggered it.

Report Alerts result

Here we can see the number of records has narrowed down to only eighteen pages’ worth, rather than fifty-nine, and at least one supplier’s data confirms only twenty-three orders (less than thirty). So the alert lets you know something’s different, then allows you to see what the changes are.

As is often the case, the hard part is getting the details clarified. Someone said, “Computers don’t do vague.” It’s a point to bear in mind with Report Alerts.

Slicers in Excel PivotTables

When Excel 2007 was introduced, the PivotTable Field List included a “Filter” area, where one could drop a field to use as a filter (for example, year, brand name, size, etc. from a block of sales data). If the user filtered for one year, or one brand, the name of the item was visible in the space above the body of the table. But if more than one item was filtered for, all it would say was “multiple items”…and not which ones. The feature worked, but couldn’t take this into account. So in Excel 2010, and all versions following, the Slicer feature was added. (No, I don’t know where they got the name. Sounded a little odd to me, too….)

Creating one or more slicers is dead simple. Make sure you’ve clicked somewhere in your PivotTable or –Chart, go to the PivotTable/Chart Tools Options tab, look in the Sort and Filter group, and click Insert Slicer.

Insert Slicer

You’ll need to choose which fields you want to filter with. Usually it’ll be at least a couple, one of which will often but not always contain values. And when you’ve selected, click OK.

Select Slicers

Done. Using the slicers is also easy. Click on the thing(s) you want to filter for (i.e. see), and they stay visible. Everything else hides.

But there is one neat extra tucked in there. Aside from the fact that the slicers print (they’re a WYSIWYG item), and you can resize them, you can change the number of columns of buttons per slicer, which means you can show more of them at once, and save some screen (and print) space.

Setting Columns

And that means you can use them to the fullest, because most of the time, any user who looks at your printout can see what you filtered for! No muss, no fuss.

Slicers are one of my more favorite features in Excel. They’re a good answer to a reasonable question (filters you can see and know what they’re filtering for), and they’re easy to use. They help content be more user-friendly, and even in a static situation (printout or PDF), they retain their usefulness.

Filtering Data in Crystal Reports

When you run a report in Crystal Reports, you have a lot of latitude as to what goes in, how it’s formatted and organized, and so on. But one consideration that doesn’t always get mentioned is how to leave data out.
Turns out, it isn’t difficult. In fact, the technique is similar to one we find in several other programs which deal with data and databases.

After opening the program, the database and the report in question, the feature that does the filtering is the Select Expert–specifically, the Record part.

Among the logical operators available to us is “Is Not Like” (sometimes seen in other programs as “< >” or “Not Equal To”). This is the one that says “DON’T show me…” whatever it is you want to exclude from a given field, like City.

But the next part is also easy–just a little different. If, let’s say, we want Crystal to show all cities in a shipping list other than Albuquerque (watch your spelling on this one! 🙂 ), we put that name between asterisks, like so: *Albuquerque*.

This means that every record whose city name does NOT include Albuquerque (beginning with, ending with, or containing) will show in the report.

It may seem a little odd at first, to want to exclude information in Crystal Reports, since usually we’re trying six ways from Sunday to figure out how to include information. But don’t forget that panning for gold meant sifting out what you didn’t want, and reducing a sauce involves removing water or other fluid to make it denser and more flavorful.
As a related point, doing Web searches can involve a similar idea. We often use Google to look for things, but the Advanced search capability lets us say, in essence, “Look for this and this but NOT that.” Hardly ever gets used, but it’s always there if we need it. Ditto in Crystal.