We help IT Professionals succeed at work.

Crystal Reports Where clause in a formula

szadroga
szadroga asked
on
3,743 Views
Last Modified: 2012-08-13
I am trying to write a formula that does a count on a data set with a WHERE clause.  I have 2 columns of data:  NAME and TASKS.  I want to do a count of the NAME WHERE that person has 1 or more TASKS.

I am not sure how to put the WHERE clause into the crystal formula.  Is there another type of function/operators I should be using?
Comment
Watch Question

Commented:
You should look into creating a subreport and pulling the information from that.

Author

Commented:
There is no formula I can write?  
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try this idea
Add a group by Name

In the report header add a formula
WhilePrintingRecords;
Global NumberVar NameCount := 0;
''

In the group header or footer add a formula
WhilePrintingRecords;
Global NumberVar NameCount;
If Count({TaskField},{NameField}) > 0 then
    NameCount := NameCount + 1;
''

In the report footer
WhilePrintingRecords;
Global NumberVar NameCount;
NameCount

mlmcc

Author

Commented:
I think i have realized my problem, i am trying to a count on a summary field.  I keep getting an error message that this field cannot be summarized.  Is there a way around this?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
You have to do it manually using a method I outlined.

mlmcc

Author

Commented:
When I get to COUNT line, I get the error message because the TaskField is already a formula field.

In the group header or footer add a formula
WhilePrintingRecords;
Global NumberVar NameCount;
If Count({TaskField},{NameField}) > 0 then
    NameCount := NameCount + 1;

Author

Commented:
I fixed the summary issue by doing all the summarizing in the SQL query before being brought into Crystal.  But when i insert the two fields into the Count(), i get an error message (see image)
error.jpg
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.