Suppress detail rows when group header is also suppressed

I'm trying to figure out how to suppress all detail rows when the parent group header will be suppressed. Here is an example of the data I'm getting: <img src='http://share.mygeekdaddy.net/sampledata_grouping_2013-07-24.png' alt='Posted Image' class='bbc_img' /> The group header is getting suppressed with the following conditional statement on the visibility property:<pre class="_prettyXprint _lang-js _linenums:1">if (row["asset_status"]=='OPERATING') { true } else { false }</pre>So the next step I want to take is suppressing all the detail rows (which show the asset number/asset desc/asset status) when either the parent group header has been suppressed or when any of the detail records in the group are listed as 'OPERATING'. So if everything worked properly, only items 1292 and 1751 would show up on the report. Thanks for all the help in advance!

Find more posts tagged with

Comments

jverly01

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. 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:<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>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. Hope this helps!

mwilliams

Glad you were able to get it working! Thanks for updating the thread with your solution!