Find all reports based on a certain table in DWH

Ingo Klose shared this question 23 months ago
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

Best Answer
photo

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'

Comments (2)

photo
1

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

photo
1

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'