Formula Help

[tkschief]

I am stuck. I am using Crystal Reports XI and retrieving data from a SQL database. The goal of the report is to return cases that can be destroyed each calendar year. To determine when a case can be destroyed, the youngest child dob + 27 years on the case is compared to the latest order event + 6 years (of which there are 6 order types), and the file destruction date is whichever is later.

File Destroy Date = DOB of the youngest child + 27 years OR 6 years from the date of the last order, whichever date is later. Display for previous calendar year.

Report Information:
1. All of the children plus their dob and all of the events plus event date on the case are returned.
2. The following fields are returned: case number, childname, dob, event, eventdate, eventid.
3. Formula Fields:
a. DOBPlus27Yrs: adds 27 years to each child’s dob on the case.
DateAdd('yyyy',27,{Command.DOB})
b. EventPlus6Hrs: adds 6 years to the eventdate for each event on the case.
DateAdd('yyyy',6,{Command.EVENT_DATE_TIME})
c. EventIDReq: indicates if eventid is one of 6 ids.
IF {Command.EVENT_TYPE_ID} in [1000111, 1000188, 385, 397, 412, 775, 781] then 1 else 0;
d. DOB27 vs Event6:
IF {@DOBPlus27Yrs} >= {@EventPlus6Yrs} then 1
else 0;
4. The report is grouping on case number and a group selection formula is being used to only return the youngest child on each case.
({Command.DOB}=minimum({Command.DOB},{Command.CASE_NUM})

I am having trouble pulling all of this together. I added to the group selection formula AND
sum({@EventIDReq}, {Command.CASE_NUM})>1 but then the cases that did not have an eventid for the EventIDReq formula, did not display which is not what I wanted. If an order does not exist, then the dob+27 years for the youngest child on the case should be the file destruction date.

Example of data:

CaseNum ChildName DOB DOB+27 Event EventDate EventDate+6 EventID
CS-00001 Doe, Jane 08/13/1998 08/13/2025 Ref Rec 12/28/2010 12/28/2016 1* 505 0**
CS-00001 Doe, Jane 08/13/1998 08/13/2025 Order Rec 12/05/2011 12/05/2017 1 385 1

CS-00003 Doe, John 07/19/2005 07/19/2032 Hrg - Rev 01/23/2012 01/23/2018 1 123 0

EX-00001 Doe, Jane 08/13/1998 08/13/2025 Order Rec 12/05/2011 12/05/2017 1 385 1

*Formula D
**Formula C

Expected Results:

CaseNum Child DOB DOB+27 Event EventDate EventDate+6 EventID FDD
CS-00001 Doe, Jane 08/13/1998 08/13/2025 Order Rec 12/05/2011 12/05/2017 1 385 1 08/13/2025

CS-00003 Doe, John 07/19/2005 07/19/2032 1 123 0 07/19/2032

EX-00001 Doe, Jane 08/13/1998 08/13/2025 Order Rec 12/05/2011 12/05/2017 1 385 1 08/13/2025


FDD = File Destruction Date

Thanks for your expertise and help!

 

[lbass]

Try the following:

Change {@EventIDReq} to:

IF {Command.EVENT_TYPE_ID} in [1000111, 1000188, 385, 397, 412, 775, 781] then
DateAdd('yyyy',6,{Command.EVENT_DATE_TIME}) else
date(0,0,0)

Create this formula {@DestroyDate}:

maximum([{@EventIDReq},{@DOB+27Yrs}])

Then you can use a group selection formula like this:

{@DestroyDate} = maximum({@DestroyDate},{command.case_num})

You don't have to limit it to the youngest child, because that child will always have the maximum DOB_27yrs value.

-LB

 

[tkschief]

I tried what you suggested and it didn't group as expected. I put the DestroyDate formula in the report and a DestroyDate exists for each record because the formula is being calculated even when the EventIDReq is null. It is a bit complicated as I need the report to look at each group and if a certain event exists, then compare the dob + 27 yrs and certain event + 6 yrs; otherwise, if a certain event doesn't exist, I only want 1 record to return in that group and it will use the dob + 27 yrs calculation. Hopefully this makes sense. Do you think it would be easier to bring the required events in via a subreport? I wasn't sure if it was possible to do calculations between the main report and the subreport. Thanks for your help - Teresa

 

[lbass]

I think I see. Keep the {@EventIDReq} formula as is, and change the group selection to this:

(
(
maximum({@EventIDReq},{command.casenum})=date(0,0,0) and{@DOB+27Yrs}=maximum({@DOB+27Yrs},{command.casenum})
) or
{@EventIDReq}=maximum({@EventIDReq},{command.casenum})
)

-LB

 

[tkschief]

Thanks for your hard work. I tried more of your suggestions but just wasn't getting the results that I needed. So, I created another report that has a subreport bringing in the specific order events with the order event + 6 years. With what you showed me in this post I was able to get everything that I need using a shared datetimevar and calculating the File Destroy date on the shared datetimevar. Now I need to be able to run the report returning just the cases where the File Destroy Date falls within a specific date range which will probably be for a year's length of time. In my Group Selection Formula I tried the following but receive an error of 'This formula cannot be used because it must be evaluated later.' I am obviously doing something wrong.

{Command.DOB}=minimum({Command.DOB},{Command.CASE_NUM}) and {@DestroyDate}in DateTime (2011, 01, 01, 00, 00, 00) to DateTime (2011, 12, 31, 00, 00, 00)

Details of this new report:
Main report is grouped by case_num and displays youngest child on case along with dob + 27 years calculated date with group selection formula of
Command.DOB}=minimum({Command.DOB},{Command.CASE_NUM})

Also has a SharedVariable formula which has been placed into the details b section.Formula details:

WhilePrintingRecords;
Shared DateTimeVar mycalc;
mycalc

Then I created another formula (DestroyDate)which I placed into the Details a section:
maximum([{@DOBPlus27},{@SharedVariable}])

Subreport: specific order events along with calculated event date (event+6 yrs) by case_num. DateTimeVar formula of:
whileprintingrecords;
shared DateTimeVar mycalc:={@Add6yrs};

Subreport and main report are linked by case_num. Subreport was inserted into details a of main report.

Report is working great and has everything needed but now I need to be able to filter on the File Destroy Date which is what I am having problems with now. Your expertise would be most appreciated!

Thanks - Teresa

 

[lbass]

You can't use a shared variable in a selection formula, as it is processed in a later phase. Instead of group selection, you could use it for section suppression in the main report, but it would only work for report sections below the ones containing the subreport.

-LB

 

[tkschief]

Would it be possible to add a date range to the DestroyDate formula of

maximum([{@DOBPlus27},{@SharedVariable}])

Thanks

 

[lbass]

You should be able to use a section suppression formula like this:

{@DestroyDate} in {?Start} to {?End}

...as long as you are suppressing sections below the one containing the sub.

-LB