Solved

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

Posted on 2004-10-07
8
273 Views
Last Modified: 2012-08-13
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...
0
Comment
Question by:wcotis60
  • 4
  • 4
8 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 12253926
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?

frodoman
0
 

Author Comment

by:wcotis60
ID: 12258495
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.
0
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 12258725
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.


HTH

frodoman
0
 

Author Comment

by:wcotis60
ID: 12259500
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

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 42

Expert Comment

by:frodoman
ID: 12259731
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"
    COLLECTION  
 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"
D   COLLECTION  
GF Collection
GH Pt 303
D     no alerts
GF
GH Pt 404
D     "Print Film"
D     "Call Report
GF
GH Pt 733
D      no alerts
GF

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.

frodoman
0
 

Author Comment

by:wcotis60
ID: 12260031
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:

   <blank>
   <blank>
   Collection

How can I suppress the blank lines within the subreport?
0
 

Author Comment

by:wcotis60
ID: 12260047
Forget the previous question - I got it to work.

Suppress blank section within the subreport!
Thanks!

points on the way
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12260711
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!

Cheers,

frodoman
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now