Details of Reports scheduled on SSRS (Report name, userdetails, frequency, etc)

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
June 4th, 2012 10:26am

 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 



  • Marked as answer by ucrajee Tuesday, June 05, 2012 6:47 AM
Free Windows Admin Tool Kit Click here and download it now
June 4th, 2012 11:02am

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.

c.Name AS ReportName,
'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
'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))
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]
 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) =
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

Let me know if you have any queries.

June 4th, 2012 11:32am

Hi Sandip,
getting error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Catalog'.
Free Windows Admin Tool Kit Click here and download it now
June 4th, 2012 12:02pm


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.

June 4th, 2012 2:01pm

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.

Free Windows Admin Tool Kit Click here and download it now
June 5th, 2012 5:39am

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
June 5th, 2012 6:30am

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.



Free Windows Admin Tool Kit Click here and download it now
June 5th, 2013 7:59pm

How can i get the query out of the datasettings field for data driven subscription?
October 8th, 2014 12:44pm

Hi Sandip,

This query is very helpful. Thanks for providing.


Vivek Singh

Free Windows Admin Tool Kit Click here and download it now
October 21st, 2014 5:39am

September 6th, 2015 11:17pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics