Wednesday, April 25, 2012

Crystal Reports : Conditional Running Totals

So, being Crystal Reporters, we are used to exceptions, right? For instance, management wants a Count of records on a report, but ONLY IF certain criteria is met. They want a totaling of Sales dollars, but ONLY THE ONES that happened between a specific date range. Now, the most usual way to deal with this is to simply modify your Selection Criteria. Unless of course they want to see "all the rows", but only get the special totals as described. In this case, we cannot constrain our Selection Criteria any further.

This is very typical, and can be handled in a variety of different ways. Many folks will use the Running Total object, which is a good way. I, however, prefer to use a combination of formulas to make this happen. It allows more flexibility down the road when more changes are required.

So, let us assume we need to count records only where the CONTACT1.CREATEON falls within a certain date (i.e. 1/1/2012 to 1/17/2012). Because of this, we cannot insert a simple Summary, which will of course count all the records.

The first thing I do is create a "testing" formula, which returns a 1 or 0, depending on a condition like this;

Formula @ShouldRecordCount:
IF {CONTACT1.CREATEON} >= "01/01/2012" AND {CONTACT1.CREATEON} <= "01/17/2012"
THEN 1 ELSE 0

So, now if we drag this new formula onto the report we can see it returning a 1 or 0, depending on our CREATEON value. Now it's an easy procedure to create a simple Summary (Insert | Summary) on {@ShouldRecordCount}, which will of course total all those 1's and 0's.

To make it nicer, you need NOT have the @ShouldRecordCount formula on the report itself. All formulas are evaluated, regardless of whether or not they appear on the report. It's very common to see several of these "testing formulas" on a single report, each returning 1's or 0's to be subtotaled by simply summaries. Give it a try!

ProTip: Experienced Crystal Reporters will undoubtedly realize that the same functionality is achieved though using a Running Total object. The reason I prefer this method is that it allows (in my opinion) easier editing of the Conditional code and the ability to re-use @ShouldRecordCount in other areas of the report.

3 comments:

Anonymous said...

I have used this concept several times in the past. BUT, I didn't know you didn't have to put the formula on the report! I thought it had to be on the report to be evaluated. Nice to know that I don't have to clutter up my report with formulas like this and still have them do what I want them to do. Thanks for sharing!

Anonymous said...

Also found this very useful, thanks!

apple iphone 6 covers said...

iOS 9 will likely join the iOS 8.4 update on stage in June and it will likely come with a number of features, enhancements and fixes. And while many believed that some older devices would not get these features and enhancements, that may not be the case at all.