3rd Clause of IF statement not working

[beth4530]

I can't figure out why the 3rd "if" statement will not work..It works in a separate formula but not when combined in this one. I need it in one formula so I can group by it. Any ideas???

if sum({@Inter Ref Count},{Patient_Assignment.patient_assignment_id})=0 and
isnull({Patient.referral_source}) then "BLANK"
else
if sum({@Inter Ref Count},{Patient_Assignment.patient_assignment_id})=0 and
Not isnull({Patient.referral_source}) then {Patient.referral_source}
else
if sum({@Inter Ref Count},{Patient_Assignment.patient_assignment_id})<> 0
then "INT REF

 

[kray4660]

Always do the isnull check first. We have odd issues if Null is checked after other items. Also I thought that the Not was a function (thus the argument needs to be in parenthesis). Of course I am assuming that the @Inter Ref Count is working.

I hope this helps.

if isnull({Patient.referral_source})
and sum({@Inter Ref Count},{Patient_Assignment.patient_assignment_id})=0
then "BLANK"
else
if Not(isnull({Patient.referral_source}))
and sum({@Inter Ref Count},{Patient_Assignment.patient_assignment_id})=
then {Patient.referral_source}
else
if sum({@Inter Ref Count},{Patient_Assignment.patient_assignment_id})<> 0
then "INT REF"

 

[beth4530]

thank you for the suggestion but it still doesn't work. I'm getting the first 2 statements but not the third. I've rearranged the order without any success.

 

[lbass]

Please show the content of {@Inter Ref Count} and of any formulas nested within it.

Note that you will not be able to group by this formula even if you get it to work, since it contains summaries.

-LB

 

[beth4530]

I was able to get the statement to work using parens and now I'm trying to work around the grouping piece because as you said I can't use it due to the summaries..back to the drawing board.

 

[lbass]

I asked for the content of the formula because if it's not too complex, you could potentially replace the summaries with SQL expressions--then you would be able to use the formula in a sort.

-LB

 

[beth4530]

content of {@Inter Ref Count}

if isnull({Patient_Assignment_Staff.role}) then 0
else
if {Patient_Assignment_Staff.role}<> "INTREF" then 0
else
if {Patient_Assignment_Staff.role}= "INTREF" then 1

 

[lbass]

Can you please also identify your version of CR, type of database, and also copy your current SQL query (database->show SQL query) into this thread?

-LB

 

[beth4530]

CR 2008 SQL DB

SELECT Patient_Assignment.patient_id, Patient_Assignment.date_referred, Patient_Assignment.date_requested, Patient_Assignment.date_enrolled, Patient_Assignment.program_id, Patient_Assignment.protocol_id, Patient_Assignment.date_discharged, Patient_Assignment.discharge_reason, Patient_Assignment_Staff.role, Patient_Assignment.patient_assignment_id, Patient_Assignment_Staff.patient_assignment_id, Patient.referral_source
FROM (Provider.dbo.Patient_Assignment Patient_Assignment LEFT OUTER JOIN Provider.dbo.Patient_Assignment_Staff Patient_Assignment_Staff ON Patient_Assignment.patient_assignment_id=Patient_Assignment_Staff.patient_assignment_id) LEFT OUTER JOIN Provider.dbo.Patient Patient ON (Patient_Assignment.patient_id=Patient.patient_id) AND (Patient_Assignment.episode_id=Patient.episode_id)
WHERE (Patient_Assignment.discharge_reason IS NULL OR Patient_Assignment.discharge_reason<>'DEE') AND (Patient_Assignment.date_discharged IS NULL OR Patient_Assignment.date_discharged>={ts '2011-07-01 00:00:00'}) AND (Patient_Assignment.program_id='CMMH' OR Patient_Assignment.program_id='CMMR') AND (Patient_Assignment.date_enrolled IS NULL OR Patient_Assignment.date_enrolled>={ts '2011-07-01 00:00:00'}) AND (Patient_Assignment.date_requested IS NULL OR Patient_Assignment.date_requested>={ts '2011-07-01 00:00:00'}) AND (Patient_Assignment.date_referred IS NULL OR Patient_Assignment.date_referred>={ts '2011-07-01 00:00:00'}) AND (Patient_Assignment_Staff.role IS NULL OR (Patient_Assignment_Staff.role='CLINICIAN' OR Patient_Assignment_Staff.role='INTREF'))

 

[lbass]

Are you using a parameter for the date field?

-LB

 

[beth4530]

No- I'm not using a parameter- The dates are filtered within the report filter.

 

[lbass]

See if the following SQL expression returns the correct summary value (same as sum({@Inter Ref Count},{Patient_Assignment.patient_assignment_id})):

(
select count(B.role)
from Provider.dbo.Patient_Assignment A, Provider.dbo.Patient_Assignment_Staff B
where A.patient_assignment_id=B.patient_assignment_id and
(
A.discharge_reason IS NULL OR
A.discharge_reason<>'DEE'
) AND
(
A.date_discharged IS NULL OR
A.date_discharged>={ts '2011-07-01 00:00:00'}
) AND
(
A.program_id='CMMH' OR
A.program_id='CMMR'
) AND
(
A.date_enrolled IS NULL OR
A.date_enrolled>={ts '2011-07-01 00:00:00'}
) AND
(
A.date_requested IS NULL OR
A.date_requested>={ts '2011-07-01 00:00:00'}
) AND
(
A.date_referred IS NULL OR
A.date_referred>={ts '2011-07-01 00:00:00'}
) AND
A.patient_assignment_id=Provider.dbo.Patient_Assignment.patient_assignment_id and
B.role='INTREF'
)

This might be too complex for the the SQL expression editor to handle efficiently, but try it. Place the expression in the group section to see if it matches. If it does, you can plug it into your master formula and then group on that formula.

-LB

 

[beth4530]

Thanks LB- I'll give it a try