Crystal Reports Count of Y and N in one field

Posted on 2012-08-29
Last Modified: 2012-09-05
I am very new to CR,  I have been using Access.  If I were doing this in access I would create a query with the same field one I would ask for all the "Y" and the other all the 'N" then I would create another field to perform my calculation.  I am sure it is easy to do in Crystal Report, but I am stumped.  I am pulling from a sql dw.  This is all new to me, I am ready to just pull it all into an access database and forget it.  Crystal Reports is very powerful and my new work loves the reports it can create.  So I want to do it the correct way
Question by:sharronWilliams
    LVL 13

    Expert Comment

    Are Y and N different fields? Or different values of one same field?
    Are you able to modify the report source so that you can do the count on the query and just display it as a new field?

    Author Comment

    No they are one field from a SQL statement with differnt values y or n in the same filed. The field is flagging that a patient has met the requirements.  Either a y for yes, or n for no.  I tried to create a new field with a formula as I would in ACCESS.  It would not work, it either pull all y or n, not both.  I want the total.  To top it all off.  The programmer, decided to change the name of the field, so my report would not pull correctly. I keep trying to do it.  The name change of the field was a huge set back.  The data had to be remapped.  So I am still at a stand still.
    LVL 13

    Accepted Solution

    I can think of two ways of achieving what you want.
    I'll briefly describe them, check them out and let us know if you want to apply any of them.

    Option a. Using one formula field for Y and one for N.
    With this option what you could do is create a formula field that evaluates the content of your "flag" field. If it is yes you display a 1, if it is no you display a 0.
    So you would create two formulas (i.e. formYes and formNo). In formYes you'd have this code (used vb syntax, but no CR available so sorry for any typos):
    if {myFlagField}="Y" then
    return 1
    return 0
    end if

    Then in your formNo formula you'd have:
    if {myFlagField}="N" then
    return 1
    return 0
    end if

    You would place these formulas in the detail section of your report so that it evaluates for each record it prints.
    You would also suppress these fields so they don't show in your final report.

    To show the total you can add a running total of those fields.

    Option b. A little more complicated, but if you master it you will solve most of your problems when it comes to summarizing in CR. This is the three formula trick and it is well explained in this article. (Link)

    Let us know how it goes.

    Author Closing Comment

    Thank you, this worked so well.  It has made my day.  Today I was able to finish 3 reports.  That I have been stuck on for a week.  You were right on!!!1

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now