How to pull a specific value from a list of values 2

[LisaRR]

I have a report that displays all departments a patient has been in during their hospitalization. The report is grouped by Patient Account, a field in the details lists all departments the patient was in. Sample with relevant fields:

Group 1 : Patient Acount - 1234
Patient Account Dept ID Dept Name Date ICU Formula
1234 123 Gen Med 5/10/2012 0
1234 456 ICU 5/12/2012 1
1234 123 Gen Med 5/16/2012 0

I need to display in the group footer the Dept Name when it is an ICU department.

I created a formula to identify the ICUs and placed it in the details:
If {table.Dept_ID} in [456,137,689] then 1 else 0

I placed another formula in the group footer to identify when @ICUFormula = 1:
If {@ICU Formula} = 1 then {table.DEPT_ABBREVIATION}

This formula only displays the icu if it was the last location in the list. Appreciate any suggestions as I am working on a tight timeline. Thanks!

 

[Charliy]

You need to use a shared variable.

First create a second group footer and suppress it.
Now you need a formula to set up and clear the variable

// init
SHARED NUMBERVAR ICU : = 0

Place this formula in your report header and the second group footer.

Now you need a formula to test each record

// testicu
SHARED NUMBERVAR ICU ;
If {@ICU Formula} = 1 THEN ICU := 1 ELSE ICU := ICU

put that in the detail section

Then in the first group footer put this formula:
// showicu
SHARED NUMBERVAR ICU ;
IF ICU = 1
THEN "ICU"
ELSE "

 

[LisaRR]

Thank you!!! I've incorporated the formulas into my report and it appears to be working so far.

I need one minor change - when it is determined that there is an icu record, I need to display the department name {table.DEPT_ABBREVIATION}. How do I include that in the formula?

 

[Charliy]

Change it from a NUMBERVAR to a STRINGVAR, then make the test formula

If {@ICU Formula} = 1 THEN ICU := {department} ELSE ICU := ICU

 

[LisaRR]

Thanks for the direction, I made a few modifications and got it to work. Thank you!

 

[lbass]

Create this formula:

If {table.Dept_ID} in [456,137,689] then
{table.deptname}

Place this in the detail section and insert a maximum on it at the group level.

-LB