I needed a way for me and my teammates to see and edit all report subscriptions across a Sharepoint site
with SQL Reporting Services in Integrated mode.
There are two parts here; the first is a function that returns text between delimiters. This is important to get
the site and folder from the ItemPath in the ReportServer’s Catalog table.
This function takes a string, delimiter and position. For example
dbo.fn_ParseStr(‘Server/SubSite/Folder’,’/’,2)
Will Return the SubSite as this is the 2nd item delimited.
dbo.fn_ParseStr(‘Server/SubSite/Folder’,’/’,3)
Will Return the Folder as it is the 3rd item delimited.
create Function dbo.fn_ParseStr(@Message Varchar(1000), @delimiter char(1), @index int )
Returns Varchar(1000)
As
Begin
Declare
@curIndex int = 0,
@pos int = 1,
@prevPos int = 0,
@result varchar(1000)
while @pos > 0
Begin
set @pos = CHARINDEX(@delimiter, @Message, @prevPos);
if(@pos > 0)
begin-- get the chars between the prev position to next delimiter pos
set @result = SUBSTRING(@message, @prevPos, @pos-@prevPos)
end
else
begin--get last delim message
set @result = SUBSTRING(@message, @prevPos, LEN(@message))
end
if(@index = @curIndex)
begin
return @result
end
set @prevPos = @pos + 1
set @curIndex = @curIndex + 1;
end
return ''--not found
End
Here is the query to return the fields for the report and also builds the link to edit the subscription.
USE [ReportServer]
GO
SELECT
distinct(S.[SubscriptionID]),
S.[ModifiedDate],
S.[Description],
S.[LastStatus],
S.[LastRunTime],
C.ItemID as CatalogID,
--- This substring removes the GUID from the Path in the Catalog table.
substring(C.[Path], 40, 255) as Path ,
dbo.fn_ParseStr(C.[Path],'/',2) as Site,
dbo.fn_ParseStr(C.[Path],'/',3) as Folder,
C.[Name],
[U1].[UserName] AS [Owner],
[URL2] = 'https://yourservername/'+ dbo.fn_ParseStr(C.[Path],'/',2) +
'/_layouts/ReportServer/SPSubscriptionProperties.aspx?SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(80)) +
'&list=' + CAST(F.[tp_ID] AS VARCHAR(80)) + '&ID=' + CAST(D.[DoclibRowId] AS VARCHAR(80))
FROM
ReportServer.dbo.[Subscriptions] S
JOIN ReportServer.dbo.[Catalog] C ON S.[Report_OID] = C.[ItemID]
JOIN ReportServer.dbo.[Users] [U1] ON S.[OwnerID] = [U1].[UserID]
-- Your Sharepoint Content Database
JOIN WSS_Content_.dbo.AllDocs D on D.Id = C.[ItemID]
JOIN WSS_Content_.dbo.Lists F on F.tp_ID = D.ListId
