• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

show date for last time field was updated

I am creating a Crystal Report in Version 10. I have a table with employee information and a sample looks as follows:

Emp ID         Class          Rate            Effective Date
100             acct             20.00           10/01/2010
100             acct             22.00            3/01/2011
100             acct             21.75            6/1/2011
100             hr                21.75            8/1/2011

I need to show a summary line that shows current class and pay rate and the date for the last pay rate change. (not the last date record changed)

For example:

Emp ID        Class          Rate               Date of Last Rate Change
100              hr               21.75             6/1/2011

By grouping by employee ID and sorting by date and using max, I can get the current class and rate, but I cannot get the date for the rate change. Unfortunately sorting by rate won't work because the change in rate is not always an increase.
Ideas?
 
0
flynn60
Asked:
flynn60
  • 2
  • 2
1 Solution
 
mlmccCommented:
Group by EmpId
Sort by date (Descending)
Put the information in the group header

mlmcc
0
 
flynn60Author Commented:
This has the same result as sorting ascending and putting info in footer. You get the last date on the record and not the date where the data changed.
0
 
mlmccCommented:
Try this idea

Add a formula to the report header
WhilePrintingRecords;
Global NumberVar CurrentRate := -1;
Global DateVar RateChangeDate;
""

In the group header
WhilePrintingRecords;
Global NumberVar CurrentRate;
Global DateVar RateChangeDate;
CurrentRate := {RateField};
RateChangeDate := {DateField};
""

In the details add a formula
WhilePrintingRecords;
Global NumberVar CurrentRate;
Global DateVar RateChangeDate;
If CurrentRate <> {RateField} then
(
    CurrentRate := {RateField};
    RateChangeDate := {DateField};
);
""

In the group footeradd 2 formulas to display the data
WhilePrintingRecords;
Global NumberVar CurrentRate;
CurrentRate


WhilePrintingRecords;
Global DateVar RateChangeDate;
RateChangeDate

mlmcc



0
 
flynn60Author Commented:
Thank you! It worked perfectly.
0

Featured Post

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now