Hi all,

Is there a way to find all SSRS scheduled reports details such as (Report Name, schedule time, mail IDs configured, etc.)?
Please help as we have many SCCM reports on SRS, and manual work will take very l

 Hi ucrajee,

You can query ReportServer database to get the all report details. You can customize the below query as per your need.

select c.Name As ReportName,sub.ExtensionSettings,s.StartDate,s.DaysofWeek,s.weeksInterval 
from reportschedule rs
join schedule s on rs.ScheduleID=s.scheduleID
join subscriptions sub on rs.SubscriptionID=sub.SubscriptionID
JOin catalog c on rs.reportID=c.ItemID 

Thanks,

Saikat

• Marked as answer by ucrajee Tuesday, June 05, 2012 6:47 AM

Hey ucrajee,

Here is simple SQL query which will give you details on report subscription along with Subscription type, render format, next run date, time, email ID configured with subscription etc. 

You need to run this SQL Query on your report server database.

SELECT
c.Name AS ReportName,
'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,'NA'),
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="CC"])[1]','nvarchar(50)') as [CC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]
---Example report parameters: StartDateMacro, EndDateMacro & Currency.
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM 
 dbo.[Catalog] c
INNER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID
INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

Let me know if you have any queries.

Hi Sandip,
getting error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Catalog'.

ucrajee,

You need to execute execute provided SQL Query on your ReportServer Database. for your reference please find below image.

Which SQL Reporting Server version you are using?

Let me know if you still have any issues with this.

Hi Sandip,

Thanks for the info. Now its not giving error, but does not fetches anything except headers.
We are running 'Microsoft SQL Server 2005'. PFB the snapshot.

Hi Sandip/Saikat,
I logged in my SRS server , and ran the report from its SQL, and it works when i chose SCCMSRS Database..
It WORKS great and i found the required tables under Report Server, SRS, SCCMSRS databases, so that i can edit according to the requirement.
Thanks a lot mates. You're

Sandip, This code was very helpful to me as well. Thank you very much. I have one further query for you.

I am using your code to provide details on a report that has 60 subscriptions bast on a different parameter selection. I am building a report so that I can tell the subscription details apart but I was wondering if there was a way to link the report back to the subscription properties page.

Basically there is an 'application' parameter and I want to be able to click the record and take back to the properties page for that 'application'. Kind of a way to audit subscriptions.

Thanks,

Clinton

How can i get the query out of the datasettings field for data driven subscription?

Hi Sandip,

This query is very helpful. Thanks for providing.

Thanks,

Vivek Singh

Thanks