How do I retrieve previous job code??? 1

[kharismatik]

I am new to Crystal. I am attempting to create a promotions report and am having trouble displaying the previous job & position codes. This report will have a date condition and an personnel action condition. Below is an example of the data.

Emp. Posn Job Begin Date End Date Action
1 10 01 1/1/09 Null Promotion
1 11 02 10/31/08 12/31/08 Salary Incr.
1 11 02 5/30/08 10/30/08 Promotion
1 12 03 1/14/08 5/29/08 Correction
2 14 04 12/14/08 Null Salary Incr.
2 15 05 10/1/08 12/13/08 Promotion
3 16 06 12/21/08 Null Correction
3 17 07 6/25/08 12/20/08 Promotion
3 18 07 3/24/08 6/24/08 Salary Incr.


My desired result would be (begin date >= 1/1/2008, action = Promotion):

Employee 1: Position = 11; Job Code: 02
Employee 1: Position = 12; Job Code: 03
Employee 2: Position = Null; Job Code: Null
Employee 3: Position = 18; Job Code: 07

Can this be done? If so, please help. Thanks!

 

[Madawc]

First, group by employee. But show the details and suppress the group header and footer.

Do a summary count within the group, and also a running total count. The summary will be the total number, while the running total will go 1, 2, 3 etc.

Conditionally suppress the detail section - right-click on the section and choose Section Expert. Choose the formula icon (x+2 and a pencil) for suppression.

Enter a formula:

@SummaryCount - @RunningTotal > 2

For developing, you might find it easier to display the counts and add the suppression once you are sure it is OK.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[kharismatik]

Your solution works perfectly!!! Thank you.

My desired result has changed a bit. Below is the data sample:

Emp. Posn Job Beg Date End Date Action Pay
1 10 01 1/1/09 Null Promotion 10
1 11 02 10/31/08 12/31/08 Salary Incr. 9
1 11 02 5/30/08 10/30/08 Promotion 8
1 12 03 1/14/08 5/29/08 Correction 7
2 14 04 12/14/08 Null Salary Incr. 12
2 15 05 10/1/08 12/13/08 Promotion 11
3 16 06 12/21/08 Null Correction 10
3 17 07 6/25/08 12/20/08 Promotion 8
3 18 07 3/24/08 6/24/08 Salary Incr. 6


My desired result would be (begin date >= 1/1/2008, action = Promotion):

Employee 1: Position = 11; Job Code: 02; Salary: 9
Employee 1: Position = 12; Job Code: 03; Salary: 7
Employee 2: Position = Null; Job Code: Null; Salary: Null
Employee 3: Position = 18; Job Code: 07; Salary: 6

I am attempting to you the previous statement, but it is not working. Please help.

 

[lbass]

Since you have this ordered by employee and then the dates sorted in descending order, you need to use the next() function. Try placing this formula only in the detail section and formatting the detail section to "suppress blank section":

if {table.action} = "Promotion" and
{table.emp} = next({table.emp}) then
"Employee "&{table.emp}&": Position = "&
next({table.posn})&"; Job Code: "&next({table.job})&
"; Salary: "&next({table.pay})

-LB

 

[kharismatik]

When I suppressed the blank section nothing happens. Do I need a formula...conditional formating perhaps?

 

[lbass]

Make sure there is nothing in the section--try looking in design mode. If it is truly empty, "suppress blank section" will work.

-LB

 

[kharismatik]

I see. Is there a way I can suppress the blank fields and display other data in the detail section? For example


Emp Job Old_Job Action
1 Mechanic Apprentice Promotion
1 Apprentice SAL CHG
1 Apprentice Trainee Promotion

As info, I have "broken out" your suggested formula to return individual fields (e.g. old job, old salary). So the data that I am including in the detail section is the employee current information and the employee's most recent information. I need the data to look like this:

Emp New_Job Old_Job Action
1 Mechanic Apprentice Promotion
1 Apprentive Trainee Promotion


Please advise...

 

[lbass]

I don't know where that middle line is coming from, or for that matter, what each field is based on. You would need to show the content of each formula and also show the detail records before and after application of the formulas.

-LB

 

[kharismatik]

I apologize for the confusion. Below is the data prior to the application of the formulas:


Emp. Posn Job Begin Date Action
1 10 01 1/1/09 Promotion
1 11 02 10/31/08 Salary Incr.
1 11 02 5/30/08 Promotion
1 12 03 1/14/08 Correction

Below is the data after the application of the formulas:


Emp. Posn Job Old Job Begin Date Action
1 10 01 02 1/1/09 Promotion
1 11 02 10/31/08 Salary Incr.
1 11 02 03 5/30/08 Promotion
1 12 03 1/14/08 Correction

The formula I am using to return the old job is:

if {PERSACTHST.REASON_01} in ["INTRL HIRE", "LATERAL", "PROMOTION", "RECLASS", "TO FT"] and
{PAEMPPOS.EMPLOYEE} = next({PAEMPPOS.EMPLOYEE}) then
next({PAEMPPOS.JOB_CODE})

My desired result would be:

Emp. Posn Job Old Job Begin Date Action
1 10 01 02 1/1/09 Promotion
1 11 02 03 5/30/08 Promotion

 

[lbass]

I think you could just use section suppression (no checkmark though) with a conditional formula like this:

not({PERSACTHST.REASON_01} in ["INTRL HIRE", "LATERAL", "PROMOTION", "RECLASS", "TO FT"])

-LB

 

[kharismatik]

Thanks, the suppression works!!!

However, when I pull records for several employees simutaneously (within a date range), my "new job" formula does not return anything. Everything works perfectly when I am pull records for one employee at a time. Please advise.

I am still using the following formula:

if {PERSACTHST.REASON_01} in ["INTRL HIRE", "LATERAL", "PROMOTION", "RECLASS", "TO FT"] and
{PAEMPPOS.EMPLOYEE} = next({PAEMPPOS.EMPLOYEE}) then
next({PAEMPPOS.JOB_CODE})

 

[lbass]

I think you are showing the old job formula. What's the new job formula?

-LB

 

[kharismatik]

I am not sure if I understand what you are asking....

I do not have a formula for the new job code as this is populated when the promotion data is entered into the system. In other words {PAEMPPOS.JOB_CODE} is the new job code. I think I may have confused you with the column headings, for that I apologize.

 

[lbass]

However, when I pull records for several employees simutaneously (within a date range), my "new job" formula does not return anything.

-LB

 

[kharismatik]

I apologize....it should have read "....old job formula...

 

[kharismatik]

Please allow me to clarify. Below is the data prior to application of any formulas:

Emp. Posn Job Beg Date Action Pay
1 10 01 1/1/09 Promotion 10
1 11 02 10/31/08 Salary Incr. 9
1 11 02 5/30/08 Promotion 8
1 12 03 1/14/08 Correction 7
2 14 04 12/14/08 Salary Incr. 12
2 15 05 10/1/08 Promotion 11
3 16 06 12/21/08 Correction 10
3 17 07 6/25/08 Promotion 8
3 18 08 3/24/08 Salary Incr. 6


Below is my desired result after application of the formulas:

Emp. Posn Job Beg Date Action Pay Old Job
1 10 01 1/1/09 Promotion 10 02
1 11 02 5/30/08 Promotion 8 03
2 15 05 10/1/08 Promotion 11 Null
3 17 07 6/25/08 Promotion 8 08

As I stated earlied, your solution works perfectly if looking at one particular employee. However, for multiple employees during a specified date range, I do not get the "old job" code. Please advise.

 

[lbass]

I don't see why that would happen. For the above dataset, what DO you get using the formula? Are you sorting by employee ID and by date descending?

-LB