If a report uses multiple tables it should not appear on SQL Perf Main report when not all tables are present

[PiJoCoder]

In this case there are multiple tables in the report datasets. So if one of the tables are missing, but the main one is there - dbo.tbl_dm_db_file_space_usage, the report will be shown as clickable in the SQL Perf Main

insert into @tbl_reports (ReportName, ReportDisplayName, ReportDescription, VersionApplied, Category, ValidationObject, SeqNo, DataCollection,Manditory, CategorySeq) values ('Tempdb_Space_Use_C', 'Tempdb Space Usage', 'Breakdown of space usage in tempdb', 1|2|4|8, 'Performance', 'dbo.tbl_dm_db_file_space_usage', 4000, 'Tempdb and Trans Collector', 1,3000)

And yet when you click the report, you can get a failure because another table is missing

[PiJoCoder]

Possible solution: re-architect DataSet_Reports queries to somehow account for multiple tables, not just one. One quirky solution, that may work is:

select
CategorySeq, Category, ReportName, ReportDisplayName, ReportDescription, DataCollection, SeqNo, case when (Sum(ReportAvailable) - Count(ReportAvailable)) = 0 then 1 else 0 end    ReportAvailable
from 
(
select  CategorySeq,Category, ReportName, ReportDisplayName, ReportDescription, DataCollection, case when indx.rowcnt >0 then 1 else 0 end  ReportAvailable, SeqNo
 from @tbl_reports  rep  
 left outer join sysindexes indx on ( OBJECT_ID (rep.ValidationObject) = indx.id and indx.indid in (0,1,300)) 
 ) t
 group by CategorySeq, Category, ReportName, ReportDisplayName, ReportDescription, DataCollection, SeqNo
 order by CategorySeq, SeqNo, ReportName

[hacitandogan]

This modification can be tested via just renaming some tables and seeing corresponding reports disappearing from SQL Perf Main. This should prevent errors similar to the screenshot on this issue.