Hiding sections if criteria doesn't match

[gk17]

I'm working on a report that will do the following:

- display a specific exam code for a date range that the user will specify in the report parameter
- take the resulting scheduled date and do another search for xx amount of days before and after that scheduled date
- return ALL exams for that second search

For example, if the user only searches for Port Knee X-Rays for 1/9/2014 and specifies 5 days for the date range, it should only return patients who have Knee X-Rays and any other eams from 1/4 (5 days prior) and 1/14 (5 days after).

I think I'm close to finishing this but ran into problems. I'm getting the "ALL other exams" (second search) results returned even when the patient has no Knee X-Ray. What can I do to have it hide GH1, Da & Db sections if a patient does not have the exam that the user specified in the report parameter?

One example is for test patient "PFSREG, GERONIMO I". This patient has no Port Knee exams and I don't want anything to show up for this patient. All sections should be hidden. For "Test, Kevin" on the other hand, there are two Port Knee exams and I should also see any exams before and after 5 days.

Please see attached report with sample data. You can ignore the last two fields on the far right. I was using them for reference purposes only.

Thanks.

 

[hilfy]

You're going to have to use the table that contains the exam data twice in the report. When you try to add a table that is already in the list, Crystal will throw up a message asking you if you want to Alias the table. It will then add the table with "_1" at the end of the table name. So, assuming that the table with the exam data is called "Exam", you'll have "Exam" and "Exam_1" in the table. Link from the patient data to Exam_1 in the same the same way you link to Exam and make the join a "Left Outer" join.

To set up the selection criteria, you'll need to edit the formula instead of just using the Select Expert. Assuming that your parameters are {?ExamName}, {?ScheduledDate} and {?Days}, you'll include something like the following in your Select formula:

{Exam.ExamName} = {?ExamName} and
{Exam.Date} = {?ScheduledDate} and
(
IsNull({Exam_1.Date}) or
{Exam_1.Date} in ({?ScheduledDate} - {?Days}) to {{?ScheduledDate} + {?Days})
)

Note where I've put the parentheses - this are very important for getting this to work correctly!

You would then display the data from Exam_1 on the report and not use anything from Exam, which is just used to filter the data correctly.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[gk17]

Thanks for the reply Dell.

I made the changes you suggested and I'm only getting back results for the Knee X-Ray now. I need the other results that are + - days within the scheduled date also. Those are not showing up.

 

[hilfy]

I assume that you're displaying the data from the original Exam table on the report, correct? Remove those fields and use the fields from the new copy of the Exam table instead.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[gk17]

Do you mean Exam vs Exam_1 fields? Yes, I deleted the Exam table fields already. I'm using all fields from Exam_1 table and it just shows one type of exam.

I was also using the following in the Select Expert (like you mentioned above):

{vusrExamPatientListing.ExamID} = {?ExamCodeID}

I removed it before posting here because it was limiting my results to only that one exam code. Instead I was using suppression formulas for my two details (for Exam and Exam_1 fields) to see if that would help. I got as close as displaying everything even those patients that don't have that exam.

 

[hilfy]

Can you post your whole selection formula?

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[gk17]

{vusrExamPatientListing.ScheduledDTTM} in {?FromDate} to {?ToDate} and
(
IsNull({vusrExamPatientListing_1.ScheduledDTTM}) or
{vusrExamPatientListing_1.ScheduledDTTM} in ({?FromDate} - {?Days}) to ({?ToDate} + {?Days})
) and
{vusrExamPatientListing.ExamID} = {?ExamCodeID}

 

[hilfy]

I think I really need to see the whole report at this point. If you go to my website -

http://www.dellstinnett.com

- you'll find a link where you can email me. Can you send the .rpt file to me (with saved data if possible - I realize this is confidential medical data you're dealing with so it may not be legal for you to include the saved data) so that I can play with it. I specifically want to look at the linking and grouping.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[hilfy]

Thanks for sending the .rpt to me. What you're seeing is actually due to the way you have the tables linked. Use the Patient table (vusrPatientNumbers) as the "Master" table. Link FROM the Patient ID in that table TO the Patient ID in the first exam table (vusrExamPatientListing). Leave this link as an Inner Join (default join type). Then Link FROM the Patient ID in the Patient table (again!) TO Patient ID in the second Exam table (vusrExamPatientListing_1). Right click on this join and go to Join Options. Make this a "Left Outer" join.

This should now get you the data that you need.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[gk17]

Thanks Dell. That resolved the issue

I had to use a second group also because the details section was still showing lots of duplicate records.