List all Reports with Shared Data Source

[Steffi1013]

Is there an easy way (or not so easy way) to get a list of all reports that use a shared data source and the name of the data source?

 

[vbajock]

I linked the Catalog and Datasource tables in the ReportServer sql db to an Access DB via ODBC, and then ran this query:

SELECT Catalog.Name AS ReportName, DataSource.Name AS DataSourceName
FROM [Catalog] INNER JOIN DataSource ON Catalog.ItemID = DataSource.ItemID

 

[Steffi1013]

This doesn't show if the report is using shared datasource. It shows the name of the datasource that was used when creating the report in VS.

What I am trying to figure out is if there is a way to see if the Report is using a shared datasource (the first option under data sources in Report Manager)

 

[xlbo]

You should be able to see form report manager, the list of reports that use any given data source. the data source tab should have a further option of "reports" which will give you a list of all the reports that use that particular data source

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Steffi1013]

Perfect! That will give me what I am looking for -- I was hoping to be able to write a query that would give me the same thing, but this will definitely work.

Thanks!

 

[xlbo]

np - I'm surprised that the query route doesn't work though - I would expect it to

VBAJock - just so you know, it is recommended to NOT use the "live" reportserver db. There is a reportserver TEMP db that should be created as part of install that has the major components used for meta reporting. If that doesn't cut the mustard MS suggest doing a copy of the live db to a new instance nightly rather than run over the live db as apparently "it's a bit unstable" - I guess there could well be an issue with table / row locks etc when reports are being run as that is the database used to serve up the report configuration when requested by a user...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question