Parse Path from Filename in the Catalog.Path table in Report Server.

Leave a comment

On my trials and tribs trying to develop a report to display SQL Report Server execution data (from a SRS server in Sharepoint integration mode) I found a way to parse the pathname from the Catalog.Path column in the Catalog table. (see below)

 

I left the stages of the parse routine commented out for your own research. To fill in the variables query your report server’s catalog table. There you should find valid path names and to query against. Heck, also comment out the @Report and @User variables unless you also plan to filter

by those two criteria.

Clean formatting 🙂

 

Messy Script

DECLARE @Report VARCHAR(50) = ‘Adhoc Analysis Report.rdl’
DECLARE @User VARCHAR(50) = ‘CORP\sa_aforney’
DECLARE @Path VARCHAR(50) = ‘/LIMS/Reports/Job Logs/’

SELECT ExecutionLogStorage.ReportID
,ExecutionLogStorage.UserName
,ExecutionLogStorage.TimeStart
,ExecutionLogStorage.TimeEnd
,ExecutionLogStorage.TimeDataRetrieval
,ExecutionLogStorage.TimeProcessing
,ExecutionLogStorage.TimeRendering
,[Catalog].ItemID
,[Catalog].[Path]
–,SUBSTRING([Catalog].[Path], 40, 100) AS [1]
–,REVERSE(SUBSTRING([Catalog].[Path], 40, 100)) AS [2]
–,CHARINDEX(‘/’, REVERSE(SUBSTRING([Catalog].[Path], 40, 100))) AS [3]
–,SUBSTRING(REVERSE(SUBSTRING([Catalog].[Path], 40, 100)),CHARINDEX(‘/’, REVERSE(SUBSTRING([Catalog].[Path], 40, 100))),100) AS [4]
,REVERSE(SUBSTRING(REVERSE(SUBSTRING([Catalog].[Path], 40, 100)),CHARINDEX(‘/’, REVERSE(SUBSTRING([Catalog].[Path], 40, 100))),100)) AS [AbrvPath]
,LEN([Catalog].[Path])
,[Catalog].Name
,[Catalog].ParentID
,[Catalog].Type
,[Catalog].Description
FROM ExecutionLogStorage
INNER JOIN [Catalog]
ON ExecutionLogStorage.ReportID = [Catalog].ItemID
WHERE [Catalog].Name <> ‘ ‘
–AND [Catalog].Name IN ( @Report )
AND ExecutionLogStorage.UserName IN ( @User )
AND REVERSE(SUBSTRING(REVERSE(SUBSTRING([Catalog].[Path], 40, 100)),CHARINDEX(‘/’, REVERSE(SUBSTRING([Catalog].[Path], 40, 100))),100)) IN (@Path)
ORDER BY ExecutionLogStorage.TimeStart DESC

Advertisements

Sharepoint 2010 SSRS URL’s for ASP.Net Reportviewer control

Leave a comment

So, you need to figure out the SQL Report Server URL parameters to pass to your ASP.Net reportviewer control and your Report Server is running in SharePoint mode?

After a lot of trial and error I came across this fix.

ASP.Net Reportviewer control properties

 

 

 

 

 

 

 

In order to connect to the report server you’ll need to add the following suffix to your Report Viewer properties > Report Server URL . . _vti_bin/ReportServer.

Example: http://siteurl/site/_vti_bin/ReportServer

You’ll also need the path to the report including the reportname.rdl

Example: http://siteurl/site/library/reportname.rdl

*If you have the proper permissions you can grab this path via the “Open with Explorer window”  (in Library Tools>Library) and simply add the /reportname.rdl 

Hope this helps someone out . . 🙂

%d bloggers like this: