Solved

Crystal Reports Where clause in a formula

Posted on 2009-05-08
8
2,441 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?
0
Comment
Question by:szadroga
  • 4
  • 3
8 Comments
 
LVL 9

Expert Comment

by:wellhole
ID: 24338078
You should look into creating a subreport and pulling the information from that.
0
 

Author Comment

by:szadroga
ID: 24338133
There is no formula I can write?  
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24338628
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:szadroga
ID: 24338742
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 24338754
You have to do it manually using a method I outlined.

mlmcc
0
 

Author Comment

by:szadroga
ID: 24338793
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
 

Author Comment

by:szadroga
ID: 24339582
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 24340176
You need to have a group in the report for FullName.

mlmcc
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

770 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