We help IT Professionals succeed at work.

Error message in Crystal when trying to filter out certain people

Trying to get just patients who have not been seen in the last 180 days.
Grouped by location and then patient.

Tried putting this in my record select
Maximum ({@Clinical Date formatted}, {PERSON.SEARCHNAME}) > {@180daysago}
and got this error message:  This function cannot be used because it must be evaluated later.

This will work by putting it in the section expert for the group, but then it is just hiding those people, not eliminating them from the report.  

And if I do that, it causes another problem-- cannot get an accurate count of the people on the list, the count always includes the hidden ones!

Please help...
Watch Question

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
YOu will need to do this either n the database or use a Crystal command

The SQL is something like

SELECT "your list of fields"
FROM PatientTable
Where NOT (PatientID in (SELECT PatientID From PatientTable where VisitDate >= CurrentDate - 180))

Handling it in the db, as in mlmcc's suggestion, would be the most efficient option.

 FWIW, group selection might work.  For any summaries, like a count, you'd probably have to use formulas and variables.  The formulas would test the same Maximum, to decide which records to include in a summary (ie. which records to add to a variable).

 But, as you know, the report will still be reading the records for all of the people in the hidden groups, so if you have a lot of data, that could take a lot longer than using a query to select the correct people in the db.  Also, if the group tree is visible, it will still show the hidden groups (people).  They won't be visible on the report, but (at least in my experience) they will still show in the group tree.

Becky EdwardsEpic Clarity Developer


Yes, I would really like to do the selection within the database if possible. But the syntax mlmcc suggests is not in crystal format, and it needs to be.  Crystal does not recognize a where statement.

Can you figure out another way to say this without the where clause?

Here is my current crystal sequel statement.

Becky EdwardsEpic Clarity Developer


OK, now I know a little more after doing more research.  Perhaps I can create a sequel expression and use that in the Crystal Report?

If so, I would really appreciate some direction in how to do that.  And once I get the sequel expression created, how is it activated in the report?  Do I add it to the report selection?
Becky EdwardsEpic Clarity Developer


Here is the expression that works in the suppression section of the group footer.  

Maximum ({@Clinical Date formatted}, {PERSON.NAME}) > {@180daysago}

I would really like to create a sequel expression that did this instead, so it could be evaluated on the server and not inside the report.

What mlmcc was suggesting was that you create a query and use that as the datasource for the report, instead of selecting and linking the tables in CR.  You can use much more sophisticated queries that way.

 He mentioned a "Crystal command".  When you create a connection to the db in a report, there will probably be a Command option (it may depend on your db).  You can use that to create the query that CR will use for the report.  That would replace the tables that you're presumably using now.  You could start by copying the SQL that you posted, and paste that into the Command.  Then you could add a test similar to the one that he posted.

 If you have any parameters that are used to select the data, you'll need to (re)create those parameters in the Command editor window, and then insert them in the appropriate places in the Command.

 Another option would be to create something like a view or stored procedure in the db, put your query in that (including the test that mlmcc posted), and use that as the datasource for the report.

 Or, you may be able to continue using the tables as you are now, and add a SQL Expression, as you suggested.  I really don't know if that will work.  I never used SQL Expressions in my reports, because almost all of my reports used stored procedures, and CR doesn't allow SQL Expressions when using a stored procedure as the datasource.

 Basically, a SQL Expression has to return a single value.  In your case, it would probably be the maximum date for a person.  In general terms, something like:

(select Maximum ("date column") from "your table"
where "person ID field" = "person ID field from report")

 For "person ID field from report", you would select the appropriate field from the Field Explorer in CR and double-click, to insert it into the SQL Expression.

 You should be able to use something like that to get the most recent date for each person.

 What I really don't know, is whether or not you can use that (or any SQL Expression) in the record selection formula.  In theory, you would go into the record selection formula and add something like:

{%your SQL expression} < {@180daysago}

 I have no idea if CR will allow that, but if it does, that may give you what you want.

 Note that I changed > to <.  If you want people that have not been seen in 180 days, you want the people whose most recent dates are before 180 days ago (ie. the last date is < the date 180 days ago).

Becky EdwardsEpic Clarity Developer


All of this sounds a bit over my head.
I tried building a sequel expression like you suggested but wasn't able to get that to work.

I am able to get the correct set of data to export to EXCEL, for now.  I created a group for each person, put in the maximum(encounterdate) as a field to show for the person, then set a suppression formula on the group so that it displays just the persons whose most recent visit (maximum encounter date) is greater than 180 days ago.  

Ended up using a version of James first solution so gave him most of the points.  Still not able to summarize and total the number of people so this report could definitely use more work if I had the time.

Yeah, if you haven't written queries before, it could be difficult for you.

 FWIW, if you're suppressing sections, another option would be group selection.  It's really suppression, but handled in a different way.  You'd go to Report > Selection Formulas > Group and enter a formula to select the groups that you want to see (instead of suppressing the sections that you don't want to see).  Based on the formula that you posted earlier, it would be:

Maximum ({@Clinical Date formatted}, {PERSON.NAME}) <= {@180daysago}

 That would include any groups (people) where the most recent date was on or before {@180daysago}.  The other groups would be suppressed (so you wouldn't need formulas to suppress the individual sections in the group).

 I guess the end result is basically the same as using a formula to suppress the individual sections.  Group selection just allows you to suppress the whole group at once, instead of each section.

 As for getting a count, try this:

 Create a formula like the following in the Field Explorer (give the formula whatever name you like) and put it in the report header:

Global NumberVar patientcount;

 That just declares/initializes the patientcount variable.  The "" at the end is just so the formula doesn't produce any visible output on the report.  You could also suppress the field, or the section that the formula is in (the formula should still be evaluated).

 Create a formula like the following (call it whatever you like) and put it in the PERSON.NAME group header (or the group footer, but not both):

Global NumberVar patientcount;

if Maximum ({@Clinical Date formatted}, {PERSON.NAME}) <= {@180daysago} then
  patientcount := patientcount + 1;


 That increments the variable once for each group that fits your condition.  Again, the "" is just so the formula doesn't produce any visible output.  The field could also be suppressed.

 Then create a formula like the following and put it in the report footer to display the count:

Global NumberVar patientcount