Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2004-10-07
8
280 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 

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
 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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