Solved

Summation of Yes/No Formula Field

Posted on 2011-02-15
19
403 Views
Last Modified: 2012-05-11
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
Comment
Question by:wdelaney05
  • 11
  • 6
  • 2
19 Comments
 
LVL 8

Expert Comment

by:kingjely
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

by:wdelaney05
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

by:wdelaney05
ID: 34904014
I keep getting an err msg stating "this field cannot be summarized". Any thoughts on that?
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 8

Expert Comment

by:kingjely
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 100

Expert Comment

by:mlmcc
ID: 34904065
Waht are these formulas

{@Hrs Per Order}  and  {@Compliant_Hrs_Per_Ord_Diff}

mlmcc
0
 

Author Comment

by:wdelaney05
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

by:wdelaney05
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 100

Assisted Solution

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

mlmcc
0
 

Author Comment

by:wdelaney05
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

by:wdelaney05
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

by:kingjely
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

by:wdelaney05
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

by:
kingjely earned 250 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

by:wdelaney05
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

by:wdelaney05
ID: 34923425
Actually, after doing some experimentation, I did this:
Made a Sum Compliant formula:

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

by:kingjely
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

by:wdelaney05
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

by:wdelaney05
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

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

813 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