Find all SSRS reports using the same shared dataset

0
votes

I have been trying to find out what (if anything) is using a particular database table, so I can update it.
I have found a stored procedure accessing this table that is used by a SSRS instance for a shared dataset. I have a copy of the SSRS solution and when I open it in VS2008 I can see the dataset. There are also 18 reports in this solution. When I right-click and select find usages on any of the shared datasets it just says "Usages of blah.rsd was not found".
I can individually open each Report and check the datasets on each report to see if they match the shared dataset, but that seems ridiculous.

Surely there is an easier way to find usages of the shared dateset?

2
visual-studio-2008reporting-servicesssrs-2008bids

2 Answers

2
votes

The query below displays all reports that are in the same project and using the shared dataset. For explanation of the type column in the Catalog, see this link from the msdn forums where they also unhelpfully mention querying the database directly is not supported...

select c.Name as ReportName, c.Path 
  from Catalog c
  join DataSets ds on c.ItemID = ds.ItemID                 --all reports datasets (including those created from shared datasets)
  join Catalog c2 on ds.LinkID = c2.ItemID and c2.Type = 8 --all shared datasets
  join Catalog c3 on c.ParentID = c3.ItemID                --the project dataset object
  join Catalog c4 on c3.ParentID = c4.ItemID               --the project object
 where c2.Name = 'MySharedDatasetName' and c4.Name = 'MyProjectName'

Thanks to s_f for leading me in the right direction!

1
votes

you can run this query on ReportServer DB:

select c.Name, c.Path 
from dbo.DataSets ds
inner join dbo.Catalog c
    on ds.ItemID = c.ItemID
where ds.Name like 'yoursDSname'