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?
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.
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.
Thanks for the reply and great information.
I found where the report is location. I went to search and type report and open up reporting services configuration manager and there is opens up SQL server.
I connect and login and in the report manager URL in there I can find the data source and reports from report builder.
However I did come across another issue. The issue is that when I schedule the report to email it doesn’t and gets an error message “THE EMAIL ADDRESS OF ONE OR MORE RECIPIENTS IS NOT VALID”.
Not sure how to fix this.
Please mark the answer that you feel gave you the correct direction a d mark it closed. The email issue is here
To find the data source of your SSRS reports without affecting their functionality, you can follow these steps:
1. Open the report in SQL Server Data Tools (SSDT) or Report Builder.
2. In the Report Data pane, right-click the dataset and select Dataset Properties.
3. In the Dataset Properties dialog box, click the Connection Properties button.
4. The Connection Properties dialog box shows the connection information used by the report. It will show the server name and database name used for the data source. You can also check if the data source is SQL Server or Dynamics 365.
5. If you need more details about the data source, such as the login credentials or connection string, click the Edit button to view or modify the connection properties.
6. Once you have checked the data source information, click Cancel to close the Connection Properties dialog box and Dataset Properties dialog box.