Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

New and Previous Value in Crystal Report

Posted on 2016-10-09
8
61 Views
Last Modified: 2016-11-04
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.
 Ex:
 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
0
Comment
Question by:metalteck
  • 4
  • 3
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 41836823
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
    Previous({DeptField})   

Open in new window


mlmcc
0
 

Author Comment

by:metalteck
ID: 41837088
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?

example

Employee       Previous Value     New Value
12345                                             90601
12345              90601                    54007
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41837114
The first record for an employee can't have a previous value since it is the first record.

mlmcc
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:metalteck
ID: 41837130
I understand that.
But for auditing purposes, I can't have a blank value there.
I need to have the starting value listed.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41837461
Try this

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

Open in new window


mlmcc
0
 

Author Comment

by:metalteck
ID: 41848874
mlmcc,

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?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 41849050
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

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 41849297
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?

 James
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Crystal Reports Date Calculation 10 42
Cyrstal Reports formula to check IsNull 8 37
Supress a section of a field 6 19
Sum Before Standard Deviation 2 20
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question