Need help getting oldest anniversary date to show

[chish_nc]

I’m working on a report using CR 2016. I'm having an issue getting the original anniversary date for an agent. An agent can have multiple agent numbers but they only want to see the oldest anniversary date. So, you might have this scenario:

So, they would want to see the anniversary date of 12/27/90 in 2 more years because the report only shows agents in milestones of 5-year increments. It’s showing the other 2 milestone years but it’s not his original ann date and they don’t want to see him for 2 more years, so he shouldn’t be on the report at all until he reaches his 35 year with the oldest date. The report is working fine getting anniversary dates for each quarter but just having a problem getting the oldest anniversary date to show. The report runs quarterly with all agents that reach a milestone year in the next quarter. I hope this makes sense and any help is appreciated.

Here are the formulas:

@Age_Next_Quarter
Truncate(DateDiff('d', {AGMRV_HIER.CONTRACT_DTE}, {@Next_Quarter_End_Date})/365.25, 0)

@Next_Quarter_End_Date:
If Month(Today) in [1 to 3]
Then Date(Year(Today), 6, 30)
Else
If Month(Today) in [4 to 6]
Then Date(Year(Today), 9, 30)
Else
If Month(Today) in [7 to 9]
Then Date(Year(Today), 12, 31)
Else
If Month(Today) in [10 to 12]
Then Date(Year(Today) + 1, 3, 31)

@Notable _Anniversary

If Remainder({@Age_Next_Quarter},5) = 0
Then 'Y'
Else 'N'

I have @Notetable_Anniversary suppressed in the group footer. The report is grouped by agent number.

 

[dgillz]

Why isn't it grouped by Agent Name?

If it was you could create a group selection formula:

{ann date}=minimum({ann date},{agt name})

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[chish_nc]

It needs to be grouped by agent number or tax id number. I have tried the option you suggested with tax id number but it didn't work.

 

[pmax9999]

I would retain the Agent Number grouping and add an Agent Name grouping one level higher (so if Agent Number is Grp1, add a new Group for Agent Name and drag it up so it becomes Grp 1 and the Agent Number group is 'demoted' to Grp2), suppressing the new Grp1 Header & Footer. That way you could use Dgillzs Group Selection Formula.

The only downside is that it will change the sort order to Agent Name rather than Agent Number.

Without understanding the report structure (ie, the groups that exist and where the various fields and formulas are placed within those groups), it is impossible to advise on other changes that may be required to formulas etc, but I am confident it is workable.

In need, if you are prepared to post the actual report file with saved data, I'd be happy to take a look if you need further guidance.

Cheers
Pete.