Current and Previous Row on One Line 2

[FireGeek21]

I need to do a report on job history that will show the previous job and department on one line with the current job and department along with the effective date of the change.

In the table, the data looks like this:

EMPLID EFFDT JOBCODE DEPT
1234 01/01/2006 100101 100
1234 02/28/2005 501100 500
1234 04/01/2009 321101 300
1234 01/01/2011 900901 900
9876 05/01/2002 600601 600
9876 05/01/2007 100101 100
9876 07/01/2011 400100 400

What I need to see is the following:

EMPLID EFFDT OLD_JOB NEW_JOB OLD_DEPT NEW_DEPT
1234 01/01/2011 321101 900901 300 900
9876 07/01/2011 100101 400100 100 400

So, I need to see the current row of data and the previous row of data per employee all on one row with the most current effective date.

I looked at the Next() and Previous() functions but found they refer to the report data and not the table data.

Suggestions? THANK YOU!!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[briangriffin]

1. Group on EmpID

2. Make sure the sort is by effective date descending. This won't work if the sort is wrong. if you click the A->Z button, it should look like this:

Sort Fields
Group 1: EmpID
D - Effective Date


If the effective date happens to be a string, create a formula to convert it to a date and do the sort descending on the formula.

3. Put this formula in the group header:

//@Reset
whileprintingrecords;
numbervar v_counter := 0

4. Put this formula in the details section:

//@Counter
whileprintingrecords;
numbervar v_counter;

v_counter := v_counter + 1;

v_counter

4. Put this formula in the details section:

//@EffectiveDate
whileprintingrecords;
stringvar v_effectivedate;
numbervar v_counter;

if v_counter = 0
then 
v_effectivedate := {Sheet1_.EFFDT        }
else
v_effectivedate;

v_effectivedate

So we're saying if the record is the first one in the group (counter = 1), we know it's the employee's current position so that's the date we want.

5. Put the same formula in the group footer. When you refresh the report you should see the correct effective date for each employee.

6. You need to create formulas for old job, new job, old dept, and new dept. Each one will go both in the details section and the group footer.

//@OldJob

whileprintingrecords;
stringvar v_oldjob;
numbervar v_counter;

if v_counter = 2
then 
v_oldjob := {Sheet1_.JOBCODE    }
else
v_oldjob;

v_oldjob

//@NewJob
   
whileprintingrecords;
stringvar v_newjob;
numbervar v_counter;

if v_counter = 1
then 
v_newjob := {Sheet1_.JOBCODE    }
else
v_newjob;

v_newjob

//@OldDept

whileprintingrecords;
numbervar v_olddept;
numbervar v_counter;

if v_counter = 2
then 
v_olddept := {Sheet1_.DEPT}
else
v_olddept;

v_olddept

//@NewDept

whileprintingrecords;
numbervar v_newdept;
numbervar v_counter;

if v_counter = 1
then 
v_newdept := {Sheet1_.DEPT}
else
v_newdept;

v_newdept

Once you've validated the data is correct, hide or suppress the details section and the group header. Add the Employee ID to the group footer.

 

[FireGeek21]

Hi...

I have finally had an opportunity to apply the above solution but am seeing a problem. Was hoping for an additional looksey!

In the table, the data looks like this:

EMPLID EFFDT JOBCODE DEPT
1234 01/01/2006 100101 100
1234 02/28/2005 501100 500
1234 04/01/2009 321101 300
1234 01/01/2011 900901 900
9876 05/01/2002 600601 600
9876 05/01/2007 100101 100
9876 07/01/2011 400100 400

What I need to see is the following:

EMPLID EFFDT OLD_JOB NEW_JOB OLD_DEPT NEW_DEPT
1234 01/01/2011 321101 900901 300 900
9876 07/01/2011 100101 400100 100 400

What I am seeing is if the previous person had more than 2 old history rows, the previous person's "old data" is showing for my current person! So, from the above example, here is what I am now seeing...

EMPLID EFFDT OLD_JOB NEW_JOB OLD_DEPT NEW_DEPT
1234 01/01/2011 321101 900901 300 900
9876 07/01/2011 321101 400100 300 400

The new data is correct but the old is not. Now, this "old data" will continue to be passed down to other employees until it hits someone with more than 2 old history rows!

Maybe a tweak of the "old" code somehow? Help?

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[briangriffin]

I rebuilt this using the new data but my results are the same as your desired results. Please double check to see that all the formulas are correct and in the right sections.

The first place I would look is the reset formula in the group header, since you seem to have debris moving from group to group. The number of records per group shouldn't matter since we are using the counter variable to identify the first two records of each group.

 

[FireGeek21]

So, the EffectiveDate and the Counter formulas go in the Details section?

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[briangriffin]

Yes. If you still can't get it working I'll figure out a way to put my test version in shared storage someplace for you to download and compare to your version.

 

[FireGeek21]

Brian,

Every group, sort, formula matches up exactly. I can't see where this is going wrong. It seems when there are 2 or more rows of history, the reset works. But for 1 row of history, it is using the "old" value from the previous employee.



FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[lbass]

To intrude here, you should be using new formulas for the display in the group footer, not the same formula, so for old dept:

//{@displayolddept}:
whileprintingrecords;
numbervar v_olddept;

Repeat for the other variable. I would also add these variables to the reset formula for the empl ID group header.

-LB

 

[briangriffin]

Since the variables are being incremented conditionally the new formulas aren't technically required, but it might help for troubleshooting.

I would also unsuppress the counter and reset formulas. You should see a 0 in the group header for the reset, and 1, 2, 3, etc. for each detail record.

 

[lbass]

Brian,

I guess you are right about separate display formulas not being necessary in this case, but I don't think it is best practice (not that what I do is always either!).

FireGeek21,

If you change the reset formula to:
//@Reset
whileprintingrecords;
numbervar v_counter := 0;
numbervar v_olddept := 0;
numbervar v_newdept := 0;
numbervar v_oldjob := 0;
numbervar v_newjob := 0;

---the problem should be eliminated.

-LB

 

[FireGeek21]

ARUGH!!!

Problem persists and it still appears with records that have only one history row.

When there is only one history row, there is no "old" value. How can we get a "blank" to print in these cases? It appears to be grabbing the previous employee's old value for these instances.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[lbass]

Did you add the reset formula? I realized the resets should be using stringvar for old and new job (tested using numbers):

whileprintingrecords;
numbervar v_counter := 0;
numbervar v_olddept := 0;
numbervar v_newdept := 0;
stringvar v_oldjob := 0;
stringvar v_newjob := 0;

Make sure your variable names are the same in all formulas, too.

-LB

 

[briangriffin]

I wondered if this would work for employees with only one record, and the answer is no, so I added a new employee to my sample data.

When I do this, and add the resets for all the variables, my result for the single record employee looks like:

9999 9/1/2011 [blank @OldJob] 77777 (NewJob) 0 [@OldDept] 555 (NewDept)

So if you can get to this point then we can make some minor changes to the display of one-record employees.

 

[FireGeek21]

Here are my formulas:

//@RESET in Group Header
WhilePrintingRecords;
NumberVar v_counter := 0;
NumberVar v_OldFTE := 0;
NumberVar v_NewFTE := 0;


//@EFFDT in Detail
WhilePrintingRecords;
StringVar v_effdt;
NumberVar v_counter;

IF v_counter = 0
THEN v_effdt := ToText(HRHISTORY.BEG_DATE}, "yyyyMMdd")
ELSE v_effdt;

v_effdt


//@COUNTER in Detail
WhilePrintingRecords;
NumberVar v_counter;

v_counter := v_counter + 1;

v_counter


//New FTE in Detail
WhilePrintingRecords;
NumberVar v_NewFTE;
NumberVar v_counter;

IF v_counter = 1
THEN v_NewFTE := {HRHISTORY.N_VALUE}
ELSE v_NewFTE;

v_NewFTE


//New FTE in Footer
WhilePrintingRecords;
NumberVar v_NewFTE;


//Old FTE in Detail
WhilePrintingRecords;
NumberVar v_OldFTE;
NumberVar v_counter;

IF v_counter = 2
THEN v_OldFTE := {HRHISTORY.N_VALUE}
ELSE v_OldFTE;

v_OldFTE


//Old FTE in Footer
WhilePrintingRecords;
NumberVar v_OldFTE;

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[briangriffin]

Everything matches my report, except you still need to modify the reset formula:

whileprintingrecords;
numbervar v_counter := 0;
numbervar v_oldFTE := 0;
numbervar v_newFTE := 0;
stringvar v_oldjob := "";
stringvar v_newjob := "";


And aren't you missing the formulas for the departments?

 

[lbass]

Sorry for my sloppiness on the reset formula. Strings obviously must be set to "".

-LB

 

[FireGeek21]

Thank you LBass and Brian Griffin for all your help with this!!! I ended up starting from scratch and was able to get the report working.

There seems to be a bit of a lingering issue. If the counter only = 1 (not more than 1) such as in the case of a hire or rehire, the old job/dept is showing as the previous employee's data and not a blank. I have tried a few things to get it to show as a blank with no success. Thoughts???

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[FireGeek21]

HA! I believe I found the issue! Helps to declare the variables in the header of the counter for ALL variables and set them to 0 or "" !!!

Thanks again for all your help LBass and Brian!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)