EDI Reporting and curious case of missing 997

We all agree that out of box EDI reporting in Biztalk leaves lot of gaps. For example, it is not easy to find all messages which were sent to our partners that didn't send 997 back to us. One can go on Biztalk console and click each message to find if Ack was received. But this is a very tedious approach and time consuming too. Instead, creating a query using BAM views can quickly give us desired results. This way a report can be set up using SSRS that can be viewed across the organization.

SELECT TOP (100) PERCENT InterchangeControlNo, ReceiverID, ReceiverQ, ApplicationReceiver, DATEADD(HOUR, - 5, TimeCreated) AS ProcessTime, TransactionSetId
FROM dbo.bam_TransactionSetActivity_AllInstances AS T
WHERE (SenderID LIKE ' [your org ID] %') AND (TransactionSetId <> '997') AND (NOT EXISTS
(SELECT RecordID, ActivityID, InterchangeActivityID, GroupControlNo, InterchangeControlNo, ReceiverID, SenderID, ReceiverQ, SenderQ, InterchangeDateTime,
Direction, AckProcessingStatus, AckStatusCode, DeliveredTSCount, AcceptedTSCount, AckIcn, AckIcnDate, AckIcnTime, ErrorCode1, ErrorCode2,
ErrorCode3, ErrorCode4, ErrorCode5, TimeCreated, RowFlags, LastModified
FROM dbo.bam_FunctionalAckActivity_AllInstances AS F
WHERE (GroupControlNo = T.GroupControlNo) AND (SenderID = T.ReceiverID)))
ORDER BY TimeCreated DESC