Discoverer Report - how to add Inspected items on failed inspection report

Question

I am trying to create a report to show all defects received and the defect reason, however I cannot work out how to add the reason for the defect which appears against an Inspected item. Its ok if the details are in the comments box but when they are against an item, for example item 3 Saftey Zone, and the detail is listed in the Defect text I can't work out how to show this. the current items in my report are:- 
 TWor Works Ref 
 Tire Date 
 Tire Date created 
 Highway Authority 
 Titp Meaning 
 Tica Category Name 
 Tiot Meaning 
 Twor External Ref 
 Tphs Contractor Name 
 Tirs Site Comment Text 
 Tire comments 
 Tirs Slg Default Text 
 Tirs SLG Default flag 
 Tirs Site No 
 Tirs Sampled Site 
 Tirs Site Location Text 
 Any help would be appreciated. Thanks ali

Lee Jackson · Accepted Answer

Hi Ali, 
 What Jodie and the MAS team have done is add the Information Manager views to your existing business area, which gives you a lot more data to choose from. 
 When selecting data from multiple tables or views, you need to create a join between them in order to identify which records should be returned, 
 In this case a join has been created between TMA_INSP_RESULTS_ALL table and the IMF_TMA_INSP_RESULT_LINES view using the inspection ID. 
 I have now added the inspection Item Type Name and Defect Reason Text to the report for you. I also added a condition to only select Defect Reason Text where it is not null, which should make the results clearer. I've saved this report as Failed Inspections - test 2' and shared the report with you. 
 Please could you review the report and let us know if it fulfils your requirements, or any further questions you have? 
 Thanks,

Lee Jackson · Answer

Hi Ali, 
 I think this is related to a feature in Oracle Discoverer called 'Fan Trap Detection'. 
 From what i've read online, this feature can cause errors like you've seen, even though the report is ok. 
 I suspect the 'Fan Trap' is being detected because of the way the data is held in the database and then selected, but the report results should actually be ok. 
 Please could you: 
 1 - Log in to Discoverer Plus 
 2 - Navigate to Tools > Options >Advanced 
 3 - Tick the 'Disable Fan Trap Detection' box 
 4 - Try the report again. 
 This should solve the error you're seeing, but please let me know if it doesn't. 
 If you can then run the report, please could you analyse the results in detail and let me know if it looks ok or not? 
 Thanks,

Lee Jackson · Answer

Hi Ali, 
 In order to return the site item details, you will need to link the TMA_INSP_RESULTS_SITES_ALL 
 table (that you're already using) to the TMA_INSP_RESULT_LINES table, and collect the site item details from there. The link will need to be done using the TIRS_INSPECTION_ID and TILI_INSPECTION_ID columns. 
 Please have a look and let us know how you get on.

Lee Jackson · Answer

Hi Ali, 
 As a courtesy we have created the report for you on NEDL, but it has been done slightly differently to the one on YEDL. 
 Please see TMA Inspection Report (TSG) which is currenlty only available to users ATLAS and APSUP. 
 This version has the same fields and conditions as the YEDL report, so it should return the same data, but it is soley based on Information Manager views. 
 The IMF views give better descriptions for each of the fields, making it easier to navigate and edit the report. By only using the IMF views it should also improve runtime performance. 
 Please have a look at this and let me know if it looks ok covers all the data you need. 
 Thanks,