Avatar of Member_2_6430006
Member_2_6430006
 asked on

How can I make a date appear on the group line?

Attached is a report where I am trying to capture all employees active during a date range, plus those employees who had or not a medical benefit during a date range.  The active employees date range is 1/1/2015 to 12/31/2015.  The benefit date range is 4/1/2014 to 12/31/2015 or active date = 12/31/2049.  The unfortunate part is I am trying to capture active benefits during a date range of 1/1/2015 to 12/31/2015, but the employee could have an active benefit from 4/1/2014 to 3/31/2015, and the report wants to see their start date as 1/1/2015 (Start Date 2). With a Calc,Display and Reset on the employee# grouping and a suppression on the Detail line I have captured most of what I need.  But the Start Date 2 formula field I cannot seem to get it to appear on the group footer line as 1/1/2015 or the actual start date of an employee who started during the current year.  I hope I have explained this clearly.  Please advise.
WORXTIME_WRPI_Coverage-Upload_Sample.rpt
Crystal Reports

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon
Mike McCracken

I looked at the report and am not sure what you think the error is.

Which dates are wrong?

If there is a date in the start date 2 column, is that what the value should be?

mlmcc
Member_2_6430006

ASKER
Hello mlmcc, I would like to see Start Date 2 output to display on the Group footer.  If I do a copy of the field and paste it into the group footer nothing appears nor do other start dates appear.  It seems I cannot summarize on the formula field.  I would like the 01/01/2015 to appear on the group footer line.
ASKER CERTIFIED SOLUTION
James0628

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Member_2_6430006

ASKER
Hello James, See attached sample report.  I have used your suggested formula (the last column of the Sample report) and it is working for my first concern, for anyone who had a benefit prior to 03/01/2015 then display 01/01/2015 but if an employee did not have a benefit before 03//31/2015 and their current benefit start date is >= 04/01/2015, I need to see the start date of their current benefit.  Please advise.
WORXTIME_WRPI_Coverage-Upload_Sample.rpt
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
James0628

Honestly, I'm still not really sure about the "rules" for the date that you're looking for, but maybe something like the following will work.  If the StartDate variable is empty, it displays the maximum BENEFIT_START_DATE for the group instead.  Create a new formula (call it whatever you like) and put it in the group footer.

WhilePrintingRecords;
Global Stringvar StartDate;

if StartDate <> "" then
  StartDate
else
  CStr (Maximum ({EMEB_EMP_BENEFITS.BENEFIT_START_DATE}, {@Group: LF#}), "MM/dd/yyyy")

Open in new window


 If you don't want to show the maximum date if it's before 04/01/2015, you could add a test for that.

WhilePrintingRecords;
Global Stringvar StartDate;

if StartDate <> "" then
  StartDate
else
  if Maximum ({EMEB_EMP_BENEFITS.BENEFIT_START_DATE}, {@Group: LF#}) >= Date (2015, 04, 01) then
    CStr (Maximum ({EMEB_EMP_BENEFITS.BENEFIT_START_DATE}, {@Group: LF#}), "MM/dd/yyyy")
  else
    ""

Open in new window


 FWIW, a side issue with these formulas is checking for specific dates, like 01/31/15 and 04/01/15.  If you're going to use this report again, what happens when you run it next month, or next year?  Do those dates need to change?

 James
Member_2_6430006

ASKER
Hello James, Sorry to being tardy on this question, your solution did work for the most part, there were a few bumps but worked through them.  Thank you for your knowledge.
James0628

You're welcome.  Glad I could help.

 James
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.