Skip to Content
0
Former Member
Aug 18, 2015 at 07:01 PM

Grouping challenge

18 Views

Hi. I've got a report that lists categories, products, and status. I would like to display grouping by Category and Product. I need to suppress the product group if the product status fits certain criteria.

If the product status = "Good" then display that group. If the product status = "Expired" then display that group. If a product has a record that is both "Good" and "Expired", then only display the "Good" group.

My first thought was to group by Product, and do a descending sort by Status. Then the Product would be listed only once, and if a product had both "Good" and "Expired", only the "Good" status products would appear, due to the descending sort of Status. The problem is I can't group by Product and sort by Status.

I tried inserting a Status group between Category and Product, then suppressing the Status group. That displayed all of the groups, but it listed the products that had both "Good" and "Expired".

For a visual, this is what might show up when I group by Category, Status (suppressed), and Product. As you can see, Apples is listed twice. What I would like is to suppress the "Apples - Expired" and keep the "Apples - Good" in the list.

Category

Fruit

Product - Status

Apples - Good

Bananas - Good

Pears - Good

Apples - Expired

Oranges - Expired

Any advice would be great. Thanks!