Incorrect Totals

[SamDout]

Hello all. I’m trying to create a report that shows employees who have completed training programs. Each prog has several courses. Each course has a seq #. For a program to be completed this criteria must be met:
All courses with a seq of 1 must have a completion date. All courses with a seq of 10, 12 must have at least one course with a completion date.
All courses with a seq of 20, 21 must have at least two courses with a completion date
The running total I setup with a formula in the evaluate section but it keeps counting everyone.
On the final report I would like to show org, name, actual completion date (max completion date) if completed or blank if not, a subtotal of those who have completed the prog by org and a grand total.
I have some data and any help would be appreciated. Thanks
Org Course Seq Employee Completion Date
1234 62 1 Smith, Jane 2000/09/21
48 1 Smith, Jane 2000/05/16
84 10 Smith, Jane 1999/12/17
98 10 Smith, Jane
32 20 Smith, Jane 2000/12/18
61 20 Smith, Jane

 

[synapsevampire]

Please supply example data and expected output, along with the table structures, it's hard to figure out what you want from this.

-k

 

[SamDout]

I have a query setup using PeopleSoft / DB2.

Output would look something like this.

Program 1

Org Name Expected Actual
Completion Completion
Date Date
1234 John 6/10/2002 2/5/2001
Jane 5/3/2002
Bill 8/1/2002 9/1/2002
Greg 9/6/2002
Sub Total 4 2
Org
4567 Joe 6/1/2002 6/30/2002
John 5/1/2002 7/1/2002
Betty 5/25/2002
Subtotal 3 2

Grand Total 7 4

 

[synapsevampire]

OK, I still don't know what your data looks like, but perhaps this will suffice:

It appears that you want to only count those that have a completion date, so in the Running Totals->Evaluate->Use a Formula place something like:

not(isnull({table.completiondate}))

-k

 

[SamDout]

Here is some of the data I'm working with. Since there is only one completion date with seq 20 this person has not completed the program

Progm Org. CD EmplidID Est Comp Date Course Code Compl Date Seq
000005 0599 012345 2003-01-10 003848 2000-05-16 1
000005 0599 012345 2003-01-10 004568 2000-02-03 1
000005 0599 012345 2003-01-10 003862 2000-01-28 1
000005 0599 012345 2003-01-10 004566 2000-08-31 11
000005 0599 012345 2003-01-10 004765 11
000005 0599 012345 2003-01-10 000648 2000-08-03 13
000005 0599 012345 2003-01-10 005102 13
000005 0599 012345 2003-01-10 005141 2001-11-01 20
000005 0599 012345 2003-01-10 004632 20
000005 0599 012345 2003-01-10 004584 20

 

[stevefah]

This looks easy if you do a conditional suppression on those detail lines that don't have a completion date.
suppression formula could be as simple as "isnull ({field.completiondate})"

Then do a manual running total.
The running totals doc is available at:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_running_total.zip

You must have a CR registration number to access it.