Crystal 8.5 - Calculating Leave of Abs from Leave History Data

[Chinnymerlgrove]

The following sample data shows a Leave/Return from Leave job history report for 4 employees. The first column identifies which rows are the GH- Group Header and D-Detail. The data is grouped on ({PS_JOB.EMPLID}). What I need to determine on the Group Header row, is how many times in 2006 the employee was on Leave and for how many
days each time. Below each employee rows is what the result should be.

a = {PS_JOB.EMPLID}
b = {PS_NAMES.NAME}
c = {PS_JOB.EFFDT}
d = {PS_JOB.ACTION}
e = {PS_JOB.ACTION_REASON}

Sample Data
PH a b c d
GH1 EMP1 Employee 1
D1 EMP1 Employee 1 2006-04-17 PLA
D2 EMP1 Employee 1 2006-04-24 RFL
D3 EMP1 Employee 1 2006-06-26 PLA
D4 EMP1 Employee 1 2006-08-14 RFL

What the results should look like for EMP1
1st leave Period = 4/17/06 - 4/24/06 for a total of 7 days
2nd leave period = 6/26/06-8/14/06 for a total of 49 days
3rd Leave period =
Total Leave period = 56 days

GH1 EMP2 Employee 2
D1 EMP2 Employee 2 2005-07-25 PLA
D2 EMP2 Employee 2 2005-10-24 LOA
D3 EMP2 Employee 2 2006-02-13 RFL
What the results should look like for EMP2
Comment: Rows D1 & D2 are included in the calculation because the corresponding RFL row is in 2006
1st leave Period = 7/25/05 - 2/13/06 for a total of 203 days
2nd leave period =
3rd Leave period =
Total Leave period = 203 days

GH1 EMP3 Employee 3
D1 EMP3 Employee 3 2004-07-12 LOA
D2 EMP3 Employee 3 2004-08-23 RFL
D3 EMP3 Employee 3 2006-06-08 PLA
D4 EMP3 Employee 3 2006-07-03 RFL

What the results should look like for EMP3
Comment: Rows D1 & D2 are not calculated because the corresponding LOA/PLA and RFL rows are before 2006
1st leave Period = 6/8/06 - 7/3/06 for a total of 25 days
2nd leave period =
3rd Leave period =
Total Leave period = 25 days
GH1 EMP4 Employee 4
D1 EMP4 Employee 4 2005-08-22 PLA
D2 EMP4 Employee 4 2005-09-06 RFL
D3 EMP4 Employee 4 2006-01-31 LOA
D4 EMP4 Employee 4 2006-02-06 RFL
D5 EMP4 Employee 4 2006-08-28 PLA
D6 EMP4 Employee 4 2006-09-06 RFL
D7 EMP4 Employee 4 2006-09-12 PLA
2006-09-20 - Current Date
What the results should look like for EMP4
Comment: Rows D1 & D2 are not calculated because the corresponding LOA/PLA and RFL rows are before 2006
1st leave Period = 1/31/06 - 2/6/06 for a total of 6 days
2nd leave period = 8/28/06 - 9/6/06 for a total of 9 days
3rd Leave period = 9/12/06 - Current Date (9/21/06) for a total of 9 days
Total Leave period = 24 days

 

[Madawc]

Please post the formula you are using.

Have you tried using PREVIOUS? As with

 {PS_JOB.ACTION_REASON} = "RFL" and PREVIOUS({PS_JOB.ACTION_REASON}) = "PLA"

Of course your data shows other record types. If they are needed for other aspects of the report, maybe the function needs to be separated as a subreport. For each RFL, look back for the previous PLA in a subreport? Return the value using a Shared Variable.

Subreports eat up a lot of machine-time, but may be the simplest way to get the answer you need.




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

 

[Chinnymerlgrove]

Thank you Madawc for your response.

The problem is I did not know how to write the syntax so I don't have a formula.

What I want my formaula to do is as follows.

If the {PS_JOB.ACTION_REASON} for the first row (D1)is (PLA or LOA) then look for the fist instance where the {PS_JOB.ACTION_REASON}is RFL (D2)then
Compare the Year of the {PS_JOB.EFFDT}. If the year for at least 1 row being compared is 2006, then Subtract the (PLA or LOA) {PS_JOB.EFFDT} from the RFL {PS_JOB.EFFDT} to get the + difference in days. Record the value.

If both Years of the {PS_JOB.EFFDT} are before 2006 then do not calculate

Loop, and do the same thing for the remaining rows within the ({PS_JOB.EMPLID}) group. Record the value.
ID Name Efftd Action
D1 EMP4 Employee 4 2005-08-22 PLA
D2 EMP4 Employee 4 2005-09-06 RFL
D3 EMP4 Employee 4 2006-01-31 LOA
D4 EMP4 Employee 4 2006-02-06 RFL
D5 EMP4 Employee 4 2006-08-28 PLA
D6 EMP4 Employee 4 2006-09-06 RFL
D7 EMP4 Employee 4 2006-09-12 PLA
2006-09-20 - Current Date

Can you help me any further?

 

[synapsevampire]

Here's the theory for handling this:

Group by the employee ID, sort by the dates ascending.

In the group header place a formula of:

whileprintingrecords;
datevar array PLA;
datevar array RFL;
redim array PLA;
redim array RFL;
stringvar output:="";

Details formula:
whileprintingrecords;
datevar array PLA;
datevar array RFL;
If {table.code} = "PLA" then
redim array PLA[ubound(PLA)+1];
PLA[ubound(PLA)]:= {table.date};
If {table.code} = "RFL" then
redim array PLA[ubound(RFL)+1];
RFL[ubound(RFL)]:= {table.date};
"blah"

Group Footer formula:
whileprintingrecords;
datevar array PLA;
datevar array RFL;
numbervar counter;
for counter := 1 to ubound(PLA) do(
if ubound(pla) = ubound(rfl) then
Output:=Output+"Period " & totext(counter,0,"") & ": " & pla[counter] & " - " & rfl[counter] & ", for a total of " & totext((rfl[counter] - pla[counter]),0,"") & chr(13)
else
"Period " & totext(counter,0,"") & ": " & pla[counter] & " - not returned yet," & " so far for a total of " & totext((currentdate - pla[counter]),0,"") & chr(13)
);
left(Output,len(Output)-1)

Suppress the groupheader and details sections and this should get you very close, if not there.

-k

 

[synapsevampire]

Sorry, tweak the details formula to:

Details formula:
whileprintingrecords;
datevar array PLA;
datevar array RFL;
If {table.code} = "PLA" then
(
redim PLA[ubound(PLA)+1];
PLA[ubound(PLA)]:= {table.date};
);
If {table.code} = "RFL" then
(
redim PLA[ubound(RFL)+1];
RFL[ubound(RFL)]:= {table.date};
);
"blah"

-k

 

[synapsevampire]

Sorry, still wrong, use:

Details formula:
whileprintingrecords;
datevar array PLA;
datevar array RFL;
If {table.code} = "PLA" then
(
redim PLA[ubound(PLA)+1];
PLA[ubound(PLA)]:= {table.date};
);
If {table.code} = "RFL" then
(
redim RFL[ubound(RFL)+1];
RFL[ubound(RFL)]:= {table.date};
);
"blah"

-k

 

[Chinnymerlgrove]

This looks like a great start. I will go try it and let you know how it works.

Thanks so much!

 

[Chinnymerlgrove]

See error messages for each formula.

//Group Header
//error message says "a variable name is expected here" at the redim lines

whileprintingrecords;
datevar array PLA;
datevar array RFL;
redim array PLA;
redim array RFL;
stringvar output:="";

//Detail records
//error message says "A date is required" referring to line - PLA[ubound(PLA)]:= {PS_JOB.EFFDT};

whileprintingrecords;
datevar array PLA;
datevar array RFL;
If {PS_JOB.ACTION} = "PLA" then
(
redim PLA[ubound(PLA)+1];
PLA[ubound(PLA)]:= {PS_JOB.EFFDT};
);
If {PS_JOB.ACTION} = "RFL" then
(
redim RFL[ubound(RFL)+1];
RFL[ubound(RFL)]:= {PS_JOB.EFFDT};
);
"blah"

//Group Footer formula:
//error message says "A number, currency amt, boolean, date-time, string is expected here" referring to line - if ubound(pla) = ubound(rfl) then

whileprintingrecords;
datevar array PLA;
datevar array RFL;
numbervar counter;
for counter := 1 to ubound(PLA) do(
if ubound(pla) = ubound(rfl) then
Output:=Output+"Period " & totext(counter,0,"") & ": " & pla[counter] & " - " & rfl[counter] & ", for a total of " & totext((rfl[counter] - pla[counter]),0,"") & chr(13)
else
"Period " & totext(counter,0,"") & ": " & pla[counter] & " - not returned yet," & " so far for a total of " & totext((currentdate - pla[counter]),0,"") & chr(13)
);
left(Output,len(Output)-1)

 

[synapsevampire]

Change group header to:

//Group Header
//error message says "a variable name is expected here" at the redim lines

whileprintingrecords;
datevar array PLA;
datevar array RFL;
redim PLA;
redim RFL;
stringvar output:="";

As for the details error, your field isn't a date type, right click it and select browse data to learn it's type, then convert it to a date type. If you don't know how to do that, post an example of how it's stored. When you state that something is a date, I'll take it literally.

As for the group footer, I can't see where the problem is, try testing just using:

whileprintingrecords;
datevar array PLA;
datevar array RFL;
ubound(PLA) & ' - ' & ubound(RFL)

and see what the values are, perhaps there's a null or zero to allow for there.

-k

 

[Chinnymerlgrove]

Thanks for your diligence in helping me with this challenge.

The {PS_JOB.EFFDT} is a type: DateTime. Does it need to be converted to just Type: Date? If yes, how would I do that?

//Group Header
//new error message says "A [is expected here" at the redim lines
whileprintingrecords;
datevar array PLA;
datevar array RFL;
redim PLA;
redim RFL;
stringvar output:="";

This piece of the footer works. If Null or zero, let's return a message "Still on LOA"
whileprintingrecords;
datevar array PLA;
datevar array RFL;
ubound(PLA) & ' - ' & ubound(RFL)

Chinny_Merlgrove

 

[synapsevampire]

Guess the ole memory isn't so solid, better start testing...

Change the datevar to datetimevar.

Change the redims to:

redim PLA[0]

Group Footer:

whileprintingrecords;
datevar array PLA;
datevar array RFL;
numbervar counter;
for counter := 1 to ubound(PLA) do(
if ubound(pla) = ubound(rfl) then
Output:=Output+"Period " & totext(counter,0,"") & ": " & pla[counter] & " - " & rfl[counter] & ", for a total of " & totext((rfl[counter] - pla[counter]),0,"") & chr(13)
else
Output:=Output+"Period " & totext(counter,0,"") & ": " & pla[counter] & " - not returned yet," & " so far for a total of " & totext((currentdate - pla[counter]),0,"") & chr(13)
);
left(Output,len(Output)-1)

You need for the first two formulas to work before the group footer will.

-k

 

[Chinnymerlgrove]

Here are the new error messages. Whether I code the dateTimeVar value as [PLA] or PLA, I get the same result.

//Group Header
//error message says "a variable name is expected here" at the dateTimeVar array lines. I also tried dateTimeVar and I get the same result.
whileprintingrecords;
dateTimeVar array [PLA];
dateTimeVar array [RFL];
redim PLA[0];
redim RFL[0];
stringvar output:="";

//Detail records
//error message says "a variable name is expected here" at the dateTimeVar array lines.
whileprintingrecords;
dateTimeVar array [PLA];
dateTimeVar array [RFL];
If {PS_JOB.ACTION} = "PLA" then
(
redim PLA[ubound(PLA)+1];
PLA[ubound(PLA)]:= {PS_JOB.EFFDT};
);
If {PS_JOB.ACTION} = "RFL" then
(
redim RFL[ubound(RFL)+1];
RFL[ubound(RFL)]:= {PS_JOB.EFFDT};
);
"blah