help with report format

[SamLN]

Hi Tek Heads!
needing some advice on the simplest way to construct a report on Employee Service Milestones (Anniversary's).
Essentially the end user will use parameters to select a start date and end date, and a service milestone (5 yes, 10 yrs etc) and all staff who have a service anniversary for 5yrs, 10 yrs etc. during the date ranges selected will be displayed.

I have tried calculating each of the service anniversary dates out for each employee but feels cumbersome and long winded .....any suggestions??

 

[lbass]

Use a record selection formula like this:

dateadd("yyyy",{?milest},{table.hiredate}) in {?StartDate} to {?EndDate}

Then place the employee name in the detail section. If you want to show the anniversary date, add this formula to the detail section:

dateadd("yyyy",{?milest},{table.hiredate})

-LB

 

[SamLN]

Hi LB thanks for your suggestion.
I have tried this and I get the error "this array must be subscripted for example: Array"
This error seems to relate to the parameter for anniversary. This parameter is set up to be a number, with the values of 5,10,15,20 etc in the "VALUE" column and "5 Years" in the DESCRIPTION column. The other Parameter selections are;
Type: static
description only: true
custom values: false
Multiple values: true
discrete values: true
Range: false


dateadd("yyyy",{?Anniversary},{PERSON.START_DATE}) in {?Start Date} to {?End Date} and

Do you know what this error means and how to correct?

Sam

 

[SamLN]

Hi LB
I have googled and seems this is related to the multiple values which may be selected at the anniversary parameter (no doubt you knew this!)

I have tested report and works absolutely perfectly when there is only one service anniversary selected. The question is how to construct to allow the user to select any number of anniversaries (5 years, 10 years, 15 years, 20 years etc)in the parameter.

the full selection formula is:
dateadd("yyyy",{?Anniversary},{PERSON.START_DATE}) in ({?Start Date} to {?End Date} )and
isnull({PERSON.TERMINATION_DATE}) and
{PERSON.EMPLOYEE_NO} < 5000000.00

any suggestions much appreciated.

 

[lbass]

datevar array x;
numbervar i;
numbervar j := ubound({?milest});
for i := 1 to j do (
if not ({person.startdate} in x) and
dateadd("yyyy",{?milest},{person.startdate}) in {?StartDate} to {?EndDate} then (
redim preserve x[ubound(x)];
x := {person.startdate})
));
{person.startdate} in x and
isnull({PERSON.TERMINATION_DATE}) and
{PERSON.EMPLOYEE_NO} < 5000000.00

The above record selection will work if you have fewer than 1000 records that meet the criteria. Let me know if you have more.

-LB

 

[SamLN]

Thanks so much LB, I cant guarantee that there will be less than 1000 records so can you tell me what's required in that instance.
I really appreciate your help.

 

[SamLN]

Hi LB, thought i'd give your formula for less than 1000 records a try.
it errors initially with what I think is an additional bracket at ));
When I remove a bracket it then errors and says I need an array at the {milestone} in the line "numbervar j := ubound({?milest});"
Sam

 

[lbass]

datevar array x;
numbervar i;
numbervar j := ubound({?milest});
for i := 1 to j do (
if not ({person.startdate} in x) and
dateadd("yyyy",{?milest},{person.startdate}) in {?StartDate} to {?EndDate} then (
redim preserve x[ubound(x)];
x := {person.startdate}
));
{person.startdate} in x and
isnull({PERSON.TERMINATION_DATE}) and
{PERSON.EMPLOYEE_NO} < 5000000.00

This assumes you are using a multi-value parameter named {?milest}.

-LB

 

[SamLN]

Hi LB
thanks for this but I am still getting errors.
At this line x := {PERSON.START_DATE} it errors with "A date is required here". I attempted to amend so it became- x := date({PERSON.START_DATE}). which allowed the formula to save but then got an error saying "this array must be subscripted " and highlighting the {?Anniversary} in the formula that calculates the future anniversary milestone (ie: 5 yrs, 10 yrs etc) formula is: dateadd("yyyy",{?Anniversary},{PERSON.START_DATE})

sorry to be such a pain. you have been really helpful.

 

[SamLN]

Ok, have played with it some more.
Changed the future anniversary milestone formula to:

numbervar j := ubound({?Anniversary});

dateadd("yyyy",j,{PERSON.START_DATE})

which I have no idea if its right or not (just trying to make sense of your formula). Saves the formula without error, but when re-running it errors says "an arrays integer must be between 1 and 1000".

... exasperated ...

 

[lbass]

OK, you could just use:

redim preserve x[1000];

...as long as you be returning fewer than 1000 distinct dates--about 3 years worth.

-LB

 

[SamLN]

Hi LB, thanks for all your help. I still have questions, so I have reposted. I didn't want to keep bothering you, but have been very helpful thus far. (feel free to answer the new post if you want)

Sam

 

[lbass]

You should have stayed with this thread.

-LB

 

[lbass]

So what happened when you used my last suggestion? This formula will return records that can be greater than 1000 in number, but which would be limited to 1000 unique dates.

-LB