How to count certain records from a summary within Crystal reports

Posted on 2009-12-17
Last Modified: 2012-05-08
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
Question by:r_crews
    LVL 4

    Accepted Solution

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

    Author Comment


    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
    (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
    LVL 1

    Author Closing Comment

    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.


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now