We have a slew of courses set up in TR21. I need a report that will show:

Column 1:
Course Completion Date

Column 2:
Complete Status: "F" (or not - some totext statement here)

How can I do this and verify that the "F" is for that particular course?

Course Completion Date and Complate Status are both on PATRNHIST. If you need the Course Description, looks like PACOURSE (join to PATRNHIST on COURSE field) is what you need.

:werd:

I just need to show the Stats next to the Course in the report.

So, for example:

Employee Course Stats

Joe Blow WS0000117 "F" (I will want to make a TOTEXT "Complete" or something)


I am dropping the right fields in the report but am not sure if that "F" is for that course (WS000017) ?? Make sense??

if {PATRNHIST.COMPLETE_STATS} = "F"
and {PATRNHIST.COURSE} = "WO00000117" then
TOTEXT "Complete" else
""

You're just about there, but you are making it harder than it needs to be. For the Status formula, try the following:

if {PATRNHIST.COMPLETE_STATS} = 'F' then 'Complete With Competencies' else
if {PATRNHIST.COMPLETE_STATS} = 'N' then 'Complete With No Competencies' else
if {PATRNHIST.COMPLETE_STATS} = 'S' then 'Partial With Competencies' else
if {PATRNHIST.COMPLETE_STATS} = 'P' then 'Partial With No Competencies' else 'Undefined'

Drop that formula in the Detail section of the report next to your other two fields.

Ok, then if I put this in the report after each column (which is a course) it will represent that particular course? In other words,

Employee's run down column 1, then every column after that is a course

So, Column1-Employee, Column2-CourseABC, Column3-Stats?

(kinda of like cross-tab)

That should do the trick. Let me know if you have any problems.

Hey - So I got this to work, but I have another issue:

I need to count and add a percentage of completed tests on the footer. Now, the selections I have in place are for each course returning the text "Complete" "Incomplete", etc..

So, I have 12 employee's in this one department, with 8 courses each. How can I count this, and provide a percentage. Keep in mind that I am place Nth Largest dates, and Nth smallest on Status (Complete/Incomplete)

If you want to "save report with data" and email it to me at cjmart@swbell.net, I'd be happy to take a look at it.

Mil0n023:

The formula that cjmart gave you for COMPLETE-STATS is encoded in TR21's Form in $FORM TR211, the $EDITS section. (There is corresponding and more explanatory documentation when you run dbdoc on PATRNHIST, but the Form is what the user actually interacts with.)

A lot of times, when you want to populate a Crystal Reports report with meaningful data for cryptic codes, you can't link to code descriptions because the descriptions actually reside in a Form's $EDITS section. So as cjmart showed, you have to set up your own special Formula field to do the translation.

:blink: Thanks Mil0 - CJMart and I talked "offline" about this. The percentage works, but now I need to total them - it will take time that I probably don't have! DOH!

There's a lot of neat stuff you can do with Formula fields. You should be able to figure it out. :thumb: Also, check out the sums -- they are pretty powerful. I have used them to create percentages and percent-of-totals.

(You should be thinking now, "So that's why they made me take math in grade school!" ;) )