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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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 long time..

    scorpITs | http://scorpITs.blogspot.com


    • Edited by youwanafly Monday, June 4, 2012 10:26 AM
    Monday, June 4, 2012 10:26 AM

Answers

  • Question
    Sign in to vote
    11
    Sign in to vote

    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.

    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)

    • Proposed as answer by Sanjeewan Kumar Monday, June 4, 2012 11:47 AM
    • Marked as answer by youwanafly Tuesday, June 5, 2012 6:30 AM
    Monday, June 4, 2012 11:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

     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 youwanafly Tuesday, June 5, 2012 6:47 AM
    Monday, June 4, 2012 11:02 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

     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 youwanafly Tuesday, June 5, 2012 6:47 AM
    Monday, June 4, 2012 11:02 AM
  • Question
    Sign in to vote
    11
    Sign in to vote

    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.

    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)

    • Proposed as answer by Sanjeewan Kumar Monday, June 4, 2012 11:47 AM
    • Marked as answer by youwanafly Tuesday, June 5, 2012 6:30 AM
    Monday, June 4, 2012 11:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Sandip,
    getting error:
    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'dbo.Catalog'.

    scorpITs | http://scorpITs.blogspot.com

    Monday, June 4, 2012 12:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.

    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)


    Monday, June 4, 2012 2:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.

    scorpITs | http://scorpITs.blogspot.com


    • Edited by youwanafly Wednesday, October 8, 2014 12:52 PM to hide the organization name on snapshot
    Tuesday, June 5, 2012 5:39 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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 rocking..

    scorpITs | http://scorpITs.blogspot.com



    • Edited by youwanafly Tuesday, June 5, 2012 6:49 AM
    Tuesday, June 5, 2012 6:30 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Wednesday, June 5, 2013 7:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    How can i get the query out of the datasettings field for data driven subscription?
    Wednesday, October 8, 2014 12:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Sandip,

    This query is very helpful. Thanks for providing.

    Thanks,

    Vivek Singh


    Tuesday, October 21, 2014 5:39 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Monday, September 7, 2015 3:16 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ,

    How to get the BCC and and scheduled info(attached image)

    Starting from and time 


    Tuesday, September 22, 2015 6:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Salmanpc,

    In Reporting Services, there are two type tasks manage subscriptions: “Manage All Subscriptions” and “Manage Individual Subscriptions”. When you subscribe to a report, the e-mail delivery settings you work vary depending on the role assigned with the manage subscription task.
    When the role includes the “Manage Individual Subscriptions” task, fields that enable a user to automate and deliver a report to himself or herself is available. In this mode, fields that accept e-mail aliases are not available.

    If you want to show fields that support wider distribution, including To:, Cc:, Bcc:, and Reply-To: fields, please try to change the task type in your role assignment.

    For more information, please see:

    E-Mail Delivery in Reporting Services
    Predefined Roles

    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Tuesday, September 22, 2015 6:37 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    How to get "Next Run Time" and "Next Run Date" together in single field.

    Thanks,

    Salman

    Monday, September 28, 2015 8:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Folks,

    Can we include interval information too in report? I mean daily, weekly, monthly etc with each job in report. 


    • Edited by Prajiit Thursday, May 3, 2018 6:06 PM
    Thursday, May 3, 2018 5:03 PM