New and Previous Value in Crystal Report

In my report, I have to have 2 fields, one that's called previous department and the other is called new department for an employee.
 That database stores this value in a field called A_Value.
 Emp       Dept
 12345    91204
 12345    93000

 My question is how can I have all this information displayed on 1 line?
 Expected result:

 Emp     Old Dept      New Dept
 12345  93000           91204

 Thanks for all you help guys
Who is Participating?
mlmccConnect With a Mentor Commented:
For the first record of an employee, there is no previous department.  

You can put whatever you want there.

It should either be blank to indicate there is no previous department a fixed value like No Prev Dept

Are you bringing in all the records?

Does anyone have more than 2 records?

Group on the Employee ID
In the group footer add a formula
Name - OldDept
If Not(PreviousIsNull({EmployeeID})) AND Previous({EmployeeID}) = {EmployeeID then

Open in new window

metalteckAuthor Commented:
Hi mlmcc,

Yes, I'm bringing in all records.
And yes, employees can have more than 2 records.

I used your code and its working, but there is just one issue.
The starting previous value is always null on the first line. The other values are correct.
How can I get that value populated?


Employee       Previous Value     New Value
12345                                             90601
12345              90601                    54007
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

The first record for an employee can't have a previous value since it is the first record.

metalteckAuthor Commented:
I understand that.
But for auditing purposes, I can't have a blank value there.
I need to have the starting value listed.
Try this

If Not(PreviousIsNull({EmployeeID})) AND Previous({EmployeeID}) = {EmployeeID then

Open in new window

metalteckAuthor Commented:

I tried using your code, but if I use the deptfield in the else clause, that gives me the current department.
Would I be able to do this if I used variables or in sql?
James0628Connect With a Mentor Commented:
If you don't want to use something like "No previous dept", and you want to display "the starting value", like you mentioned before, then I think the question is, how do you define "starting value"?

 For "previous dept" and "new dept" to make sense, I would assume that the records were somehow sorted in department assignment order, so that the first record for an emp has their first department, and the last record has their last department.  But, from what you've said, that is not the case.

 Or are you maybe doing something like only including specific dates in the report, and the "starting value" that you're looking for is from older records that aren't included in the report?

 Either way, how would you identify their first department?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.