Evaluate Multiple Records 1

[govmule]

Hello All,

I'm using Crystal Reports 8.5/Crystal Enterprise 8.0 connected to an Informix Dynamic Server 7.3 via Native drivers.

The report is grouped by client.client_id. The details for this report include activity records so there are multiple detail records for each client. Each activity record has a start_date and an end_date. The end_date is null if the activity has not been completed.

I would like to evaluate all activity records to identify those clients that have no null end_date (all activities completed) and 90 days has past since the latest end_date.

My goal is to highlight the record(s) if it(they) meet(s) this criteria.

I hope this makes since. If more information is needed or a better explanation I apologize and will be happy to provide it.

As always, thanks in advance.

See ya,

Jack

 

[synapsevampire]

Straightforward enough I think.

You can limit the whole report to just those rows by selecting Report->Edit Selection Formula->Record and placing something like:

not(isnull({table.enddate}))
and
curreentdate-{table.enddate} >= 90

I assumed that when you said 90 days had passed, you meant 90 or more.

Or if you intend to display all rows and highlight those that meet this criteria, just right click the details section->format section->Color tab (far right top)->X 2 next to Background color and place something like:

if not(isnull({table.enddate}))
and
curreentdate-{table.enddate} >= 90 then
cryellow
else
crwhite

-k

 

[lbass]

To determine whether someone has ANY null enddates, you cannot limit the records to those that are not null in the record selection formula. I think you should group on the client ID and then create a formula {@null}:

if isnull({table.enddate}) then 1 else 0

Then go to edit selection formula->GROUP and enter:

sum({@null},{table.clientID}) = 0 and
currentdate - maximum({table.enddate},{table.clientID})> 90

This should return only those clients for whom all activities are completed and with a completion date greater than 90 days.

-LB

 

[synapsevampire]

LB: Did I misunderstand this "I would like to evaluate all activity records to identify those clients that have no null end_date (all activities completed) and 90 days has past since the latest end_date."

It says no null end dates, not that they are null.

Sample data and expected output would have helped to clarify.

-k

 

[govmule]

Hello Vampire and LBass

My boss has reprioritized my life at the moment. I'll post a sample in a couple of hours. I'm sorry for this. I usually try not to post unless I have the whole day to devote to it (or at least a couple of hours)

Thanks to you both

 

[lbass]

SV--I think he meant he wanted CLIENTS who have no null enddates in any records, not just records with no null enddates. While I might be wrong, I believe I have a slight advantage here, since I think basshead and I work in the same field. But, I just noticed that he wanted to highlight these people, not group select them, so...

basshead - Instead of using the group select formula, go to format section->group header (and details/group footer, if you wish)->color tab->background->x+2 and enter:

if sum({@null},{table.clientID}) = 0 and
currentdate - maximum({table.enddate},{table.clientID})> 90 then crYellow else crNoColor

-LB

 

[synapsevampire]

Ahhh, I see, those that have no null enddates, got it, your formula looks right.

-k