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
WORXTIME_WRPI_Coverage-Upload_Sample.rpt
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
WORXTIME_WRPI_Coverage-Upload_Sample.rpt
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.
If you don't want to show the maximum date if it's before 04/01/2015, you could add a test for that.
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
WhilePrintingRecords;
Global Stringvar StartDate;
if StartDate <> "" then
StartDate
else
CStr (Maximum ({EMEB_EMP_BENEFITS.BENEFIT_START_DATE}, {@Group: LF#}), "MM/dd/yyyy")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
""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
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.
You're welcome. Glad I could help.
James
James
Which dates are wrong?
If there is a date in the start date 2 column, is that what the value should be?
mlmcc