Print Page | Close Window

Help counting records that meet criteria

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11268
Printed Date: 29 Apr 2024 at 2:10am


Topic: Help counting records that meet criteria
Posted By: zann
Subject: Help counting records that meet criteria
Date Posted: 29 Sep 2010 at 8:52am
Hi everyone,

I'm new to writing formulas in Crystal.  I have version XI.

What I'm trying to do is count the number of records in a field that fall within a range, then return that result. 

A simplified example of what I want to end up with:

Grouped data (Orders by Company name)
0-100 pounds       5
101-200 pounds    6
201-300 pounds    1

I've experimented with the Count formula and control structures, but I don't see the solution.  I noticed there are Range functions but don't understand how to make them work here.  Can anyone please help me put it together?


Replies:
Posted By: DBlank
Date Posted: 29 Sep 2010 at 10:03am
please explain your row level data that you want to get into the format you posted

Posted By: zann
Date Posted: 29 Sep 2010 at 10:12am
Just found one solution, however messy it is.   In case someone needs it, first create formula fields with this:

if ({table.field} = "criteria")
then 1
else 0

You can then create a second formula to sum this field to get a count of records matching the criteria and place this field on the report.  This is working for me so far.

Posted By: DBlank
Date Posted: 29 Sep 2010 at 10:17am
That is one solution
or you can use Running totals
or you can use shared variable formulas
or you can create a formula to make your 'groups' (e.g. 0-100 lbs) and then do sums/counts on that grouping
but it is hard to tell without knowing what your data looks like

Posted By: rvink
Date Posted: 29 Sep 2010 at 10:18am
Can you filter the record set using the Select Expert so your data only contains the fields that fall in the range you want?

If your report needs to select a wider range of data and you only want to count a subset, could you group the data and count the data in that group?

Have you looked at DistinctCount vs Count?

You could create a formula like this:

if {table.field} >= lowerlimit and {table.field} <= upperlimit then
   1
else
   0

You can then sum the formula to count the records you want.


Print Page | Close Window