Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Which dates are wrong?
If there is a date in the start date 2 column, is that what the value should be?
mlmcc
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.
{@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.
James
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
James
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial