Return records using group selection?

[gk17]

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:

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

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

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

Using Crystal Reports v14

Thanks.

 

[Charliy]

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

 

[gk17]

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.

 

[Charliy]

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

 

[gk17]

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

 

[Charliy]

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.

 

[gk17]

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.

 

[Charliy]

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.

 

[gk17]

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.

 

[Charliy]

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.

 

[gk17]

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.

 

[hilfy]

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:

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/crysta...ices-when-using-commands-with-crystal-reports

-Dell

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

http://www.decisionfirst.com

 

[gk17]

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.

 

[hilfy]

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

http://www.decisionfirst.com