SQLTeam.com | Weblogs | Forums

Find data source of created SSRS report

Hello,

So I have some reports created in SSRS that runs on a daily basis. It has been there for a while and running. I like to know how can I find the data source of these reports without jeopardizing them and cause them not to run.

Data source such as the database name and is it from SQL server or from dynamics 365, how would I do that (find the data source these reports comes from) ?

Please advise !!

open the report in report builder and you can find the datasource in there.

where is report builder though. I see Microsoft SQL server management studio I see a bunch of database, no report builder.

Is report builder in visual studio ?
I see reporting service configuration manager, is this report builder ?

Download report builder from online. Then download the report itself from report server. Then edit using report builder

Ok how or where do we schedule the reports to run each morning ?

By scheduling you mean email it as subscription?

https://www.google.com/url?sa=t&source=web&rct=j&url=https://docs.microsoft.com/en-us/sql/reporting-services/subscriptions/subscriptions-and-delivery-reporting-services&ved=2ahUKEwif8oqJxJnlAhVHIjQIHcYRC0oQFjABegQIDhAJ&usg=AOvVaw1O5pCqmgvMaq130LUPQtmV

That sounds about right.
It’s there any other way to schedule to send the report ?

you could write your own chron job that hits the report URL, download the file and email it.
SSIS or powershell come to mind.

but why reinvent the wheel? maybe @BabyAqua can give you some feedback on that. He/She wants to contribute more to this forum

Johnse,

First, you need to find where your report database is located on which server. The database is called "ReportServer". If you don't know how to track that, let me know but for a start, the URL of the report is the DNS (CName) which your network admin can tell you the location of the server. I believe you can also use "Ping -a TheURL" in a cmd to give you the IP Address too.

Second, the major table that holds all of the reports is called: ReportServer.dbo.[Catalog]

Many tables tie to this Catalog table, including a subscription table (for email and scheduling). There is also a Datasource table as well.

This database looks simple but it is not because more data are hidden deeply in those XML fields. Reading is the easy part. However, I recommend against making updates against the XML because it is super tricky to tie it back to the tables.

1 Like

Just thought I'd chime in here. You can download the RDL file from the report server (if you can get to the SSRS Report Manager) and then just open the downloaded copy with Notepad and you can do a search for data source. It is one large XML file so pretty easy to locate.

1 Like