CR9/SQL - Create Section only if 'field' contains "WORD"

Using CR 9 with a MS SQL db

Patient database, tables: tbPatients (name, ID, AlertID), tbPatientAlerts(alertID, alertDesc)

Alerts contains "Collection", "Call Dr. Office", "Print Film", etc.

Need to show Section with "Patient in Collection" if "Collection" is selected in table tbPatientAlerts, and not show if any other alerts or no alerts are selected.

I can do basic stuff but...
Who is Participating?
frodomanConnect With a Mentor Commented:
I'm assuming that each patient will only be in the table once.

Create your report using both tables.  Link them by AlertID.  Set your selection criteria to {tbEncounters.ApptDate} = "10/8/04".

Add your fields to the report, including the alert description.  If I understand correctly, you should not be viewing almost what you need - the difference being that all alert descriptions are shown, not just the Collection ones.

To fix this, you want to suppress the one's that aren't needed.  Right-click on the {tbPatientAlerts.alertDesc} field and select "Format Field".  On the 'Common' tab you want to click the formula button (X+2) that is to the right of the word "suppress".  Enter a formula that results in "True" when you want the field hidden and "False" when you want it visible.  In this case a good formula would be:

{tbPatientAlerts.alertDesc} <> 'Collection'

This will suppress this field whenever it's not equal to the word Collection.

A similar alternative would be to leave all of the alerts displayed, but change the color of the Collection alerts.  On the 'fonts' tab for this field enter a formula like this to the right of the 'color' dropdown:

If CurrentFieldValue = 'Collection' then crRed Else crBlack

This will color the word Collection red and leave all other text black.


So you want to show all patients that have an AlertID equal to whatever ID is described as "Collection" - is that correct?

If so, Create a report and put both tables on the report.  Link them by AlertID.  Set your selection criteria to {tbPatientAlerts.alertDesc} = 'Collection'

That's all there is to it, so I'm guessing maybe I didn't understand what you want?  If I'm off target can you explain a little more what you're trying to do?

wcotis60Author Commented:
Not exactly, I need to show all patients for {tbEncounters.ApptDate} = "10/8/04" and then IF they have a "Collection" alert show that alert in the report.

Sorry for the confusion.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

wcotis60Author Commented:
When I do the first suggested formula, I am getting a patient line for each alert selected for that patient (or if no alerts), but only the "Collection" is printing.

  Pt 101    (empty alert)--> alert "Metal in Body"

  Pt 101    COLLECTION  --> alert"Collection"

 Pt 303     (empty)  --> no alerts

 Pt 404     (empty) --> alert "Print Film"

 Pt 404     (empty) ---> alert "Call Report

 Pt 733     (empty) --> no alerts

So obviously each patient *is* in the table more than once then.  What I would do now is this:

Erase the conditional suppression formula - it isn't going to help you.

Report -> Group Expert:  Add a group based on Patient so your report (given example above) will look like this:

 Pt 101    
    "Metal in Body"
 Pt 303
     no alerts
 Pt 404
     "Print Film"
     "Call Report
 Pt 733
      no alerts

Create a new formula and place it in the group header:
// @ResetGroup
shared numberVar IsCollection := 0;

Create a new formula and place it in the details section:
// @EvalGroup
if {tbPatientAlerts.alertDesc} = 'Collection' then shared numberVar IsCollection := 1;

Create a new formula and place it in the group footer:
// @ShowAlert
if shared numberVar IsCollection = 1 then 'Collection';

This should make your report look like this now:

GH Pt 101    
D    "Metal in Body"
GF Collection
GH Pt 303
D     no alerts
GH Pt 404
D     "Print Film"
D     "Call Report
GH Pt 733
D      no alerts

Now drag the patient id / name down to the group footer.  Suppress the details section and the group header section and you'll be left with this:

GF PT101 Collection
GF PT303
GF PT404
GF Pt 733

I believe that's what you're trying to get to - let me know if we still aren't there.

wcotis60Author Commented:
I will look at the above, but in the mean time I have created a subreport of the {alerts.desc} linked to main by PatientID.

The earlier formula does suppress the alerts that are not "Collection", but leaves a blank line in the subreport, so if the patient has 3 alerts and one is "Collection" the subreport looks like this:


How can I suppress the blank lines within the subreport?
wcotis60Author Commented:
Forget the previous question - I got it to work.

Suppress blank section within the subreport!

points on the way
Glad you got it working.  

If performance is a concern you may want to consider my last posting as an option.  Of course the subreport method works fine but it also requires Crystal to hit the db once for every patient instead of just one time in total.  If you're happy w/ current performance then no reason to change of course!


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.

All Courses

From novice to tech pro — start learning today.