Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count or DistinctCount?

Status
Not open for further replies.
BradCustom

BradCustom

IS-IT--Management
Joined
Oct 5, 2007
Messages
296
Location
US
Hi,
I'm using Crystal Reports 2011 with an SQL database.
I'm trying to count the number of Workcenters who's utilization is equal to or below 75%. There are 39 different Workcenters in the report and it's grouped by Workcenter and Transaction Start Date. The Utilization spans over a date range selected be the user. The Utilization is calculated using the following formulas.

@GrandTotWCUtil
if (Sum ({@WCHoursWorkedLine}, {Work_Center.Work_Center}))=0
then 0
else
(Sum ({@WCHoursWorkedLine}, {Work_Center.Work_Center})/{@GrandTotWCCap})*100


@GrandTotWCCap
whileprintingrecords;

Global numbervar newtotalvar;


@WCHoursWorkedLine
DateDiff ('h',{Transaction_Data.Transaction_Start} ,{Transaction_Data.Transaction_End} )


@RTotWCCap
whileprintingrecords;

Shared numbervar WCC;
Global numbervar newtotalvar;

newtotalvar:= newtotalvar + WCC;

@TotWCCap
shared numbervar WCC :=
if (Sum ({@WCHoursWorkedLine}, {Transaction_Data.Transaction_Start}))>10 and
({Transaction_Data.Employee}) in ["396","044","378","049"]
then 16
else 8

Let me know if anyone has any ideas how I can do this. Thanks for your help!!
 
Sort by date Sort by votes
All of those variables and nested formulas make this confusing, but you should be able to write a formula like this (which uses a variable because you used one for the calculation) to be placed in the workcenter group footer:

//{@meetscriterion:
Whileprintingrecords;
Numbervar cnt;

If {@yourfinalvalue} > 0 and
{@yourfinalvalue}<=75 then
cnt := cnt + 1;

Then in the report footer, add a display formula:

//{@displaycnt}:
Whileprintingrecords;
Numbervar cnt;

-LB
 
Upvote 0 Downvote
LB,
I tried your suggestion and it works fine. Thanks for your help!!

Sorry if the post was a little confusing, there are two running totals that I am showing. One for Hours Worked and the second is Hours Available. Then I compare them to determine the utilization. I thought it might help to show all of the formulas I was using.
Brad
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mmck1964
  • Locked
  • Question Question
Using "OR" to select data
Replies
4
Views
1K
mmck1964
mmck1964
socalvelo
  • Locked
  • Question Question
Group Sorting Distinct Count Minimum
Replies
2
Views
547
socalvelo
socalvelo
Paulo R
  • Locked
  • Question Question
CR Running Total distinct count duplicates
Replies
9
Views
866
Paulo R
Paulo R
incsoft
  • Locked
  • Question Question
group DistinctCount total
Replies
4
Views
456
lbass
lbass
ryanparks
  • Locked
  • Question Question
Grouping On Count of Records
Replies
10
Views
1K
lbass
lbass

Part and Inventory Search

Sponsor

Back
Top