Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2812
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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