HELP SORTING OR GROUPING

[smt2004]

The following formulas are used to determine compliance. i need to sort on the results which is N/A. NO, YES. Any suggestions? i do not get the option to sort or group with this formula.
below is the main formula. the patient type formula is below

IF {Command.BATH_STATUS}='NOT DOCUMENTED' THEN 'NO'
else
IF {@PATIENT TYPE}='STANDARD' AND {Command.BATH_STATUS}='WASHED / GIVEN'
AND ISNULL({Command.STANDARD_BATH})THEN 'NO'
ELSE
IF {Command.BATH_STATUS}='Refused' THEN 'N/A'
ELSE
IF {Command.MEAS_VALUE} LIKE '*Refuse*' THEN 'N/A'
ELSE
IF {Command.MEAS_VALUE} LIKE '*Contrain*' THEN 'N/A'
ELSE
IF {Command.BATH_STATUS}='NOT DOCUMENTED' THEN 'NO'
ELSE
IF {Command.BATH_STATUS}='NOT GIVEN' THEN 'NO'
ELSE
IF {Command.MEAS_VALUE} LIKE '*Incontinence care*' THEN 'NO'
ELSE
IF {Command.MEAS_VALUE} LIKE '*Independent*' THEN 'YES'
ELSE
IF {Command.MEAS_VALUE} = 'NOT GIVEN' THEN 'NO'
ELSE
IF{@PATIENT TYPE}='STANDARD' AND {Command.BATH_STATUS}='WASHED / GIVEN'
AND {Command.STANDARD_BATH} ='Standard bathing wipes' THEN 'YES'
ELSE
IF{@PATIENT TYPE}='STANDARD' AND {Command.BATH_STATUS}='WASHED / GIVEN' AND NOT ISNULL({Command.STANDARD_BATH})THEN 'YES'
ELSE
IF{@PATIENT TYPE}='STANDARD' AND {Command.BATH_STATUS}='WASHED / GIVEN' AND NOT ISNULL({Command.STANDARD_BATH})
AND NOT ISNULL({Command.CHG})THEN 'YES'
ELSE
IF{@PATIENT TYPE}='STANDARD' AND {Command.BATH_STATUS}='WASHED / GIVEN' AND NOT ISNULL({Command.STANDARD_BATH})
AND ISNULL({Command.CHG})THEN 'YES'
ELSE
IF{@PATIENT TYPE}='CHG' AND {Command.BATH_STATUS}='WASHED / GIVEN' AND NOT ISNULL({Command.STANDARD_BATH})
AND NOT ISNULL({Command.CHG})THEN 'YES'
ELSE
IF{@PATIENT TYPE}='CHG' AND {Command.BATH_STATUS}='WASHED / GIVEN' AND NOT ISNULL({Command.CHG})THEN 'YES'
ELSE
IF{@PATIENT TYPE}='STANDARD' AND {Command.BATH_STATUS}='WASHED / GIVEN' AND ISNULL({Command.STANDARD_BATH}) THEN 'NO'
ELSE
IF{@PATIENT TYPE}='CHG' AND {Command.BATH_STATUS}='WASHED / GIVEN' AND {Command.STANDARD_BATH} ='Standard bathing wipes'
AND ISNULL({Command.CHG}) THEN 'NO'
ELSE
IF{@PATIENT TYPE}='STANDARD' AND {Command.BATH_STATUS}='WASHED / GIVEN'
AND ISNULL({Command.STANDARD_BATH}) THEN 'NO'

the PATIENT TYPE formula is

IF {Command.DEPARTMENT_ID} IN [2133009,4313023,4313015,4313016,4314007,4314009,4314027,5119219,5119221,5115578,73,5115579,5119222,5123216,
5133207,5119223,5101202,5133026,5119220,5120202,5119218,5119235] THEN 'CHG'
ELSE
IF NOT ISNULL({Command.IP_LDA_ID_A}) THEN Minimum ({Command.CL_PATIENT_TYPE_A}, {Command.Y_MRN})
ELSE
{Command.RM_PATIENT_TYPE}

i tried to use

if {@compliance} = 'n/a' then 1
else
if {@compliance} = 'no' then 2
else
if {@compliance} = 'yes' then 3

i still cant use that either.

 

[pmax9999]

It is not obvious from he code why the formulas are not offered for sorting.

If you place the formula fields on the report canvas, do they return data correctly?

Given the report is built on a Command, my advice would be to build additional columns within the Command object to provide a simple data set on which to base the sort.

Hope that helps.

Cheers
Pete

 

[kray4660]

The Patient Type formula has a aggregate in it. You cannot sort or group on formulas with aggregates.

 

[lbass]

You should create an aggregate expression within your field list in the command so that you can replace the minimum in your formula with that new field. Not sure what table or tables exist for the two fields in the function, or how the tables are linked so can’t really help further without that info.

-LB