Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Count true / false field Post Reply Post New Topic
Page  of 2 Next >>
Author Message
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet Topic: Count true / false field
    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?

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.


Edited by DBlank - 30 Sep 2009 at 9:22am
IP IP Logged
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
 


Edited by DBlank - 30 Sep 2009 at 10:13am
IP IP Logged
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet 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??
IP IP Logged
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet 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.
IP IP Logged
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet 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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.


Edited by DBlank - 30 Sep 2009 at 11:44am
IP IP Logged
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet 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 :)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.