Grabs information about what reports in SSRS are scheduled, when are they due to be sent out, report parameters and any errors in the last run etc.
Please note that the XPath on the [Parameters] column varies based on what parameters are setup in your own report. Please adjust them for your own purpose. Also the 'Prompt' value for a parameter is not stored, so you might have to join back to the relevant table for a friendly name.
Also there is additional info in the [ExtensionSetting].
SELECT
[Locale],
[InactiveFlags],
'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="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]
,[ModifiedDate]
,S.[Description]
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM [ReportServer$LIVE].[dbo].[Subscriptions] S
INNER JOIN ReportServer$LIVE.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
Easily monitoring your SQL Agent job history using SSRS for concurrency, job lenght and job status.
2013-09-05
9,613 reads
Report parameters assist in narrowing down a report for better analysis.
2008-01-17
2,614 reads
You can build some very complex reports in Reporting Services and longtime author Raj Vasant brings us a short tutorial on how you can implement internal navigation structures in your reports.
2008-01-10
4,894 reads
Gantt charts in SSRS aren’t included in the 2005 release. You can purchase a third-party add-on or, if you can wait, these powerful project tools might make it in the 2008 release. Alternatively, you can do this now with the native RS Chart control by using the methods David Leibowitz provides.
2007-12-21
12,785 reads
In SQL Reporting Services, the native Matrix control provides a crosstab view of data, similar in behavior to a PivotTable in MS Excel. Rows and columns will have intersecting points of data which is often useful in time based reporting . David Leibowitz shows you how...
2007-12-04
2,768 reads