INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Return records using group selection?

Return records using group selection?

(OP)
Hi,

I'm trying to return patients with their exams if they have more than one exam performed on the same day. I seem to have that working but now the end user wants another modification where it only returns those patients and exams if they had a specific exam performed on that date. For example, let's say patient John Doe had the following exams:

Xray of wrist done on 4/19/2016
Xray of knee done on 4/19/2016
Ultrasound of hip done on 4/19/2016
MRI of knee done on 4/10/2016


If I do a search for Ultrasound of the hip and enter 4/19/2016 for the date, it should return the following exams:

Xray of wrist
Xray of knee
Ultrasound of hip


If I enter MRI of knee and enter 4/10/2016, it should not return anything since there's no more than one exam performed on that date.

I've been using the following record/group selection formulas but don't know what to modify to get this to work:

CODE --> RecordSelection

({?Exams}='' or ','+{ExamCode}+',' in ','+Replace({?Exams}," ","")+',') and
{ExamDate} in {?FromDate} to {?ToDate} and 

CODE --> GroupSelection

DistinctCount ({Dept}, {ExamDate}, "daily") > 1 

The report itself is grouped by Patient Name first and then the ExamDate.

Using Crystal Reports v14

Thanks.

RE: Return records using group selection?

If I understand correctly you are changing the report to search for a single date rather than a date range.
If you're selecting on date, then you don't need to group on date.

Here is one way to get to what you want.

Formula INIT (goes in report header and Group Footer 1b (which is suppressed))
SHARED STRINGVAR FOUND := 'NO';
SHARED STRINGVAR ELIST := ' '

Formula EXAMS goes on the suppressed detail line
SHARED STRINGVAR ELIST;
SHARED STRINGVAR FOUND;
if [table.EXAM_CODE} = {?Exams}
then (FOUND := 'YES' ; ELIST := ELIST & [table.EXAM_CODE} & ", ")
ELSE (FOUND := FOUND ; ELIST := ELIST & [table.EXAM_CODE} & ", ")

The suppression formula for Group Footer 1B (which is where you put all your data) is
SHARED STRINGVAR FOUND;
FOUND = 'NO' // note this is just = and the others are :=)

One more formula: sholist (goes in group footer with other data)
SHARED STRINGVAR ELIST;
LEFT(ELIST,(LEN(ELIST)-2)

This will give you: X-ray or wrist, X-ray of knee, Ultrasound of hip

RE: Return records using group selection?

(OP)
Thanks for the reply.

We still want to use a date range. There will be a lot of patients where they may have multiple exams in any given date range. The problem is that I don't know how to only select those patients who have a specific exam performed (with other exams on the same day) and show only those patients. If the end user doesn't enter anything for the examcode, it shows the data perfectly. But once they start fine tuning it and want to only look for those exams where another specific exam was performed, it throws my report off. Sorry for the confusion. Trying to phrase this better. Here's another example:

Xray of wrist done on 4/19/2016
Xray of knee done on 4/19/2016
Ultrasound of hip done on 4/19/2016

MRI of knee done on 4/10/2016
Cat Scan of neck done on 4/10/2016

Xray of knee done on 4/4/2016

Xray of knee done on 3/1/2016
Ultrasound of hip done on 3/1/2016

If the end user entered date range from 3/1/2016 to 4/30/2016 for Xray of the knee exams, the report should return the following:

Xray of wrist done on 4/19/2016
Xray of knee done on 4/19/2016
Ultrasound of hip done on 4/19/2016
Xray of knee done on 3/1/2016
Ultrasound of hip done on 3/1/2016

Nothing from 4/10 date will show up because it doesn't have an Xray of the knee exam. The Xray of the knee done on 4/4 will also not show up because there were no other exams performed on that same day.

I'm not sure if I can still leave my two groups as they are (Patient, ExamDate) or it requires some change in order to get this to work. The above is perfectly fine if no examcode parameter is entered. It would return all those exams listed above except for the single exam done on 4/4

Thanks.

RE: Return records using group selection?

Okay, just change any where that I said Group 1 to Group 2.

RE: Return records using group selection?

(OP)
OK. Updated it from Group 1 to Group 2.

Is it correct to say that Group 2b has the INIT formula, is suppressed and also uses the suppression formula you provided? Just want to confirm because I see from past experience that using both suppression formula and suppressing the section seems to counter each other out or have other effects.

Are all my fields going to be in Group Footer 2B and the "shotlist" formula will be in Group Footer 1?

I'm getting an error for the sholist formula:

LEFT(ELIST,(LEN(ELIST)-2)

I added in the missing right parenthesis but have the following error now when I run it:

String length is less than 0 or not an integer

RE: Return records using group selection?

The init formula goes in Group Footer 2B, the data and the suppress formula go in Group Footer 2A.
Sholist needs to be in group footer 2A.

RE: Return records using group selection?

(OP)
I updated the report and it's running now but with two problems:

1. It needs to show the details for each exam under the patient name. Since it's in the group, it's only showing the patient name.

2. If I enter a specific examcode, it shows me an error again saying the string length is less than 0 or not an integer.

RE: Return records using group selection?

Okay, then lets make this change to sholist:
SHARED STRINGVAR ELIST;
if len(ELIST) < 3
THEN ' '
ELSE
LEFT(ELIST,(LEN(ELIST)-2)

That should solve the sting length problem.

The date and sholist should be in group footer 2a That will give you a list of procedures.
The patient's name would be in Group Header 1.

RE: Return records using group selection?

(OP)
I made the change for sholist and it still doesn't return any results. Just to make sure I'm on the right page, this is what I currently have:



The suppression formula you provided originally is in GF 2B

If I don't enter any examcode parameter, it does return data but it shows in as follows:

Group by Patient: Patient
Group by Exam Date: @shotlist (comma separated examcodes), Exam Date, Exam Code <-- only showing the last entry due to being in footer

My original report had the data fields in the details section so each exam code and their details are on its own line. I need it to be in the following format:

Group Header for Patient: Patient, DOB
Group Header for <some field-currently examdate>: Suppressed
Details Section: ExamDate, ExamCode, etc. other data fields

Thanks.

RE: Return records using group selection?

You need a lot more space for sho list, and I'd also click "Can Grow"
Squeezing it down that small you won't see anything.

RE: Return records using group selection?

(OP)
Yes, I set the setting to grow to see what it would look like. The examcodes are all grouped into that one field. I need it to be a separate field so I can add in the other fields. I have about 10 fields that should be on one line. Not sure if I can just add these other fields in the sholist formula, but even if that's possible it won't be aligned properly for each field.

RE: Return records using group selection?

If I understand you correctly, your logic is like the following:

Select all patients who had multiple exams in the time period, but only if one of their exams was a specific one you're looking for. If that's correct, the easiest, most efficient way to get this without having to jump through multiple hoops in Crystal would be to use a Command or Stored Procedure to get the data for the report. A command is just a SQL Select statement, so if you know how to write SQL, this shouldn't be too hard. When you use a command, you should get ALL of the data for the report in a single command and only include other tables or commands if you need them to pull data for dynamic parameters. You NEVER join a command with other commands or tables - that will cause Crystal to pull all of the data into memory and process it there instead of pushing the SQL to the database.

The basic logic of your command will be something like this:

CODE

Select
  <all of the fields you need for the report>
from <tables joined together, including patient table>
where ExamDate between {?StartDate} and {?EndDate}
and exists (
    select 1
    from <Exam table> as exam1
    where exam1.PatientID = patient.PatientID
    and exam1.ExamDate between {?StartDate} and {?EndDate}) 

You can get more information about how to work with commands here: http://scn.sap.com/community/crystal-reports/blog/...

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: Return records using group selection?

(OP)
Hi Dell,

I want to give the user an option to enter in a value or values (comma separated) for the exam parameter but they may leave it blank as well (which is currently working with the way I have it set up). It's when they enter the exams or comma separated exams when the report fails to return any data.

I have worked with SQL Expressions within Crystal before but usually use Microsoft SQL Studio for those queries that don't need to look nice and formatted. I'm not sure if this will be the same as SQL commands but will look into that. What changes will be needed in the command if the parameter can be left blank also?

Thanks.

RE: Return records using group selection?

If the parameter can be left "blank" by the user, then you need to set a default value, like "*All" on it so that the command never receives a blank value. See the link I provided for specific info about how to use commands efficiently.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close