CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2012
papermoon88
papermoon88

Reputation: 476

SSRS Using a report catalog report to hyperlink to other reports on report manager

I have a query that uses the ReportServer database to retrieve information about available reports on the server. I created a "report catalog" with a standard table (no grouping) that lists all the individual reports, their folders, and number of times each report has been ran. Is there a way to hyperlink each report name to take users to those reports?

Here is the query if it helps:

SELECT  
c.Name,
REPLACE(c.[Path], c.Name, '') as Path,
COUNT(*) as TimesRun
FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) el
INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) c ON el.ReportID = c.ItemID
WHERE c.Type = 2
GROUP BY c.Name,c.[Path]
order by TimesRun desc

Upvotes: 0

Views: 365

Answers (1)

papermoon88
papermoon88

Reputation: 476

I created "ReportURL" column by concatenating some URL text (like "http://") with the existing columns. The URL also replaced all spaces with "%20", so I did the same in the query. In my SSRS table, on the textbox properties , I set action to Go to URL and used expression =Fields!ReportURL.Value and it seemed to do the trick.

SELECT  
c.Name,
REPLACE(c.[Path], c.Name, '') as Path,
   REPLACE(('http://'+
   LEFT(el.InstanceName, CHARINDEX('\', el.InstanceName, CHARINDEX('\', el.InstanceName)) - 1)+ 
   '/reports/report'+
   c.Path),' ','%20')
   as ReportURL,
COUNT(*) as TimesRun
FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) el
INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) c ON el.ReportID = c.ItemID
WHERE c.Type = 2
GROUP BY c.Name,c.[Path], el.InstanceName
order by TimesRun desc

Upvotes: 0

Related Questions