Find a Report By Name

It’s been a while since the last post. But the blog is not dead, I keep working with HR.Net and when I find good enough reason to post here, I do.

Here is the new gem I had to discover. In attempt to delete an unused column from an unused table, I was present with dialog saying “Can’t delete field, it is used in a report”. Good on Vizual that they are showing reports that depend on fields. But how they do it:

2014-10-20 00_08_50-Ts - ts.cornerstone.org.uk - Remote Desktop Connection

Yep, thanks. This field is used in report “Test Blah”. But where the hell is this report? In the system I support just now there are over a thousand of reports with about a hundred of folders.

So this script will help you show the location of a report in your system:

select 
    pppf.foldername as [Folder],
    ppf.foldername as [Folder],
    pf.foldername as [Folder],
    f.foldername as [Folder],
    d.*
from OC_DOCUMENTS d
	left join dbo.OC_DOCUMENTFOLDERS f on d.folderid = f.folderid
	left join dbo.OC_DOCUMENTFOLDERS pf on f.PARENTFOLDERID = pf.folderid
	left join dbo.OC_DOCUMENTFOLDERS ppf on pf.PARENTFOLDERID = ppf.folderid
	left join dbo.OC_DOCUMENTFOLDERS pppf on ppf.PARENTFOLDERID = pppf.folderid
where d.DOCUMENTNAME = 'Test Blah'
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *