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...
Becky EdwardsEpic Clarity DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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))

mlmcc
0
James0628Commented:
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.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
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.

 SELECT "DOCUMENT"."CLINICALDATE", "PERSON"."SEARCHNAME", "PERSON"."MEDRECNO", "DOCUMENT"."DOCTYPE", "RPTAPPT"."APPTSTATUS", "USRINFO_Provider"."SEARCHNAME", "PERSON"."ISPATIENT", "LOCREG"."NAME", "PERSON"."PSTATUS"
 FROM   ((("ML"."PERSON" "PERSON" LEFT OUTER JOIN "ML"."DOCUMENT" "DOCUMENT" ON "PERSON"."PID"="DOCUMENT"."PID") LEFT OUTER JOIN "ML"."RPTAPPT" "RPTAPPT" ON "PERSON"."PID"="RPTAPPT"."PID") LEFT OUTER JOIN "ML"."USRINFO" "USRINFO_Provider" ON "DOCUMENT"."USRID"="USRINFO_Provider"."PVID") LEFT OUTER JOIN "ML"."LOCREG" "LOCREG" ON "DOCUMENT"."LOCOFCARE"="LOCREG"."LOCID"
 WHERE  "PERSON"."ISPATIENT"='Y' AND  NOT ("RPTAPPT"."APPTSTATUS"=1 OR "RPTAPPT"."APPTSTATUS"=2) AND ("DOCUMENT"."DOCTYPE"=1 OR "DOCUMENT"."DOCTYPE"=1.66560659900612e+015 OR "DOCUMENT"."DOCTYPE"=1.66841937500622e+015 OR "DOCUMENT"."DOCTYPE"=1.67215399400638e+015) AND "PERSON"."PSTATUS"='A'
 ORDER BY "LOCREG"."NAME", "PERSON"."SEARCHNAME"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Becky EdwardsEpic Clarity DeveloperAuthor Commented:
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?
0
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
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.

Becky
0
James0628Commented:
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).

 James
0
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
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.

Thanks.
0
James0628Commented:
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:

WhilePrintingRecords;
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):

WhilePrintingRecords;
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:

WhilePrintingRecords;
Global NumberVar patientcount


 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.