How To Get All Details of Scheduled Reports on The SSRS Server using SQL?

-
A few friends have been looking at a few methods to get all the details of all scheduled reports on the SSRS Server on an on-prem deployment.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. 
Please Note: 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

Comments

Post a Comment

Popular posts from this blog

How to Query Microsoft Dataverse Data using SQL from Power BI or SSMS

-
Image
Microsoft in recent months made available a much requested feature by many with the additional way to access read-only data in Microsoft Dataverse. The TDS endpoint for Microsoft Dataverse allows read-only access to Dataverse for reporting and analytics requirements. The Tabular Data Stream (TDS) Protocol endpoint functionality is managed by an administrator with access to the Admin center. This feature becomes a game changer as many Microsoft customers who required this feature and often went down the root of the Data Export Service can now start exploring this capability Prerequisites: To get started using this feature, you will need to ensure that TDS endpoint is enabled as this is required to be able to use this feature via Power BI or SSMS. Sign into the Power Platform Admin Center :( https://admin.powerplatform.microsoft.com/environments ), and then select the appropriate sandbox environment. Note: Its always good practice to test new features in a Sandbox Environment Validate t
Read more

Users Unable to See Flow Button and run On Demand workflows in Dynamics 365 CE

-
Image
Overview. Dynamics 365 CE provides functionality to automate business processes in the background or on a click of a button requiring a user to initiate this automation. In this blog post the scenario is focused on an issue a user may experience while using dynamics 365 CE on automation which is initiated by a user looking to use an On Demand. This automation triggered by a user is called an on-demand workflow. Problem When a user goes into Dynamics 365,  they are unable to see or run a workflow/flow which may have been created. So how do you resolve this issue? Steps to Resolve System Settings 1.a) Login into the Power Platform Admin center ( https://admin.powerplatform.microsoft.com/environments ) and Select your Environment name > Settings  > Behavior . At this point validate that the " Show Power Automate on forms and in the site map " is toggled  1. b) Validate that the setting, you will need to navigate to Settings > Administration > System Settings >
Read more

How To Deploy Dynamics App For Outlook (On-Premise)

-
Purpose The purpose of this post is to set out the direction, steps,scope, and implementation approach for the Dynamics Outlook App configuration item Audience This post is intended for Technical Teams/Administrators looking to implement the Dynamics Outlook App for On-premise environments. Pre-Requisites Dynamics 365 with IFD Configuration Dynamics 365 Update 8.2.2.112 (KB4046795) Minimum ADFS requirement ADFS 3 upwards due to OAuth endpoint Minimum Exchange 2016 or 2013 with Cumulative Update 14 or later Office 2016 *Recommended, Office 2013 *Compatible Supported browsers for Outlook on the web Dynamics 365 App for Outlook can be used with Outlook on the web on the following browsers: Internet Explorer 10, Internet Explorer 11, or Microsoft Edge The following configuration is supported: Protected Mode is enabled for Internet security zone. To enable Protected Mode: in IE 10 or 11, go to Tools > Internet options > Security tab > Internet. Protected Mode
Read more