• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2957
  • Last Modified:

Crystal Reports Where clause in a formula

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?
0
szadroga
Asked:
szadroga
  • 4
  • 3
1 Solution
 
wellholeCommented:
You should look into creating a subreport and pulling the information from that.
0
 
szadrogaAuthor Commented:
There is no formula I can write?  
0
 
mlmccCommented:
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
szadrogaAuthor 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?
0
 
mlmccCommented:
You have to do it manually using a method I outlined.

mlmcc
0
 
szadrogaAuthor 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;
0
 
szadrogaAuthor 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
0
 
mlmccCommented:
You need to have a group in the report for FullName.

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now