Need help with Grouping in Crystal XI please 1

[jpeters01]

My data looks like this:

Med RX#
Amoxicillin 0.00
Ampcillin 0.00
VITAMIN 3 CAP 444.4
BACITRACIN 123.4
FAMOTIDINE 352.3
THIAMINE 444.4
CYANOCOBALAMIN 444.4
Dorzolamine 0.00
FAMOTIDINE 352.3

I need the data to appear like this:
Amoxicillin 0.00
_______________________________________________
Ampcillin 0.00
_______________________________________________
BACITRACIN 123.4
_______________________________________________
CYANOCOBALAMIN 444.4
THIAMINE 444.4
VITAMIN 3 CAP 444.4
_______________________________________________
Dorzolamine 0.00
_______________________________________________
FAMOTIDINE 352.3
FAMOTIDINE 352.3

So...if the RX number <> 0.00 I need the meds grouped together but can't figure out how to do that. Can you help me with this please? Then I will need to sort the meds alphabetically like above. Thank you

 

[lbass]

Try creating a formula like this:

if {table.code} <> 0 then
totext({table.code}) else
{table.drugname}

Insert a group on this formula. Then place the drugname and other fields in the detail section. Insert a minimum on the drugname field, and then go to report->group sort and select minimum of {table.drugname} as your sort field, in ascending order.

-LB

 

[jpeters01]

Thank you for your reply lbass. The report I am working on was written by someone who is no longer here and I have been asked to modify it. It is more complex that what I normally do...but I am learning a lot!

The {table.drugname} can be one of two formulas so I don't know how to incorporate that into the formula you asked me to create:

@drug1 - the drugs listed in all CAPS, have RX numbers like 352.3. Formula:

if isnull({PatientMedication.COMPUTED_FORM}) then
Trim({PatientMedication.DESCRIPTION})
else
Trim({PatientMedication.DESCRIPTION}) + " " + Trim({PatientMedication.COMPUTED_FORM})
______________________
@drug2 - the drugs listed in lower case, that have an RX number of 0.00 Formula:

if isnull({PatientMedication.DESCRIPTION}) then '[' & {PatientMedication.BRANDNAME} & ']' else
if {PatientMedication.BRANDNAME} = ' ' then '' else
if {PatientMedication.BRANDNAME} = {@DescriptionForm} then '' else
'[' & {PatientMedication.BRANDNAME} & ']'
__________________
Also, there appears to be a sort formula that is marked as being used but I don't see it in the report anywhere.

@SortOrder:

if (isnull({PatientMedication.DESCRIPTION}) or {PatientMedication.DESCRIPTION} = ' ') then
left(trim({PatientMedication.BRANDNAME}),10)
else

if (isnull({PatientMedication.BRANDNAME}) or {PatientMedication.BRANDNAME} = ' ') then
left(trim({PatientMedication.DESCRIPTION}),10)
else
left(trim({PatientMedication.DESCRIPTION}),10)

I appreciate your help with this!

 

[jpeters01]

I looked again and found the @SortOrder in the Record sort expert.

 

[lbass]

Well, how did you get the two formulas to appear in one column with mixed cases? Are the drug names and the RX# code returned by one formula? I can't tell what is what. Which formula returned the original list you showed as sample data? Please show what the data looks like if you put the fields in the details section and label everything so that I can tell what fields are returning what result.

-LB

 

[jpeters01]

Sorry I did not explain this very well...there is a text object in the details section that includes these two fields.

@Description Form
@Display Brandname

This is how the drugs actually display (labeled with the field name). The report is based on a command that is pulling from two databases, that is why the drugs appear in both upper and lower case.

Med RX#
@Display Brandname = [Amoxicillin] 0.00
_____________________________________________
@Display Brandname = [Ampicillin] 0.00
_____________________________________________

@Description Form = BACITRACIN 123.4
@Display Brandname = [DOUBLE ANTIBIOTIC]
_____________________________________________
@Description Form = CYANOCOBALAMIN 444.4
@Display Brandname = [VITAMIN B-12]
_____________________________________________
@Description Form = Dorzolamine 0.00
@Display Brandname = [Cosopt]
_____________________________________________
@Description Form = FAMOTIDINE 352.3
@Display Brandname = [PEPCID]
____________________________________________________
@Description Form = FAMOTIDINE 352.3
@Display Brandname = [PEPCID]
____________________________________________________
@Description Form = THIAMINE 444.4
@Display Brandname = [VITAMIN-B12]
______________________________________________________
@Description Form = VITAMIN E CAP 444.4
@Display Brandname = [VITAMIN E]


Here are the formulas for the two drug fields

@Description Form:
if isnull ({PatientMedication.COMPUTED_FORM}) THEN
Trim ({PatientMedication.DESCRIPTION})
else Trim ({PatientMedication.DESCRIPTION})+""+
Trim ({PatientMedication.COMPUTED_FORM})

@Display Brandname:

if isnull ({PatientMedication.DESCRIPTION}) then '[' & {PatientMedication.BRANDNAME} & ']' else
if {PatientMedication.BRANDNAME} = '' then '' else
if {PatientMedication.BRANDNAME} = {@DescriptionForm} then '' else '[' & {PatientMedication.BRANDNAME} & ']'

Hope that makes sense...

Thanks!

 

[lbass]

What does this field {PatientMedication.COMPUTED_FORM} return? Is this the RX#? If this report is based on a command, why do the formulas reference a table?

-LB

 

[jpeters01]

(PatientMedication.COMPUTED_FORM} returns CAP, TAB, INJ, DSPK, etc. RX# is {PatientMedication.RXNUMBER}. I do not know why the formulas reference a table...I do not understand. When I go to the database expert and right click PatientMedication it gives me the option to edit the Command:

SELECT
'Impatient' AS Source,
pt.Active,
pt.Brandname,
pt.Computed_Form,
pt.Computed_FrequencySig,
pt.Computed_ItemOrder,
pt.Description,
pt.Dosestrength,
pt.FrequencyCode,
pt.HospitalNumber as Patient_ID,
pt.ItemType,
pt.PRN,
pt.Profile,
pt.Room,
pt.Route,
pt.RXNumber,
pt.StartDate,
pt.StopDate,
0 AS Dose_Amount,
'' AS Dose_Due,
'' AS Dose_Unit_Desc,
'' AS Frequency_Desc,
'' AS Last_Taken,
'' AS Med_Hist_Comment,
'' AS Med_Name,
'' AS Route_Description,
'' AS Status_Desc
FROM
HCS.PATIENTTHERAPY_VIEW pt
WHERE
pt.ROUTE <> 'IV' AND
pt.ROUTE <> 'EPID' AND
pt.PROFILE IN ('IT','IV','MED','TPN') AND
pt.ACTIVE = 'Y' AND
pt.FrequencyCode NOT IN ('NOW','ONCALL','ONCE','STAT','X1') AND
(pt.STOPDATE IS NULL OR pt.STOPDATE >= SYSDATE) AND
pt.HospitalNumber = '{?Patient_ID}'

UNION ALL

SELECT
'Home' AS Source,
Status_Code AS Active,
m.Med_Name AS Brandname,
'' AS Computed_Form,
m.Frequency_Desc AS Computed_FrequencySig,
0 AS Computed_ItemOrder,
Generic_Name AS Description,
'' AS Dosestrength,
m.Frequency_Desc AS FrequencyCode,
p.Patient_ID,
'' AS ItemType,
'' AS PRN,
'' AS Profile,
'' AS Room,
m.Route_Description AS Route,
0 AS RXNumber,
NULL AS StartDate,
NULL AS StopDate,
m.Dose_Amount,
m.Dose_Due,
m.Dose_Unit_Desc,
m.Frequency_Desc,
m.Last_Taken,
m.Med_Hist_Comment,
m.Med_Name,
m.Route_Description,
m.Status_Desc
FROM
Patient@hpclini2_live.clinicom.com p INNER JOIN
PF_Medication@hpclini2_live.clinicom.com m
ON m.CPI_SEQ = p.CPI_SEQ
WHERE
m.Status_Desc = 'Active' AND
p.Patient_ID = '{?Patient_ID}'

 

[jpeters01]

Is there something else that I can do to explain...do you think that you will be able to help me with this grouping/sorting I appreciate your help

 

[lbass]

Try the following:

Insert a group on this formula:

//{@group}:
if {PatientMedication.RXNUMBER} <> 0 then
totext({PatientMedication.RXNUMBER},1) else
trim({@Display Brandname})

Then create a second formula:

//{@drugname}:
if {PatientMedication.RXNUMBER} <> 0 then
trim({@Description Form}) else
trim({@Display Brandname})

Place this formula in the detail section and insert a minimum on it at the group level. Then go to report->group sort and select "minimum of @drugname" as your group sort field, ascending.

You can leave the existing sort formula as is, I think. Not sure if it will add anything or not.

I can't really mock up a report like this, so please follow these steps and report back with the resulting display if it doesn't work.

-LB

 

[jpeters01]

Thank you for your reply. I did what you requested (except modified the ->group sort and select "minimum of @drugname" as your group sort field, from ascending to descending) and this is how it displays which is close but the Dorzolamine should display after the CYANOCOBALAMIN

@Display Brandname = [Amoxicillin] 0.00
_____________________________________________
@Display Brandname = [Ampicillin] 0.00
_____________________________________________
@Description Form = Dorzolamine 0.00
@Display Brandname = [Cosopt]
_____________________________________________
@Description Form = BACITRACIN 123.4
@Display Brandname = [DOUBLE ANTIBIOTIC]
_____________________________________________
@Description Form = CYANOCOBALAMIN 444.4
@Display Brandname = [VITAMIN B-12]
_____________________________________________
@Description Form = FAMOTIDINE 352.3
@Display Brandname = [PEPCID]

@Description Form = FAMOTIDINE 352.3
@Display Brandname = [PEPCID]
____________________________________________________
@Description Form = THIAMINE 444.4
@Display Brandname = [VITAMIN-B12]

@Description Form = VITAMIN E CAP 444.4
@Display Brandname = [VITAMIN E]
____________________________________________________

 

[lbass]

Try changing this formula to:

//{@drugname}:
if isnull({@Description Form}) or
trim({@Description Form}) = "" then
trim({PatientMedication.BRANDNAME}) else
trim({@Description Form})

-LB

 

[jpeters01]

That looks good !!! I have to run to a meeting but will need to do more testing later. I will let you know if I run into any issues.

 

[jpeters01]

The alpha sorting looks good, thank you!! In testing I found there is another sorting issue that I need to correct. The drugs can sometimes display a start date/time. For example:

@Description Form = CYANOCOBALAMIN 444.4
@Display Brandname = [VITAMIN B-12]
_____________________________________________
@Description Form = FAMOTIDINE Start 6/10-10:00 352.3
@Display Brandname= [PEPCID]

@Description Form = FAMOTIDINE 352.3
@Display Brandname = [PEPCID]
____________________________________________________

@Description Form = FAMOTIDINE Start 6/8-21:00 478.4
@Display Brandname = [PEPCID]
___________________________________________________

But need to display chronologically like this:

@Description Form = CYANOCOBALAMIN 444.4
@Display Brandname = [VITAMIN B-12]
_______________________________________________
@Description Form = FAMOTIDINE Start 6/8-21:00478.4
@Display Brandname = [PEPCID]
___________________________________________________

Description Form = FAMOTIDINE Start 6/10-10:00352.3
@Display Brandname = [PEPCID]

@Description Form = FAMOTIDINE 352.3
@Display Brandname = [PEPCID]
____________________________________________________

The @StartDate formula is:

if not isnull ({PatientMedication.STOPDATE}) then totext ({PatientMedication.STARTDATE},"MM/dd - HH:mm") else
if isnull ({PatientMedication.STARTDATE}) then ' ' else
if {PatientMedication.STARTDATE} > CurrentDateTime then totext({PatientMedication.STARTDATE},"MM/dd - HH:mm")

I tried creating a group on @StartDate and inserted a minimum for that group and used that as the sort in the Group Sort expert but that doesn't work. Can this be done?

 

[jpeters01]

Please disregard...I figured it out. Thanks again for your help!