Print Page | Close Window

retrieving old field from report

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19995
Printed Date: 02 May 2024 at 2:41am


Topic: retrieving old field from report
Posted By: bwsanders
Subject: retrieving old field from report
Date Posted: 06 Sep 2013 at 7:11am
Hello all!

I have a report that is evaluating employee rates from a date driven table. Each rate has an effective start date and a rate end date. My report is based off a start date and an end date that the user will supply to the report before running and what I'm trying to do (and failing miserably) is:

If an employee received a rate change show the date of the change and show the new salary. That part is easy enough and works perfectly however, the client would also like to see the difference between the new salary and old salary.

to get the new salary I'm able to use :
If {Rate.startDate} IN {@YYYStartDate} TO {@YYYEndDate} Then
    {@Salary}

and to get the old salary I'm using:
If {Rate.endDate} IN {@YYYStartDate} TO {@YYYEndDate} Then
    {@Salary}

to be as clear as i can the YYYStartDate and YYYEndDate are supplied from the user and Salary is a basic math formula taking the rate * pay frequency * number of pays in a year

If there is anything that maybe I'm doing wrong or if anyone has any ideas I'd love to hear them as I have tried everything that I can to get the old rate to figure the old salary. If i pull the SQL data from the report i can see that the old rate is there and for whatever reason not coming through to my report.

Thank you in advance.




Replies:
Posted By: DBlank
Date Posted: 06 Sep 2013 at 7:22am
so you haev one table with possible multiple rows per employee. columns as  
employeeid
startdate (day rate went into effect)
end date (day rate endded and null for current rate)
rate
 
you user can enter a date range (begin and end date)
you need to retrieve all clients with all rates that were effective at anypoint during that range and show the annual salary?

Posted By: bwsanders
Date Posted: 06 Sep 2013 at 7:32am
basically.

rate.startdate(day rate started)
rate.enddate(08/24/2013) unless the rate is still active then it shows as rate.enddate(12/31/2100)

what i need to show is if the employee got a raise i need to show his name, the startdate of the new rate, the new salary and the difference between old and new salary

so....

employee: john smith
rate.startdate: 08/01/2013
rate: 10.00
rate.startdate: 08/24/2013
rate: 12.00
rate.enddate: 08/23/2013
rate.enddate: 12/31/2100

this would need to show like this
name         | new rate date | salary      | difference
john.smith | 08/24/2013     | 24960.00 | 4160.00

Posted By: DBlank
Date Posted: 06 Sep 2013 at 7:34am
what if they got more than one raise during the date range?

Posted By: bwsanders
Date Posted: 06 Sep 2013 at 7:40am
well, luckily when looking throught he database no one has yet.....although i'd wanna do a cya and make sure that I am able to see that as well providing that they all fall in during the dates provided.

this report would really only ever run on a 30 day window so i doubt that someone would get several raises in that time but it;s possible.

Posted By: DBlank
Date Posted: 06 Sep 2013 at 8:08am

if someone does please send me a link to your HR services.

 
I assume your record select is something like
(rate.startdate<?enddate and rate.enddate>?startdate)
 
group on employee (concantenate the name with an ID to get a unique record per employee but still alpha per your preference)
I assume you have a PK from your table
do a distinct count on it at the employee level
do a group select to look for that value>1 to remove all of the employees with only one record
e.g. distinctcount(pk,employee)>1
hide your details and group header
in the group footer place the fields you want
name         | new rate date | salary      | difference
john.smith | 08/24/2013     | 24960.00 | 4160.00
 
 
name (and ID if needed) = the name field
new rate date = maximum(startdate,employee)
salary = maximum(rate,employee)*workdaysvalue
difference = (maximum(rate,employee)- minimum(rate,employee)) *workdaysvalue
 

Posted By: bwsanders
Date Posted: 06 Sep 2013 at 8:12am
yeah, that's pretty much how my report is already. the only thing I'm trying to not use max or min because some of the employees who got a pay change actually stepped down in pay. so max would pull the wrong data for rate. 

Posted By: DBlank
Date Posted: 06 Sep 2013 at 8:19am
Maybe I dont want that link to the HR department...
maybe this..?
create a formula called RateCalc
if rate.startdate=(maximum(rate.start,rate.employee) then rate.rate else
if rate.startdate=(minimum(rate.start,rate.employee) then rate.rate *(-1)else 0
difference  SUM(@rateCalc, rate.employee) * workdaysvalue
Would also show negative amounts for step downs

Posted By: bwsanders
Date Posted: 06 Sep 2013 at 8:31am
ill give this a try and see what i come up with.

i'm not sure what you meant by link to hr department. did i miss something heh.

Posted By: DBlank
Date Posted: 06 Sep 2013 at 8:34am
I wanted their phone number (link) if you had a bunch of employees pop up on your report with more than one raise in a 30 day period. but now that it could be more than one demotion i am rethinking my request.
Good luck with the report.

Posted By: bwsanders
Date Posted: 06 Sep 2013 at 8:41am
haha. gotcha.

yeah, i may need to just scrap this and start all over with a different view point maybe. i applied your RateCalc and it gives me the same rate as the current rate.

Posted By: DBlank
Date Posted: 06 Sep 2013 at 9:03am
hmmm.
using your john smith example and showing details and placing the RateCalc formula on teh detail section you should have
john smith   8/1/13  $10.00  @ratecalc = (-10.00)
john smith   8/24/13  $12.00 @ratecalc = 12.00
 
the sum of the @ratecalc (at the employee gorup level) is 2.00
if you multiply that by the workdays you should get the difference amount you are looking for.
 
is this what you see? if not what do you see?
 
EDIT - i noticed i was missing some parenthesis in the original @ratecalc formula

Posted By: bwsanders
Date Posted: 06 Sep 2013 at 9:24am
yeah i put the parens in no biggie. i'm only getting one line for the recent rate for some odd reason. i don't get it. 

Posted By: DBlank
Date Posted: 06 Sep 2013 at 9:42am
did you use the group select to only show employees that have two rows of data?

Posted By: bwsanders
Date Posted: 09 Sep 2013 at 6:15am
UPDATE: i'm using a group on rate.endDate in desc order  and then using the Previous() to pull the previous endDate in the group. this is working well. don't know why this didn't come to mind earlier.

thanks as always for your help DB

Posted By: DBlank
Date Posted: 09 Sep 2013 at 6:39am
just beware any items that have more than 1 change.
That is why I was suggesting the min/max rather than a previous/next


Print Page | Close Window