Find all reports based on a certain table in DWH
Answered
Hi,
I need to find all Reports which use a certain table from our DWH.
Before I investigate myself, I was wondering if anyone has a ready made repository query?
I know that would most likely only work for clean mappings of tables and columns and not any virtual tables etc. But that would be a start.
Many thanks in advance,
Ingo
The same question
Here is the query that was created by one of our Yellowfin Consultants to retrieve this information:
Hi Ingo,
You can use this query to find the columns and reports that are using a particular database table.
All you need to do is fill in the filters highlighted in blue. This would be the view name and also the table name alias.
select distinct rv.viewid as 'tableid'
, rv.viewname as 'tablename'
, rv.viewdescription as 'aliastablename'
, rv2.viewname as 'viewname'
, rft.columnname as 'columnname'
, rft.shortdescription as 'aliascolumnname'
, rf.columnheading as 'reportcolumnname'
, rh.reportname as 'reportname'
from reportview rv
left outer join reportview rv2
on rv2.viewid = rv.parentviewid
left outer join ReportFieldTemplate rft
on rft.subviewid = rv.viewid
left outer join reportfield rf
on rf.fieldtemplateid = rft.fieldtemplateid
left outer join reportheader rh
on rh.reportid = rf.reportid
where rv.viewdescription = 'ATHLETEFACT_BC'
and rv2.viewname = 'New View'
and reportname IS NOT NULL
and rh.reportstatuscode = 'OPEN'
Here is the query that was created by one of our Yellowfin Consultants to retrieve this information:
Hi Ingo,
You can use this query to find the columns and reports that are using a particular database table.
All you need to do is fill in the filters highlighted in blue. This would be the view name and also the table name alias.
select distinct rv.viewid as 'tableid'
, rv.viewname as 'tablename'
, rv.viewdescription as 'aliastablename'
, rv2.viewname as 'viewname'
, rft.columnname as 'columnname'
, rft.shortdescription as 'aliascolumnname'
, rf.columnheading as 'reportcolumnname'
, rh.reportname as 'reportname'
from reportview rv
left outer join reportview rv2
on rv2.viewid = rv.parentviewid
left outer join ReportFieldTemplate rft
on rft.subviewid = rv.viewid
left outer join reportfield rf
on rf.fieldtemplateid = rft.fieldtemplateid
left outer join reportheader rh
on rh.reportid = rf.reportid
where rv.viewdescription = 'ATHLETEFACT_BC'
and rv2.viewname = 'New View'
and reportname IS NOT NULL
and rh.reportstatuscode = 'OPEN'
Hi Ingo,
To the best of my knowledge, we do not have a query lying around that accomplishes this. Therefore, we would be starting at the same investigation point as you. Off the top of my head I'm not sure how it would be structured, but I do conjecture that it would likely be a pretty heavy duty query, as it would have to scan a large chunk of the database and be mapped through multiple tables.
If you decide to investigate this further on your own, our usual 'Be careful and make sure you have backups when playing with the configuration database as we can't support something that goes south' disclaimer applies :)
Thanks!
-Conner
Hi Ingo,
To the best of my knowledge, we do not have a query lying around that accomplishes this. Therefore, we would be starting at the same investigation point as you. Off the top of my head I'm not sure how it would be structured, but I do conjecture that it would likely be a pretty heavy duty query, as it would have to scan a large chunk of the database and be mapped through multiple tables.
If you decide to investigate this further on your own, our usual 'Be careful and make sure you have backups when playing with the configuration database as we can't support something that goes south' disclaimer applies :)
Thanks!
-Conner
Here is the query that was created by one of our Yellowfin Consultants to retrieve this information:
Hi Ingo,
You can use this query to find the columns and reports that are using a particular database table.
All you need to do is fill in the filters highlighted in blue. This would be the view name and also the table name alias.
select distinct rv.viewid as 'tableid'
, rv.viewname as 'tablename'
, rv.viewdescription as 'aliastablename'
, rv2.viewname as 'viewname'
, rft.columnname as 'columnname'
, rft.shortdescription as 'aliascolumnname'
, rf.columnheading as 'reportcolumnname'
, rh.reportname as 'reportname'
from reportview rv
left outer join reportview rv2
on rv2.viewid = rv.parentviewid
left outer join ReportFieldTemplate rft
on rft.subviewid = rv.viewid
left outer join reportfield rf
on rf.fieldtemplateid = rft.fieldtemplateid
left outer join reportheader rh
on rh.reportid = rf.reportid
where rv.viewdescription = 'ATHLETEFACT_BC'
and rv2.viewname = 'New View'
and reportname IS NOT NULL
and rh.reportstatuscode = 'OPEN'
Here is the query that was created by one of our Yellowfin Consultants to retrieve this information:
Hi Ingo,
You can use this query to find the columns and reports that are using a particular database table.
All you need to do is fill in the filters highlighted in blue. This would be the view name and also the table name alias.
select distinct rv.viewid as 'tableid'
, rv.viewname as 'tablename'
, rv.viewdescription as 'aliastablename'
, rv2.viewname as 'viewname'
, rft.columnname as 'columnname'
, rft.shortdescription as 'aliascolumnname'
, rf.columnheading as 'reportcolumnname'
, rh.reportname as 'reportname'
from reportview rv
left outer join reportview rv2
on rv2.viewid = rv.parentviewid
left outer join ReportFieldTemplate rft
on rft.subviewid = rv.viewid
left outer join reportfield rf
on rf.fieldtemplateid = rft.fieldtemplateid
left outer join reportheader rh
on rh.reportid = rf.reportid
where rv.viewdescription = 'ATHLETEFACT_BC'
and rv2.viewname = 'New View'
and reportname IS NOT NULL
and rh.reportstatuscode = 'OPEN'
Replies have been locked on this page!