Learn how to a build a cloud-first strategyRegister Now


Crystal Reports Count of Y and N in one field

Posted on 2012-08-29
Medium Priority
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
  • 2
  • 2
LVL 13

Expert Comment

ID: 38347319
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

ID: 38366414
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

LIONKING earned 2000 total points
ID: 38367751
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

ID: 38368210
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

810 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