[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-10-07
8
Medium Priority
?
296 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
[X]
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
  • 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 2000 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
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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