Need help suppressing duplicates in details section 1

[jpeters01]

Hi, My data looks like this in the details section of my report:

Description Area NDC(string) Onhands
Acetaminophen ED 123 1
Actaminophen ICU 1234 3
Acetaminophen ED 123 2
Acetaminophen 4W 12345 4
Acetaminophen TCU 222 5
Acetaminophen 3S 222 0
Bactrim ED 333 0

I need to add an X in a column when the Area is ED and onhands > 0. I also need to remove duplicate NDC's so that the data looks like this:

Description Area NDC(string) ED
Acetaminophen ED 123 X
Actaminophen ICU 1234
Acetaminophen 4W 12345
Acetaminophen TCU 222
Bactrim ED 333

I have added the formula for the 'X', but the formula I created in the section expert suppression formula, {TABLE.NDC} = next ({TABLE.NDC}) is not working, it suppresses some of the duplicate NDC rows but not all of them. I tried creating a formula for the NDC to make it a number and then used that for the suppression formula but that didn't work either.

Can you assist me with this please?

 

[lbass]

You should group on the NDC field and place the detail fields in the group header (remove the group name). For the X formula, use:

if sum({table.onhands},{table.NDC}) > 0 and
{table.area} = "ED" then "X"

This assumes that an NDC can have only one area associated with it.

-LB

 

[jpeters01]

LBass, Thanks for the reply. An NDC does have more than one area...sorry I should have made that clear.

 

[lbass]

Then show a sample that demonstrates this and how you want the results to look.

-LB

 

[jpeters01]

Here is the data with multiple areas/same NDC codes:

Description Area NDC(string) Onhands
Acetaminophen ED 123 1
Actaminophen ICU 1234 3
Acetaminophen ED 123 2
Acetaminophen 4W 12345 4
Acetaminophen TCU 222 5
Acetaminophen 3S 222 0
Bactrim ED 333 0

Basically we are getting a list of all NDCs (medications) that are available in the dispensing machines throughout the hospital. The NDC should only be displayed once on the report. If the medication is available in the ED dispensing machine (determined by if there are "onhands" in the ED areas) then we need it designated by putting an X in a column. We do not need the areas of the medications listed on the report. This is the how the results should be displayed:

Description NDC(string) Onhands in ED
Acetaminophen 123 X
Acetaminophen 1234
Acetaminophen 12345
Acetaminophen 222
Bactrim 333

Does that make sense?

 

[lbass]

Okay, still group on NDC and place details in the group header, but create a formula {@EDOH} like this:

if {table.area} = "ED" then
{table.onhands}

Then use a formula like this for X in the group header:

if sum({@EDOH},{table.NDC}) > 0 then
"X"

-LB

 

[jpeters01]

Thank you that worked great!