How to count certain records from a summary within Crystal reports

Hello, I am trying to create a custom formula that counts record summary above a value of 1.  For example I have a report now that shows a reference number and then the number of units assigned to the reference number.

sample of data in the report:
Number 99-075380             Number of units  1

Number 99-075382            Number of units  3

Number 99-075383            Number of units  1

Number 99-075385            Number of units  1

Number 99-075386            Number of units  2

What i am trying to achieve is creating a formula that will evaluate the group summary and if the number is greater than 1 count it  So in the above example I would get a return of 2 since I have two number that have more than 1 unit.

I have tried this using Do While and if then statements but I don't feel that my syntax is right as I always get a fixed value return of 1

I'm not even sure if you can get CR to automatically increment a number variable based upon an evaluation of a count.

Any help would be appreciated.

Thank you
LVL 1
r_crewsAsked:
Who is Participating?
 
ravenblueCommented:
you should be able to do this in a simple running total.

In the running total dialog;

Set field to summarize to the reference field.
Set type of summary to count
under evaluate select use formula and enter a formula of

     {units} >1

then (assuming this is in a group footer) set the 'reset' to 'on change of group' and select the correct group.

Drop this running total into the group footer.
0
 
r_crewsAuthor Commented:
ravenblue

I tried your solution but I never was able to get an accurate return. I think one reason is due to the fact that Units is not a physical database field but a summary that counts units assigned to the reference number. ( think of this as units that a person may purchase per transaction) The reference number is a Group header and then within that group footer is the count of units assigned to the reference number.
 
Using the running total I am getting a return of 35 where the number should be 23. I changed the evaluation formula to
(Count ({CALLUNITDATA.Unit}, {Active_Calls.Call_Number})) >= 2
AND
(Count ({CALLUNITDATA.Unit}, {Active_Calls.Call_Number})) <> 1
;

as well as well as testing your first suggestion
(Count ({CALLUNITDATA.Unit}, {Active_Calls.Call_Number})) > 1;

In both situations I still get the same number returned.  I have tried using this formula in both the group footer as well as in the report footer and I still receive the same results.

Thank you
0
 
r_crewsAuthor Commented:
Thanks for pointing me in the right direction. I changed the formula count type to DisticntCount and set the evaluation type formula for DistinctCount > 1.

The formula appears to be tracking the counts correctly.

Regards,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.