How to show list of all reports Hyperlinks inside a report to click through each report inside the report server.

Problem: How to show list of all reports inside a report to click through each report inside the report server, basically it’s like a site map which navigates to all the reports on the server.

Solution: SSRS Report Server Catalog Tables

If you want to show all the reports that are available on the report server in a single report that has hyperlinks to click and go to the specific report.

Inside report server tables, catalog table has all the list of reports, path of the reports and report definition details.

Example:

In this article, I am going to show we can create a query to get the report path URL and create a link to click on the URL.

Step 1: Create a dataset to show the URL and I had to hard code the base URL to concatenate the path links.

DECLARE @BaseReportURL VARCHAR(512) = 'http://troppltmsambu/Reports_MSSQLSERVER2014/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]

--WHERE name='Charts'

Step 2: Create a Report using the above data set and fill the table with the columns as below.

Make sure to connect to the report server database in the data source section. 

Step 3: Go to the text box of Report URL and click on the action section from the dialog and select the options Go to URL and from the drop down select Report URL field and to add color and underline, go to the font section and choose the color and effects to underline.

OUTPUT:

When you go to the preview this would show up like below.