List employees and spouses in age groups based on a whole month. 1

[Evil8]

I have a report with employee Names, Dates of Birth, Ages and Spouse Dates of Birth and Ages. I have these set up to Group employee ages by Specified Order into age groups such as <25, 25-29, 30-35 ... 65-69, 70+.

I have to change this report. The staff wants a list of all employees into the highest age group for that month. Example - I run the report on 9/1 and John Smith turns 50 on 9/15 - he would be listed in the 50-55 group along with Mary Johnson whose spouse turns 50 on 9/22.

I made all these reports a few years ago and I've not had to do much modifications and lack of sleep has me behind on this one. If I can get any help or direction it would be really appreciated. Thanks!

 

[hilfy]

Try doing your age calculation based on the end date of the month rather than on the date you're running the report.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Evil8]

That's a good idea and I was about to start on that, when I got side tracked on another issue. I also had to go back to the user and explain she's going to get two reports - one that groups on employee ages and one that groups on spouse ages.

Thanks for your help.

 

[Evil8]

Back again. I think I've figured it out if anyone needs the info - I'm still testing though. Basically I've figured it out that I only need to list people that turn ages 25, 30,35, 40, 45, 50, 55, 60, 65 and 70 within the month picked.

I have one parameter field -
@BDate the use will pick this date.

I have three calculated fields -
iAge:
if Month ({tbl_ClInsured.BirthDate}) < Month (CurrentDate) or
(Month ({tbl_ClInsured.BirthDate}) = Month (CurrentDate) and
Day ({tbl_ClInsured.BirthDate}) <= Day (CurrentDate))
then Year (CurrentDate) - Year ({tbl_ClInsured.BirthDate})
else (Year(CurrentDate) - Year({tbl_ClInsured.BirthDate}))-1

spousedate: used to include nulls otherwise I only get employees that are married on the report instead of all employees
if IsNull({qry_Insured_Spouse.BirhtDate})then
date(0,0,0)else
{qry_Insured_Spouse.BirhtDate}

inMonth:
if (
month({@spousebdate}) = month({?BDate}) and
year({@spousebdate}) = year({?BDate})-26) or
(month({tbl_ClInsured.BirthDate}) = month({?BDate}) and
year({tbl_ClInsured.BirthDate}) = year({?BDate})-26) then 1

else

I do else statements in here to match age group changes ...-26) then 1 else -31) then 2 on up to -71) then 10

It seems to have worked.