How to use Maximum in the Select Expert

[CJP12711]

I have a report that is pulling cancelled appointments. In the select expert, the formula is currently set to this:

(if dayofweek(today) = 2 then {apptcancel.canceled_datetime} in Today-2 to Today ELSE
{apptcancel.canceled_datetime} = TODAY)

This is working great with one exception. Each appointment can have multiple cancellation dates.

I have a data set that looks like this:

ApptID Canceled_DateTime
1 1/22/10
1 1/25/10
2 1/20/10
3 1/26/10

Using the formula above, it is not pulling anything. It's finding the first cancellation date for appt ID #1, which was 1/22, even though it was cancelled again on 1/25. How can I tweak this formula to find all cases cancelled today, accounting for the multiple cancellations?

Thanks for the help!
CJ

 

[Madawc]

Take a look at Report > Selection Formulas > Record, everything is much clearer there.

If you create a maximum for the field, then you'll be able to find it among your report fields and use it for record selection. Once you are using it there, you can remove it from the report.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[CJP12711]

Thanks Madawc. I'm not following you though. In the Selection Formula for Record, here is what I have:

{resunit.resunit_id} in [216, 215, 214, 213, 211, 210, 209, 172, 168, 173] and
not ({pattype.abbr} in ["CNL", "I/P", "PSP"]) and
not ({proname.name} startswith "#") and
(if dayofweek(today) = 2 then {apptcancel.canceled_datetime} in Today-2 to Today ELSE
{apptcancel.canceled_datetime} = TODAY)

If I try and put a 'maximum' around the canceled_datetime field, it gives me an error saying that it has to be evaluated later.

I tried creating a formula that simply is:
maximum({apptcancel.canceled_datetime})

How do I use this in the above selection formula, while still accounting for the Today-2 criteria?

Thanks!

 

[lbass]

In your example, it should pull only 1/25, since 1/22 is today - 3 (on 1/25 anyway). Since these are separate records, each would be evaluated independently by the selection formula. Something else must be going on--maybe the record doesn't meet your other criteria.

-LB

 

[CJP12711]

Thanks LBass. When I remove the criteria of

(if dayofweek(today) = 2 then {apptcancel.canceled_datetime} in Today-2 to Today ELSE
{apptcancel.canceled_datetime} = TODAY)


then all records show up correctly. When it's added in, it's looking at the 22nd, therefore that record won't get returned, even though there is another cancellation date on 1/25. I'm pretty stumped with this one!

 

[lbass]

I don't believe that is happening. Today-2 = 1/23 (on 1/25), so ONLY the 1/25 record should show up. Of course, you can't test this today. Today you should see just Record #3.

There is nothing in your selection criteria that would account for the results you are describing. CAn you please copy your SQL query (database->show SQL query) into the thread?

-LB

 

[CJP12711]

Sure - here you go:

 SELECT "pat"."name_display", "pattype"."abbr", "visit"."pat_acct_num", 
"adtsys"."hl7_adt_code", "resunit"."resunit_id", 
"apptcancel"."last_sched_datetime", "apptcancel"."canceled_datetime", 
"loc"."name", "proname"."name", "pat"."birthdate"

 FROM   (((((((((((("psprod"."dbo"."appt" "appt" INNER JOIN 
"psprod"."dbo"."patbooking" "patbooking" ON 
"appt"."appt_id"="patbooking"."appt_id") INNER JOIN 
"psprod"."dbo"."resunit" "resunit" ON 
"appt"."resunit_id"="resunit"."resunit_id") INNER JOIN 
"psprod"."dbo"."visitapptlist" "visitapptlist" ON 
"appt"."appt_id"="visitapptlist"."appt_id") INNER JOIN 
"psprod"."dbo"."probooking" "probooking" ON 
"appt"."appt_id"="probooking"."appt_id") INNER JOIN 
"psprod"."dbo"."apptcancel" "apptcancel" ON 
"appt"."appt_id"="apptcancel"."appt_id") INNER JOIN "psprod"."dbo"."pro" 
"pro" ON "probooking"."pro_id"="pro"."pro_id") INNER JOIN 
"psprod"."dbo"."proname" "proname" ON 
"pro"."proname_id"="proname"."proname_id") INNER JOIN 
"psprod"."dbo"."visit" "visit" ON 
"visitapptlist"."visit_id"="visit"."visit_id") INNER JOIN 
"psprod"."dbo"."adtsys" "adtsys" ON 
"visit"."adtsys_id"="adtsys"."adtsys_id") LEFT OUTER JOIN 
"psprod"."dbo"."pattype" "pattype" ON 
"visit"."pattype_id"="pattype"."pattype_id") INNER JOIN 
"psprod"."dbo"."pat" "pat" ON "patbooking"."pat_id"="pat"."pat_id") INNER 
JOIN "psprod"."dbo"."facility" "facility" ON 
"resunit"."facility_id"="facility"."facility_id") INNER JOIN 
"psprod"."dbo"."loc" "loc" ON "facility"."facility_id"="loc"."loc_id"

 WHERE  ("resunit"."resunit_id"=168 OR "resunit"."resunit_id"=172 OR 
"resunit"."resunit_id"=173 OR "resunit"."resunit_id"=209 OR 
"resunit"."resunit_id"=210 OR "resunit"."resunit_id"=211 OR 
"resunit"."resunit_id"=213 OR "resunit"."resunit_id"=214 OR 
"resunit"."resunit_id"=215 OR "resunit"."resunit_id"=216) AND  NOT 
("pattype"."abbr"='CNL' OR "pattype"."abbr"='I/P' OR 
"pattype"."abbr"='PSP') AND "proname"."name" NOT  LIKE '#%' AND 
("apptcancel"."canceled_datetime">={ts '2010-01-26 00:00:00'} AND 
"apptcancel"."canceled_datetime"<{ts '2010-01-27 00:00:00'})

 ORDER BY "loc"."name"

 

[lbass]

I see nothing here that would cause this.

Please clarify. You are saying that on 1/25 when you ran this, not ANY of the four records showed up--not even the 1/25 record (the only one that SHOULD have shown up).

You can test this again by changing the formula to:

(if dayofweek(today) = 3 then {apptcancel.canceled_datetime} in Today-3 to Today-1 ELSE{apptcancel.canceled_datetime} = TODAY-1)

-LB

 

[CJP12711]

That is correct. In my example above, the case with the appointment ID of 1 was excluded from the report. It did not show up until I removed the date formula above. When I added the cancel_Datetime to the report, it reflected the 1/22 entry - not the 1/25 one (in the details section).

I tried again while modifying the formula you had, and that record was still not included.

I'm glad to see that you are stumped as well, and can't see a reason for it. It seems to only be an issue for cases with multiple cancellation_datetimes. If the case has a single one, there is no issue at all.

 

[lbass]

No, if you are only saying that record #1
does not appear, but record #3 does, then
the formula is working correctly. You are
not recognizing that 1/22 is three days
before 1/25, and therefore does not meet
your criteria.

-LB

 

[CJP12711]

I will refer you back to this data set:

1 1/22/10
1 1/25/10
2 1/20/10
3 1/25/10


I am only seeing appt ID 3. I expect to see appt ID 3 and appt ID 1. #1 is still getting excluded even though it does have a record with a cancel date of 1/25. It seems to only look at the first cancel date of 1/22, and doesn't go any further to look for the entry with 1/25 for that same appointment ID.

 

[Madawc]

To get back to Maximum, you can create it automatically by left-clicking on a field. Once it exists, use it in record selection.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[CJP12711]

It's not working. It's still not picking up appt ID 1 that has the 2 different cancellation dates... I'm not sure what I'm doing wrong here...

 

[lbass]

Since today is 1/27, are you seeing the same issue with data with two cancellation dates, only one of which is 1/27?

There is something we are missing here. A record selection formula will not remove more than one record at a time. Do you have a suppression formula for the detail section in the section expert? Do you have a group selection formula?

-LB

 

[CJP12711]

It is not having the same issue for today, 1/27. But, the issue does exist for 1/25. Perhaps there is a corrupt record? I do have a suppression formula, but it does not affect the results when I remove it.

I will continue testing the rest of the week and see if the issue comes up again on Monday. I appreciate your help.

 

[lbass]

What is your suppression formula? Your post
is too wide for me to see the end of your
post. Is it section suppression? Did you
check the box before entering the conditional
formula?

How are you verifying that it's not
working for 1/25?

-LB