Wednesday, October 3, 2012

Crystal Reports : Counting Records (The Complex Way)

How do we count records conditionally? You may be tempted to use a Running Total object, but first, look this over. I was taught this technique when I first started with Crystal and use it in almost every report.

Using simple Summaries (Insert | Summary) in Crystal is great, don't get me wrong, but they are inflexible. For instance, when summarizing (trying to get a count) on {INVOICE.INVNUM}, ALL invoice numbers are evaluated, including duplicates (unless a Distinct Count is being used).

Which usually is fine, but there is always some sort of exclusion to be made; we only want invoices that DON'T begin with "000150" because it a House Account, etc. And you could handle this within your Selection Criteria handily enough; but let's imagine that it screws up other parts of the report; excluding House Accounts impacts other Subtotals, etc.

So to accomodate this little pickle, we will use a formula to count, one record at a time. This is actually a very popular Crystal Technique and you see it a lot in older, more "veteran" reports.

Create a formula called
{@InvoiceCount}
IF MID({INVOICE.INVNUM}, 1, 6) <> 000150 THEN 1 ELSE 0

So what the formula is doing is evaluating the first six digits of the Invoice, and if it ISN'T a House Account, it returns 1 (we want to count it), otherwise it returns 0 (we want to exclude it).

Now, create a simple Summary on {@InvoiceCount} and make it a SUM operation, this way you get a count of only the records you want (for the particular task at hand), without sacrificing any existing Selection Formulas or Summary operations.

1 comment:

Anonymous said...

The actual face masks are available in various colours. Dark, Red-colored, eco-friendly as well as fake rolex watches colour kinds are offered at ask for. The actual colours are extremely essential throughout a paintball online game. They'll avoid a person through. Electronic pictures tend to be a great option to put on the replica chanel handbags. An electronic publicity isn't kind of a good publicity used with a digital camera. It's high quality is actually extra exceptional. An electronic picture may also be preserved throughout a. jpg or even. jpeg document. These types of types of video footage tend to be the very best to put on the customized swiss replica watches. The actual electronic picture that you simply are prepared to put on your own most recent reproduction totes needs to be associated with high-resolution. In case your picture promises will be higher after that all of the sharp outlines as well as spirited colours from the picture may affirmed end up being shown about the replica watches uk. Requirements to not change their own visit look at items or even competitors. The look merely supplies a broader look at. This really is sufficient look at for just about any 1 actively playing paintball online game. You'll find these kinds of totes in several prices through searching on the internet or even from numerous shops as well as thrift shops. Footwear act like handbags within replica chanel handbags design. Imagine dark brown leather-based flip flops, slouchy footwear as well as particularly blocks or even footwear along with wood bottoms. They could final for a long period.