Thursday, August 28, 2014

Crystal Reports : Count Formulas, not Fields

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...

Of the many type things that exist at the specialized economy, trainers not to mention handbags might possibly be the services that might be given the task of a number of obsessions. replica chanel For the reason that you also have desired, using a particular fixation methods having to pay fundamental sums from profit. For many, this unique will never work for a situation, like they will be able to compensate large deals for the purpose of purses and / or trainers. replica hermes But, not only on anybody inputs this unique section. There can be families, certainly a lot of women, what individuals absolutely adore handbags, and yet simply cannot afford to pay to find these products. rolex replica Subsequently, through this specified position, what’s furthermore there for you to do? How must a lot of women afford to pay amazing researching purses? rolex replica Most certainly the remedy is simple it will not perhaps even astound most people, bearing in mind that it can be this widely used rehearse. replica watches uk Most females need launched investing in replica purses.