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>&gt;:<<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.

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on February 6, 2012, in SSRS. Bookmark the permalink. 1 Comment.

  1. This comment has been removed by a blog administrator.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s