COUNT Records that meet criteria

[TracyEngel]

Using 8.5 against a SQL Database.

Sample Data:
QTEID# STATUSID# EFFDTE MKTID#
12345 DCU 06/01/2004 R0112
(QTEID# is an unique key identifier)

Need to count the number of QTEID#'s that occur within a time frame (select between two dates on the EFFDTE column) that possess a specific STATUSID# for a specific MKTID#.

For instance, need to know that there are 30 QTEID#'s from 06/01/2004 to 06/30/2004 that are status DCU in market R0112. Or perhaps there are 20 QTEID#'s from 05/15/2004 to 07/15/2004 that are status BND in market M0111. The output I'm looking for is the count and I get to input the parameters for the other fields.

 

[lbass]

You could write a formula like:

if {table.effdate} in {?daterange} and
{table.status} = {?status} and
{table.marketID} = {?market} then 1 else 0

...where {?daterange},{?status}, and {?market} are parameters that you have created. Then you would right click on this formula and insert a summary (SUM, not count).

-LB

 

[TracyEngel]

That doesn't seem to want to work, although is is along the same lines as what I was originally trying.

I oringally started with the idea of a DO/While loop where if a record met the parameters, a variable called "numCount" would increase by 1. However, it appears that there is no EOF or no way to tell Crystal to run the loop until it reaches the end of the file. Not knowing how many records will meet the criteria, I get a never ending loop.

Is there a way to change the report record selection formula on the fly then just do a count of the records in the list?

 

[lbass]

It would help for you to explain what you mean when you say my formula above doesn't work--what results did you get? Were the results inflated? If you are using multiple tables with some duplicate fields, then you might need to take a different approach, so it would help if you provided a sample of your detail level data. Otherwise, I see no reason for the formula not to work.

To populate the report only with those records which meet parameter criteria, use the following in the record selection formula:

{table.effdate} in {?daterange} and
{table.status} = {?status} and
{table.marketID} = {?market}

-LB