Extracting Report URL from ReportServer database in SSRS
I was working on a report for monitoring the usage of reports on a Report Server instance. As part of that, I wanted to display a list of reports with values for various metrics along with few additional links under each of the reports. One of the links was to Go to the Report in question, for which I would require the exact URL of the report that is hosted on Report Server. After doing some research on the data available in the ReportServer database I found an approach to get the URL.
There are various useful tables in the ReportServer database of which, there is a table called “Catalog” which stores the list of all the reports, data sources etc. which are hosted on the Report Server. The table also contains various properties for each of these objects like Object Id, Object Name, Path, Parent Id etc.
I used the following simple query to extract the Report URL.
DECLARE @BaseReportURL VARCHAR(512) = ‘http://<<servername>>:<<portnumber>>/Reports<<_InstanceName>>/Pages/Report.aspx?ItemPath=’
DECLARE @ReplacementForSlash VARCHAR(10) = ‘%2f’
DECLARE @ReplacementForSpace VARCHAR(10) = ‘+’
Name AS ReportName
, @BaseReportURL + REPLACE(REPLACE([Path],’/’,@ReplacementForSlash),’ ‘,@ReplacementForSpace)AS ReportURL
In the above query, BaseReportURL remains the same for all the reports on a particular instance of Reporting Services. Path column in the Catalog table contains the full path of the report like “/MSBI Demo/Sample Reports Folder/SSRS Cummulative Totals“. To get the Report URL, I replaced the “/” symbol with “%2f” and space with “+” in the Path field and then concatenate it with the BaseReportURL.
Let us take a look at the following example.
The output of the above example is as shown below, which is exactly the same URL which I got when I tried to hit the report by name “SSRS Cummulative Totals” on the specified instance of Report Server.
Note: Above demonstration is based on a named instance of SQL Server 2008 R2 with a standalone installation in Native Mode.