Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Summation of Yes/No Formula Field

Posted on 2011-02-15
Medium Priority
408 Views
Hi,
I have a formula field in my Details section called _Compliant. _Compliant's formula is:
If
{@Hrs Per Order} <= {@Compliant_Hrs_Per_Ord_Diff}
Then "Yes"
Else "No"

I need to total the number of yes's and no's in two new fields. Total Compliant = total count of all yes's
Total Non Compliant = total count of all no's
How do I do this?

0
Question by:wdelaney05
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 11
• 6
• 2

LVL 8

Expert Comment

ID: 34903951
Hi there,

Make a new formular called
{@COMPYES}
and put in

if { @_Compliant} = "Yes" then 1 else 0

Put that in the details section,
Then go to 'Insert summary' (on the toolbar) and sum  {@COMPYES}
You can then righ click on the  {@COMPYES} field and suppress it

DO the same for the No's

0

Author Comment

ID: 34904002
Ok, I created a new formula named {@COMPYES}. I went to Insert/Summary but COMPYES wasn't in the list of available fields for summing. How can I manually sum this formula?
I guess I can make another formula named SumCOMPYES that does this: SUM({@COMPYES}).
0

Author Comment

ID: 34904014
I keep getting an err msg stating "this field cannot be summarized". Any thoughts on that?
0

LVL 8

Expert Comment

ID: 34904024
when you put {@COMPYES} in the detail section does it work, and come up with either 1's or 0's ?
0

LVL 101

Expert Comment

ID: 34904065
Waht are these formulas

{@Hrs Per Order}  and  {@Compliant_Hrs_Per_Ord_Diff}

mlmcc
0

Author Comment

ID: 34923146
Hi! Sorry I left the question, many apologies! Our network went down yesterday and today I was trying to catch up to high priority tasks. I'm afraid I put my EE question in lower priority tasks for the day...

Ok, so, kingiely, to answer your question "when you put {@COMPYES} in the detail section does it work, and come up with either 1's or 0's ?"
Yes, it does.

mlmcc, to answer your question "what are these formulas {@Hrs Per Order}  and  {@Compliant_Hrs_Per_Ord_Diff} ?"

{@Hrs Per Order} : {@Total Hrs}/{@Ttl Restraints Ordered}
{@Total Hrs}: SUM({@Sum Hrs})
{@Sum Hrs}: ({Command.SEG_END}-{Command.SEG_START})*24

{@Ttl Restraints Ordered}: DistinctCount({Command.ORDER_PROC_ID})

{@Compliant_Hrs_Per_Ord_Diff}: {@Total Hrs}/{Command.EXPECTED_HRS}
0

Author Comment

ID: 34923227
ok, here's what I tried: I attempted to make a workaround. I changed _Compliant formula to this:
If
{@Hrs Per Order} <= {@Compliant_Hrs_Per_Ord_Diff}
Then 1
Else 0

Then I made a new formula called Compliant Display. Compliant Display has this formula:
if {@_Compliant} = 1 then "Yes"
else "No"

Compliant Display will display in the Details section for the appropriate field. Now, I just have to make the summation of  _Compliant work. I still get an err msg stating that it cannot be summed.
0

LVL 101

Assisted Solution

mlmcc earned 1000 total points
ID: 34923269
It can't because it uses a summary value in {@Total Hrs}

mlmcc
0

Author Comment

ID: 34923288
So, you can't sum something that already uses a summary value? Darn. How do I get around this?
I thought may be it was because I didn't have a If Not Is Null kind of missing statement for _Compliant...
0

Author Comment

ID: 34923289
So, you can't sum something that already uses a summary value? Darn. How do I get around this?
I thought may be it was because I didn't have a If Not Is Null kind of missing statement for _Compliant...
0

LVL 8

Expert Comment

ID: 34923295
You cant sum a sum, atleast that way. is why you are getting the error.
Can you do that formular in whatever database you are using, and then just pass the field to the report and then do the sum ?

Ie do the sum formular in the back end eg sql, then put the resulting field on the report, and do the summary?
0

Author Comment

ID: 34923309
kingiely, re: ie do the sum formular in the back end eg sql, then put the resulting field on the report, and do the summary?

Yup, I guess I can do that...
0

LVL 8

Accepted Solution

kingjely earned 1000 total points
ID: 34923336
One way i use is the add command,

SELECT field1,field2,field3,field4,field5,
sum(whatever), sum(whatever) ect
make sure you get the correct results in sql when you excecute.

Then go to database>database expert> Choose your connection > and go to addcommand.

and put the 'SELECT' in.

Rename, Addcommand to 'COMPLAINTS' as a new table.

Then you have a new table with only fields you need, with the summing already done in the back end,
Then put the fields on the report, and do your summing on the fields as they are just fields at that point.

It's alot quicker as you have only the fields you need, and a bit more flexible, ie you can sum fields easier ect.

It's a bit more of a pain to get into the habit of this, but its the way to go when you get into more complicated reporting.

Hope that helps
0

Author Comment

ID: 34923349
kingiely, that's really nice advice, thanks. Much obliged.

Well, I guess I'm gonna revisit my SQL, thanks to both of you!
0

Author Comment

ID: 34923425
Actually, after doing some experimentation, I did this:

if {@Compliance Display} = "Yes"
Then COUNT({Command.Pat_ID})

and a Sum NonCompliant formula:
if {@Compliance Display} = "No"
Then COUNT({Command.Pat_ID})

That seems to work. Eeks, I think I'm officially obsessed with this!
0

LVL 8

Expert Comment

ID: 34923436
I think you will find that counts all records, not just the 'No''s or Yes's, I tried that first.

saying if it finds ANY or even 1 Compliance Display that = "Yes"
Then COUNT all Command.Pat_ID's

You may want to double check but thats my understanding

0

Author Comment

ID: 34923498
Hmm. I tried it with one sample record. the sample record was non compliant. The Sum NonCompliant field has a 1. The Sum Compliant field has a 0. If you're right kingiely, wouldn't both be 1, then?
0

Author Comment

ID: 34923540
Actually, no, both wouldn't be one because there aren't any No's yet since I have only one record. You're right kingiely, I was just being stupid.
0

LVL 8

Expert Comment

ID: 34923566
Ahaha, I didn't say you were being stupid, I tried did the same thing!!  :)
0

## Featured Post

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyâ€¦
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can reâ€¦
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
###### Suggested Courses
Course of the Month9 days, 21 hours left to enroll