Suppress detail rows when group header is also suppressed

<p>Amazing what happens when you get a little sleep and talk to a few friends. Looks like I found two ways to get this done and wanted to pass them along.</p><p> </p><p>1. Refine SQL Query: My original method was trying to use an EXISTS statement, but it kept excluding just single rows of records, not groups as I explained above. So I added a sub-select to check if the group met the conditions and then excluded records that didn't meeting the grouping criteria:</p><pre class="_prettyXprint _lang-sql _linenums:1">sqlText = "select sparepart.itemnum, item.description, sparepart.assetnum, asset.description as asset_desc, asset.status as asset_status, a.binnum, a.category, a.status as inv_status, b.curbal "+ " from sparepart "+ " join item on sparepart.itemnum = item.itemnum and sparepart.itemsetid = item.itemsetid "+ " join asset on asset.assetnum=sparepart.assetnum and asset.siteid=sparepart.siteid and asset.orgid=sparepart.orgid "+ " join inventory a on a.itemnum=sparepart.itemnum and a.siteid=sparepart.siteid and a.itemsetid=sparepart.itemsetid "+ " join invbalances b on b.itemnum = a.itemnum and b.location = a.location and b.binnum = a.binnum and b.itemsetid = a.itemsetid and b.siteid = a.siteid "+ " LEFT JOIN (SELECT COUNT(*) opcnt, sparepart.itemnum, sparepart.siteid "+ " FROM sparepart "+ " JOIN item ON sparepart.itemnum = item.itemnum AND sparepart.itemsetid = item.itemsetid "+ " JOIN asset ON sparepart.assetnum = asset.assetnum AND sparepart.siteid = asset.siteid AND sparepart.orgid = asset.orgid "+ " WHERE asset.status = 'OPERATING' "+ " GROUP BY sparepart.itemnum, sparepart.siteid ) OperatingCount ON sparepart.itemnum = OperatingCount.itemnum AND sparepart.siteid = OperatingCount.siteid "+ " WHERE ISNULL(OperatingCount.opcnt, 0) = 0 and sparepart.siteid= ? and a.status='active' and b.binnum is not null"+ " ORDER BY sparepart.itemnum, sparepart.assetnum "</pre><p>2. Aggregation Check: Because I wanted both the group and the detail row to be suppressed based on any status record being equal to 'OPERATING', a friend of mine (thanks Brian!) suggested putting a check on each row to see if the status for the detail row was 'OPERATING'. If it was, set the conditional value to 1, otherwise 0. Then make an aggregate value to check the max value of the detail row. Now use that aggregate value to set the visiblity filter for the group header and detail rows.</p><p> </p><p>Hope this helps!</p>