Conditional counting in Crystal Reports

How to count the rows (or records) in a report according to criteria within the data.

By Mike Lewis

Figure 1

Figure 1: A report with a "conditional count" summary.

A common reporting requirement is to add a "conditional count" summary to a report. By that I mean a summary that shows counts of the various types of records in the report, classified according to some condition within the data.

Figure 1 shows an example of what I have in mind. The main part of this report is a straightforward list of orders. Below the list, there is a summary that shows the number of orders in each of three groups: low, medium and high. The amount of each order determines which of the three groups it belongs to.

In this article, I'll show you how to create this kind of summary. Although the steps I'll describe apply specifically to Crystal Reports, the general concept is not CR-specific and can be used with just about any reporting tool.

Three formulas

We'll start by creating a formula for each of the three groups. Let's name these formulas Tot_Low, Tot_Medium and Tot_High respectively. Tot_Low looks like this:

IF {orders.order_amt} <= 1000 
    THEN 1 ELSE 0;

This formula will return 1 if a given order is in the "low" category (defined as orders worth less than $1,000).

Similarly, Tot_Medium will return 1 for "medium" orders (those between $1,000 and $2,000):

IF {orders.order_amt} > 1000 AND {orders.order_amt} <= 2000
    THEN 1 ELSE 0;

And Tot_High does the same for "high" orders (those exceeding $2,000):

IF {orders.order_amt} > 2000 
    THEN 1 ELSE 0;

All three formulas will return 0 if the order amount is not within the specified range for the relevant group.

Detail band

Figure 2

Figure 2: Place the formulas in the detail band.

The next step is to place the three formulas in the detail band of the report. Figure 2 shows how they might look. You'll see that I've lined them up in three neat columns, but this is not essential. If you're short of space, just drop them anywhere that's convenient within the detail band. They won't be visible in the final version, so their exact placement and alignment won't matter.

Each order now has a 1 in the formula column that relates to its group, and 0 in the other two columns. All we have to do now is to total these 1s and 0s. To do that, just select each of the formulas in turn, and insert a summary for it. In each case, specify a "Sum" type summary, and choose "Grand Total (Report Footer)" as the summary location.

The final step is to tidy the report up. You'll need to hide the three formulas (and their associated headers), and re-arrange the summaries so they appear one below the other - as in Figure 1.

And that's all there is to it. Clearly, this approach won't be suitable if you have a very large number of groups, or if the grouping criteria are open-ended (if you don't know the criteria at design time). But in many cases, this simple technique will be all you need.

April 2013

Please note: The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly with your sites or applications.

If you found this article useful, please tell your friends: