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) = '+'

SELECT

Name AS ReportName

, @BaseReportURL + REPLACE(REPLACE([Path],'/',@ReplacementForSlash),' ',@ReplacementForSpace)AS ReportURL

FROM [dbo].[Catalog]

GO

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.

http://sindol:8080/Reports_MSSQLServer2K8R2/Pages/Report.aspx?ItemPath=%2fMSBI+Demo%2fSample+Reports+Folder%2fSSRS+Cummulative+Totals

Note: Above demonstration is based on a named instance of SQL Server 2008 R2 with a standalone installation in Native Mode.

Readers Feedback:

Did you like this article? If Yes, then please share it: | More Options

Did you like my blog? If Yes, then please help spread the word by sharing this blog with your colleagues, friends, & anyone else in the MSBI Space: Like us on Facebook , Follow us on Twitter .