Duplicates

[mercadi]

Ok, I'm going to give this my best shot in explaining the help I DESPERATELY need. First, let me say, I'm not an expert at Crystal so please bare with me and I hope someone will help me....this will be somewhat lengthy. I use Crystal 9

Here is what I need my report to end up looking like.

EmpName Termination Date Event Type


Here is what is happening.

Let's say an employee terminates twice in one year. When a employee terms the new term date is entered into our KRONOS system along with an event (event describes why they termed, whether it was Resignation or Discharge, etc.

Now, when I go into Crystal to pull my data from the tables, I pull Employee Name, Termination Date from one table and Event Code from another table (these are linked). What I end up getting from those employees that termed twice in one year is this:

Name: Term Date: Event Code
Ima Test 6/01/06 1 Resignation
Ima Test 6/01/06 2 Discharge

This may look right, but I only want to see the latest and greatest term date. Lets say the event code 1 Resignation (shown above) was really for the first time the employee termed in the year and the term date in the KRONOS system was 01/01/06 but the new term date 06/01/06 overrides the old term date but puts the old event code with it.

I need to somehow show the employee with the most recent term date with the correct event code. Now I have 10 differant event codes and when I do a SELECT EXPERT, to select my 10 codes that appears to be the reason it pulls in duplicates because each time the employee termed they showed to have differant EVENT Codes but the New term date.

Does this make sense? Here is what is in my Reports Record selection.

{TMC_EmployeePay_Job_Curr.EmployeeStatus} = "Terminated" and
{TMC_EmployeePay_Job_Curr.TerminationDate} = {?Term Date} and
{TMC_EmployeePay_Job_Curr.PayStatusCompensationType} = "BASE" and
{EVENT_CODES.EventCodeToEffectDate} = DateTime (3000, 01, 01, 00, 00, 00) and
{EVENT_CODES.EventCode} in ["1 RESIGNATION", "2 DISCHARGE", "3 LAY OFF", "4 RETIREMENT", "5 TEMPORARY/CONTRACT", "6 ON CALL/INACTIVE PRN", "7 DECEASED", "8 BUSINESS/FACILITY CLOSU", "9 COMPLETED INTERN OR RES", "10 RIF"]

Thanks and I hope someone can help me, because I can't figure how how to write a formula on this.

 

[lbass]

How do YOU know which termination code goes with the term date? Is there some field that creates a sequence in the event code table? Or do you have an employee history table?

-LB

 

[mercadi]

Sorry this is going to be lengthy again, because its hard to explain and I know you guys like to have all the information since you can't actually see the report:

I have an EVENT screen in our HRMS (KRONOS)system where we log the information in field boxes. Event Field (what type of termination), Event Date (which happens to be term date), etc. Once we save the info, it appears in a View window at the top of the page (almost like a mini report)and you can see each field so you can see each time the employee termed in any givin year and why.(Yeah, some employees come and go like crazy). Anywho, KRONOS does have standard reports that you can go in and pull data, but the standard reports don't normally show the info needed so I have to then use Crystal to pull out the data. I go find the tables in Crystal that have the required information and then link them together if I use more then one table. For some reason, the EVENT table doesn't have EVENT DATE (which so happens to be the termination date) so I have to go into my EMPLOYEE table to find the termination date (this data comes from a differant screen where employee information is inputted)(term date is also logged in under the Employee Screen as Termination Date). Your probably wondering why term date is logged twice........The EVENT DATE FIELD on the EVENT screen just happens to be the term date too but due to the EVENT table in crystal not having a field called event date, I have to pull the term date from the employee table and that is where I have my problem because I'm telling Crystal to show me the employee latest term date but I also want to see what the event type was(remember I have 10 event types: Discharge, Resignation, layoff, etc)and since the employee termed twice in 1 year and had a differant event type for each term, it gives me the latest term date but shows me all the event codes WITH the latest term date when the term dates should be differant for each event code.

Is there a way to write a formula to make the report show ALL term dates and then maybe use a MAX formula on the term date?

Thanks a million if anyone can figure this out.

 

[lbass]

You didn't really answer my questions, and from your first post it sounds like the date is simply overwritten, not stored, so where could "ALL term dates" be pulled from? If you do have a place where multiple term dates are stored, please specify and then show some sample data.

-LB

 

[mercadi]

I thought the data was overwritten but there is a button on the employee screen that is ALL RECORDS and when you click on it it pulls up an individual little box that shows all the dates this employee has hired on, termed on, rehired on, etc. I'm just not sure where the data is stored and how to find all the Term dates.

You asked:how I knew which term date went with each code: When I pull up an employee in KRONOS the screen will show me individual fields and I can see what eventcode was entered on what date. I just don't know where the physical data is stored behind the scene.

You asked:Is there some field that creates a sequence in the event code table? Not sure what a sequence is. When I pull the EVENT CODE Table up it only has fields for me to choose (like eventcodeIdNo, EventCode, EventType, EventEffecttoDate, etc). Same with Employee table.

I'll keep looking for a table where possible ALL TERM dates are stored. I just know the tables I normally use for creating reports only pulls the latest term date.

 

[dgillz]

What is the database type? SQL?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[hovercraft]

Does Kronos support v9? I was under the impression that you couldn't use CR9 with Kronos.-I read this in a post on this forum.


Thanks,
Hovercraft
-curious myself because I will soon be using CR with Kronos

 

[mercadi]

We use SQL. I use CR9 for almost all my reports. I do run into some problems with HISTORY but for the most part I have no problems pulling from KRONOS.

 

[synapsevampire]

We use SQL...does that mean MS SQL Server? SQL is a database programming language common to many databases.

There are plenty of existing stored procedures that allow for searching all fields in a database for a specific value and will return that table.field where the value is stored, which makes this discovery process a snap.

Add to that you can use a trace in SQL Server and other databases so that you can get to the point just before the screen that shows your data, turn it on, open the screen, which the trace has then captured the sql sent, turn off the trace and then you know precisely what is being used by Kronos.

Both of the above are extremely important tools when performing reverse engineering, and any business intelligence processes, as well as QA and unit testing.

As for using CR 9 with Kronos, probably any version will work, here's a blurb:

http://www.bobjects.com/email_imp/kronos/store.html

-k