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
GrapeladyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
GrapeladyAuthor Commented:
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.
James0628Commented:
{@StartDate 2} doesn't show anything in the group footer, in most cases, because it's just looking at the previous record.  In the group footer, that's the next to last record in the group, and that record doesn't usually meet your conditions (eg. the start and end dates are 04/01 to 06/30).  If the dates don't meet your conditions, the formula just produces "".

 You can't use a CR summary on that formula because it uses Previous.

 When using Previous, I would check OnFirstRecord.  There is no previous record when you're on the first record, and I'm not sure what Previous gives you in that case.  Rather than rely on some default behavior, I'd rather use OnFirstRecord to explicitly handle that situation.

 Why are you only checking the previous record?  That means that you aren't considering the last record for each emp.  Is that really what you want?  That formula will only see the dates in the last record for one emp when it's on the first record for the next emp, and any dates from the previous emp are presumably irrelevant.  Which brings me to my next point ...

 The formula should probably also check EMP_NUMBER.  You're checking the date fields in the previous record, but on the first record for an emp, the previous record is actually for a different emp.  It may not be absolutely necessary (depending on your data), but it would be a good idea to check to see if the previous record was for the same emp.

 Having said all of that ...

 I'm not sure exactly what you're trying to do in {@StartDate 2}, but you could try the following.  Basically, it sets a variable when the dates meet your conditions, and leaves the variable unchanged when they don't, so at the end of the group, the variable has the value from the last record in the group that met your conditions.  I also added a check on OnFirstRecord and EMP_NUMBER, partly for the reasons mentioned above, but also to reset the variable for each new emp.  You could create a new formula to display the variable in the group footer, or just use this formula.

WhilePrintingRecords;
Global StringVar StartDate;

If OnFirstRecord or
 {EMEB_EMP_BENEFITS.EMP_NUMBER} <> Previous({EMEB_EMP_BENEFITS.EMP_NUMBER}) then
  StartDate := ""
else
If Previous({EMEB_EMP_BENEFITS.BENEFIT_START_DATE}) in Date(2014,04,01) to Date(2015,01,31) and
   Previous({EMEB_EMP_BENEFITS.BENEFIT_END_DATE}) in Date(2015,01,31) to Date(2015,03,31)
then
  StartDate := ToText(Date(2015,01,01), "MM/dd/yyyy")
else
If Previous({EMEB_EMP_BENEFITS.BENEFIT_START_DATE}) = Date(2015,02,01) to Date(2015,02,28) and
   Previous({EMEB_EMP_BENEFITS.BENEFIT_END_DATE}) = Date(2015,02,28) to Date(2015,03,31)
then
  StartDate := ToText(Date(2015,02,01), "MM/dd/yyyy")
else
If Previous({EMEB_EMP_BENEFITS.BENEFIT_START_DATE}) = Date(2015,03,01) to Date(2015,03,31) and
   Previous({EMEB_EMP_BENEFITS.BENEFIT_END_DATE}) = Date(2015,03,01) to Date(2015,03,31)
then
  StartDate := ToText(Date(2015,03,01), "MM/dd/yyyy");

StartDate

Open in new window


 James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Learn More!
GrapeladyAuthor Commented:
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
James0628Commented:
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
GrapeladyAuthor Commented:
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.
James0628Commented:
You're welcome.  Glad I could help.

 James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.