Print Page | Close Window

Count true / false field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7860
Printed Date: 28 Mar 2024 at 1:51pm


Topic: Count true / false field
Posted By: nbritton
Subject: Count true / false field
Date Posted: 30 Sep 2009 at 9:09am
I have been trying to find a way to count the number of true flags on a field for the selected records.

Description of senerio: I have a report that indicates current open tickets for a customer. I need to report a total of tickets where field CustomerEsc has a true flag.

How can you count the number of true values and display this as a number?




Replies:
Posted By: DBlank
Date Posted: 30 Sep 2009 at 9:21am
You can use a Running Total with an evaluate as formula of that field=True.
You can create a formula using variables using a conditioanl count in it.
You can create a formula to convert the T/F into 1 / 0 as 'if {table.field}=True then 1 else 0' then just sum that formula field.


Posted By: nbritton
Date Posted: 30 Sep 2009 at 10:00am
I have tried the running total but when i review the report it is only displaying a 1 or 0 for the running total when i should see a 3 for a customer.

Here is my formula on the evaluate section:
{Incident.CustomerEscalation}=True


And here is a part that may be causing the problem as well. the value is stored in the db as a 1 or 0, but i am unable to add it. I have attempted to try the other two solutions the past couple of days, but i am not sure if i am doing it correctly. Do you have some examples i could review?

Thanks in advance.


Posted By: DBlank
Date Posted: 30 Sep 2009 at 10:10am

If you drop the field on the canvas and it is displaying as True /Flase and not a 1/0 then it is boolean (t/f) and not numeric.

For the Running Total try this:
name = TrueCount (or whatever)
Field to Summarize= {Incident.CustomerEscalation}
Evaluate = Use a formula as {Incident.CustomerEscalation} ... You do not really need the TRUE  part assuming it is boolean (t/f) but you can use it, it won't hurt
Reset = either
a. NEVER if you have no group an you want a toal for the report
or
b. On change of Group and select the group you want to start back on.
Place the RT in either the Report Footer (if for all records) or the group Footer (if per group reset).
They don't work in Headers and it sounds like that is where you put it. If you drop it on a header it only reads the first record (your 1 or 0 instead of 3).
Does that fix it?
 


Posted By: nbritton
Date Posted: 30 Sep 2009 at 10:38am
Ok i see now what is going on. I did that before and found that the number was 47 for all customers and should have been 11. But it is counting for each journal entry as well that is associated with the ticket that has been escalated.

On the report i am grouping by customer then by status and in group4a i have the ticket info, group4b i have the resolution info and in the details section i have the journal info. So it is counting for each journal that is placed on the report.

Any thoughts??


Posted By: nbritton
Date Posted: 30 Sep 2009 at 10:45am
But it looks like if i use distinct count over count that does give the right numbers. :) Thanks so much for the help.


Posted By: nbritton
Date Posted: 30 Sep 2009 at 11:25am
It looks like after further testing. When i run the report a few more times. The distinct Count is returning a 1 in the report footer. i would expect to see an 11, but count displays a 47. Any thoughts?


Posted By: DBlank
Date Posted: 30 Sep 2009 at 11:43am

I can't visualize your set up or where you need the count.

Distinct Count may work if there is a primaryKey field that is unique for what you are trying to count and all the details for that are all T or all F.
Can you explain your grouping and some row level data and how you need it counted at the grouping. Make sure to inclduet he duplicate rows that maybe causing you issues.


Posted By: nbritton
Date Posted: 30 Sep 2009 at 11:56am
There are two tables Incident and Journals. Where journal is linked to incident by a field called Parentink_ID.

I am grouping records by customername and then by status of the ticket (open closed resolved)

So each record is displaying incident info such as incident number, contact, status, description, resolution. From journal i am pulling all journals related to the incident and showing who created, what time, what catagory and what the journal notes are.

What i am trying to total and summarize is how many of the incidents selected had a true value in the incident.cusotmeresc, by a count.

When i first did the running total it came up with 47 for the count. as i look at the report it is adding one to the count for every journal that is related to a ticket with an incident where the customeresc field is true. I had changed this to a distinct count and on the first run of the report it appeared to work. i reran the report and the count was 1. I can supply screenshoots if needed, once i find a way :)


Posted By: DBlank
Date Posted: 30 Sep 2009 at 12:03pm

If I understand it correctly try a RT as a DistinctCount of the incident Number field conditionally evaluation of {Incident.CustomerEscalation}=True reset at the Customer level.

This assumes that the incident # is a unique # per incident and can only be True or False and you wnat yuor count per customer.
make sense?


Posted By: nbritton
Date Posted: 30 Sep 2009 at 12:13pm
ohh i think i know where your headed with that. Let me give that a try. If i understand. i will summerize the incidentnumber field and elevate on the customeresc field.


Posted By: nbritton
Date Posted: 30 Sep 2009 at 12:17pm
Ok excellent that did it!!

If you dont mind i would like to pick your brain on one more related item to this. Is there anyway to get that in the Report Header section?


Posted By: DBlank
Date Posted: 30 Sep 2009 at 12:21pm

If you had a 1:1 of row:incidentnumber you can use the other suggestion for creating a formula field to make an "if field=true then 1 else 0" and then SUM that. the SUM can go in a header (unlike a RT).

If not you would need to have a way to count in your source data like creating a stored procedure
or
use a sub report
or
possibly a Graph or Crosstab.
Any of those work for you?


Posted By: nbritton
Date Posted: 30 Sep 2009 at 12:29pm
That may work. I will work on that. Can you do the if and sum in the same formula or does it require two formulas one call the other to sum it?


Posted By: nbritton
Date Posted: 30 Sep 2009 at 12:34pm
i don't think it is a 1:1 because it give me the 47 number again, when i do that.

Thanks Again for the help today!!


Posted By: DBlank
Date Posted: 30 Sep 2009 at 12:34pm
One to convert
"TrueCount"
if field=true then 1 else 0
A second to Sum (or use the insert Summary function). I think only formulas that could be created via the Insert Summary can be displayed on headers without messing up the numbers...
SUM( mailto:%7b@TrueCount - {@TrueCount })
 


Posted By: nbritton
Date Posted: 30 Sep 2009 at 12:36pm
Yep thats what i did with the truecount, and then i used the insert summary and did a sum on the formula. it gave me the 47 again, so its counting the journals.


Posted By: nbritton
Date Posted: 30 Sep 2009 at 1:29pm
Ok so as a chart works great to show that information though, because you can do a chart with distinct count on incident number.


Posted By: DBlank
Date Posted: 30 Sep 2009 at 1:34pm
Yes, if you grouped the chart design on the T/F field then you would be able to use the DC of the inc# to show totals for TRUE and totals for FALSE



Print Page | Close Window