A list of reporting services reports that have subscriptions SSDTRS 17.5.33414.496 SSDTRS 17.8.34408.163 2024-01-11T03:15:44.5454464Z Anthony Duguid 0 ReportServer None 9c5d5e18-ddee-41b3-ae49-8d83f261caf6 ReportServer =Parameters!service_account.Value =Parameters!ReportServerUrl.Value =Parameters!CatalogType.Value =Parameters!all_value.Value =Parameters!DeliveryExtension.Value =Parameters!SubscriptionType.Value =Parameters!ReportFolder.Value =Parameters!ReportName.Value =Parameters!StatusGroup.Value =Parameters!EventStatus.Value =Parameters!SubscriptionStatus.Value =Parameters!LastStatus.Value =Parameters!IsDisabled.Value =Parameters!IsServiceAccount.Value =Parameters!StartDateTime.Value =Parameters!EndDateTime.Value =Parameters!EmailLike.Value WITH report_status AS ( SELECT tbl.* FROM (VALUES ( 'rrRenderingError', 'Failure') , ( 'rsHttpRuntimeClientDisconnectionError', 'Failure') , ( 'rsInternalError', 'Failure') , ( 'rsInvalidDataSourceCredentialSetting', 'Failure') , ( 'rsProcessingAborted', 'Failure') , ( 'rsProcessingError', 'Failure') , ( 'rsRenderingExtensionNotFound', 'Failure') , ( 'rsReportServerDatabaseError', 'Failure') , ( 'rsSuccess', 'Success') ) tbl ([StatusName], [StatusGroup]) ) , service_account AS ( SELECT [UserID] , [UserName] FROM [dbo].[Users] WITH(NOLOCK) WHERE [UserName] = @service_account ) , report_users AS ( SELECT [UserID] , [UserName] , [SimpleUserName] = LOWER(RIGHT([UserName], (LEN([UserName]) - CHARINDEX('\', [UserName])))) FROM [dbo].[Users] WITH(NOLOCK) ) , report_catalog AS ( SELECT c.[ItemID] , c.[CreatedById] , c.[ModifiedById] , c.[Type] , c.[Name] , c.[Description] , c.[Parameter] , [ReportCreationDate] = c.[CreationDate] , [ReportModifiedDate] = c.[ModifiedDate] , [ReportFolder] = CASE WHEN c.Path = '/' + c.Name THEN '' ELSE SUBSTRING(c.[Path], 2, Len(c.[Path])-Len(c.[Name])-2) END , [ReportPath] = c.[Path] , [UrlPath] = @ReportServerUrl + 'Reports/Pages/Folder.aspx?ItemPath=%2f' , [ReportDefinition] = CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), c.[content])) FROM [dbo].[Catalog] AS c WITH (NOLOCK) WHERE 1=1 AND c.[Type] IN(@CatalogType) ) , subscription_days AS ( SELECT tbl.* FROM (VALUES ( 'DaysOfMonth', 1, '1') , ( 'DaysOfMonth', 2, '2') , ( 'DaysOfMonth', 4, '3') , ( 'DaysOfMonth', 8, '4') , ( 'DaysOfMonth', 16, '5') , ( 'DaysOfMonth', 32, '6') , ( 'DaysOfMonth', 64, '7') , ( 'DaysOfMonth', 128, '8') , ( 'DaysOfMonth', 256, '9') , ( 'DaysOfMonth', 512, '10') , ( 'DaysOfMonth', 1024, '11') , ( 'DaysOfMonth', 2048, '12') , ( 'DaysOfMonth', 4096, '13') , ( 'DaysOfMonth', 8192, '14') , ( 'DaysOfMonth', 16384, '15') , ( 'DaysOfMonth', 32768, '16') , ( 'DaysOfMonth', 65536, '17') , ( 'DaysOfMonth', 131072, '18') , ( 'DaysOfMonth', 262144, '19') , ( 'DaysOfMonth', 524288, '20') , ( 'DaysOfMonth', 1048576, '21') , ( 'DaysOfMonth', 2097152, '22') , ( 'DaysOfMonth', 4194304, '23') , ( 'DaysOfMonth', 8388608, '24') , ( 'DaysOfMonth', 16777216, '25') , ( 'DaysOfMonth', 33554432, '26') , ( 'DaysOfMonth', 67108864, '27') , ( 'DaysOfMonth', 134217728, '28') , ( 'DaysOfMonth', 268435456, '29') , ( 'DaysOfMonth', 536870912, '30') , ( 'DaysOfMonth', 1073741824, '31') , ( 'DaysOfMonth', 8193, '1st and 14th') , ( 'DaysOfWeek', 1, 'Sun') , ( 'DaysOfWeek', 2, 'Mon') , ( 'DaysOfWeek', 4, 'Tues') , ( 'DaysOfWeek', 8, 'Wed') , ( 'DaysOfWeek', 16, 'Thurs') , ( 'DaysOfWeek', 32, 'Fri') , ( 'DaysOfWeek', 64, 'Sat') , ( 'DaysOfWeek', 62, 'Mon - Fri') , ( 'DaysOfWeek', 10, 'Mon - Wed') , ( 'DaysOfWeek', 24, 'Wed - Thurs') , ( 'DaysOfWeek', 120, 'Wed - Sat') , ( 'DaysOfWeek', 126, 'Mon - Sat') , ( 'DaysOfWeek', 127, 'Daily') , ( 'DayOfWeek', 1, 'Sun') , ( 'DayOfWeek', 127, 'Sun') , ( 'DayOfWeek', 2, 'Mon') , ( 'DayOfWeek', 10, 'Mon') , ( 'DayOfWeek', 62, 'Mon') , ( 'DayOfWeek', 126, 'Mon') , ( 'DayOfWeek', 127, 'Mon') , ( 'DayOfWeek', 4, 'Tue') , ( 'DayOfWeek', 10, 'Tue') , ( 'DayOfWeek', 62, 'Tue') , ( 'DayOfWeek', 126, 'Tue') , ( 'DayOfWeek', 127, 'Tue') , ( 'DayOfWeek', 8, 'Wed') , ( 'DayOfWeek', 10, 'Wed') , ( 'DayOfWeek', 24, 'Wed') , ( 'DayOfWeek', 62, 'Wed') , ( 'DayOfWeek', 120, 'Wed') , ( 'DayOfWeek', 126, 'Wed') , ( 'DayOfWeek', 127, 'Wed') , ( 'DayOfWeek', 16, 'Thr') , ( 'DayOfWeek', 24, 'Thr') , ( 'DayOfWeek', 62, 'Thr') , ( 'DayOfWeek', 120, 'Thr') , ( 'DayOfWeek', 126, 'Thr') , ( 'DayOfWeek', 127, 'Thr') , ( 'DayOfWeek', 32, 'Fri') , ( 'DayOfWeek', 62, 'Fri') , ( 'DayOfWeek', 120, 'Fri') , ( 'DayOfWeek', 126, 'Fri') , ( 'DayOfWeek', 127, 'Fri') , ( 'DayOfWeek', 64, 'Sat') , ( 'DayOfWeek', 120, 'Sat') , ( 'DayOfWeek', 126, 'Sat') , ( 'DayOfWeek', 127, 'Sat') ) tbl ([GroupName], [CodeNbr], [Label]) ) , subscription_schedule AS ( SELECT [ScheduleID] , [SchDaySun] = Sun , [SchDayMon] = Mon , [SchDayTue] = Tue , [SchDayWed] = Wed , [SchDayThr] = Thr , [SchDayFri] = Fri , [SchDaySat] = Sat , [ScheduleName] , [ScheduleStartDate] , [ScheduleEndDate] , [Flags] , [RecurrenceType] , [State] , [MinutesInterval] , [DaysInterval] , [WeeksInterval] , [DaysOfWeek] , [DaysOfMonth] , [Month] , [MonthlyWeek] , [ScheduleDays] FROM ( SELECT sc.[ScheduleID] , sd.[CodeNbr] , sd.[Label] , [ScheduleName] = CASE WHEN sc.[EventType] = 'SharedSchedule' THEN sc.[name] ELSE NULL END , [ScheduleStartDate] = sc.[StartDate] , [ScheduleEndDate] = sc.[EndDate] , sc.[Flags] , sc.[RecurrenceType] , sc.[State] , sc.[MinutesInterval] , sc.[DaysInterval] , sc.[WeeksInterval] , sc.[DaysOfWeek] , sc.[DaysOfMonth] , sc.[Month] , sc.[MonthlyWeek] , [ScheduleDays] = CASE WHEN sc.[DaysOfMonth] IS NOT NULL THEN COALESCE(dom.[Label], '(' + CAST(sc.[DaysOfMonth] AS VARCHAR(20)) + ') NOT CODED') WHEN sc.[DaysOfWeek] IS NOT NULL THEN COALESCE(dow.[Label], '(' + CAST(sc.[DaysOfWeek] AS VARCHAR(20)) + ') NOT CODED') END FROM [dbo].[Schedule] sc WITH (NOLOCK) LEFT JOIN subscription_days sd ON sc.[DaysOfWeek] = sd.[CodeNbr] AND sd.[GroupName] = 'DayOfWeek' LEFT JOIN subscription_days AS dom ON sc.[DaysOfMonth] = dom.[CodeNbr] AND dom.[GroupName] = 'DaysOfMonth' LEFT JOIN subscription_days AS dow ON sc.[DaysOfWeek] = dow.[CodeNbr] AND dow.[GroupName] = 'DaysOfWeek' ) sch PIVOT ( COUNT(sch.[Label]) FOR sch.[Label] IN ([Sun], [Mon], [Tue], [Wed], [Thr], [Fri], [Sat]) ) AS pvt ) , report_subscription AS ( SELECT s.[SubscriptionID] , s.[Report_OID] , [SubscriptionDescription] = s.[Description] , s.[ExtensionSettings] , s.[EventType] , s.[OwnerID] , s.[ModifiedByID] , s.[ModifiedDate] , [RunTime] = CONVERT(VARCHAR(5), s.[LastRunTime], 8) , [LastRunDate] = CONVERT(VARCHAR(11), s.[LastRunTime], 13) , [LastRunTime] = CAST(CONVERT(CHAR(16), s.[LastRunTime], 113) AS DATETIME) , [SubscriptionType] = CASE WHEN CAST(s.[DataSettings] AS NVARCHAR(50)) IS NOT NULL THEN 'Data-Driven' ELSE 'Standard' END , s.[DeliveryExtension] , s.[MatchData] , [SubscriptionLastStatus] = s.[LastStatus] , [StatusFail] = CASE WHEN s.[LastStatus] LIKE '%Mail sent%' THEN 'N' ELSE 'Y' END , [EmailSubject] = CASE CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>SUBJECT</Name><Value>') + CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>SUBJECT</Name><Value>') + CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings))) END , [EmailTo] = SUBSTRING(s.ExtensionSettings, LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>TO</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.ExtensionSettings))) , [EmailCc] = CASE CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings))) END , [EmailBcc] = CASE CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings))) END , [EmailComment] = CASE CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>Comment</Name><Value>') + CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>Comment</Name><Value>') + CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings))) END , [EmailIncludeLink] = CASE CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>IncludeLink</Name><Value>') + CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>IncludeLink</Name><Value>') + CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings))) END , [EmailRenderFormat] = CASE CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>RenderFormat</Name><Value>') + CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>RenderFormat</Name><Value>') + CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings))) END , [EmailPriority] = CASE CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>Priority</Name><Value>') + CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>Priority</Name><Value>') + CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings))) END , sch.[MinutesInterval] , sch.[DaysInterval] , sch.[WeeksInterval] , sch.[DaysOfWeek] , sch.[DaysOfMonth] , sch.[Month] , sch.[MonthlyWeek] , sch.[ScheduleName] , sch.[ScheduleDays] , sch.[SchDaySun] , sch.[SchDayMon] , sch.[SchDayTue] , sch.[SchDayWed] , sch.[SchDayThr] , sch.[SchDayFri] , sch.[SchDaySat] , sch.[ScheduleStartDate] , sch.[ScheduleEndDate] , sch.[Flags] , sch.[RecurrenceType] , sch.[State] , s.[LastStatus] , rs.[ScheduleID] --, [IsDisabled] = CASE WHEN s.[LastStatus] = 'Disabled' THEN 1 ELSE 0 END , [IsDisabled] = CASE WHEN s.[InactiveFlags] = 128 THEN 1 ELSE 0 END , [IsServiceAccount] = CASE WHEN sac.[UserID] IS NOT NULL THEN 1 ELSE 0 END FROM [dbo].[Subscriptions] AS s WITH (NOLOCK) LEFT JOIN [dbo].[Notifications] AS n WITH (NOLOCK) ON n.[SubscriptionID] = s.[SubscriptionID] AND s.[Report_OID] = n.[ReportID] LEFT JOIN [dbo].[ReportSchedule] AS rs WITH (NOLOCK) ON s.[SubscriptionID] = rs.[SubscriptionID] LEFT JOIN subscription_schedule AS sch ON rs.[ScheduleID] = sch.[ScheduleID] LEFT JOIN service_account AS sac ON s.[OwnerID] = sac.[UserID] WHERE 1=1 AND (@all_value IN (@DeliveryExtension) OR s.[DeliveryExtension] IN(@DeliveryExtension)) ) SELECT c.[ItemID] , c.[Name] , c.[Description] , c.[Type] , c.[Parameter] , c.[ReportFolder] , c.[ReportPath] , [URL_ReportFolder] = c.[UrlPath] + c.[ReportFolder] + '&ViewMode=List' , [URL_Report] = @ReportServerUrl + 'Reports/report/' + c.[ReportFolder] + '%2f' + c.[Name] , [URL] = @ReportServerUrl + 'Reports/manage/catalogitem/editsubscription' + c.[ReportPath] + '?id=' + CAST(s.[SubscriptionID] AS VARCHAR(80)) , [URL2] = @ReportServerUrl + 'Reports/Pages/Report.aspx?ItemPath=' + c.[ReportPath] + '&SelectedTabId=SubscriptionsTab' , [ReportCreatedBy] = urc.[SimpleUserName] , c.[ReportCreationDate] , [ReportModifiedBy] = urm.[SimpleUserName] , c.[ReportModifiedDate] , [SubscriptionOwner] = usc.[SimpleUserName] , [SubscriptionModifiedBy] = usm.[SimpleUserName] , [SubscriptionModifiedDate] = s.[ModifiedDate] , s.[SubscriptionID] , s.[SubscriptionDescription] , s.[ExtensionSettings] , s.[EventType] , s.[EmailSubject] , s.[EmailTo] , s.[EmailCc] , s.[EmailBcc] , s.[EmailComment] , s.[EmailIncludeLink] , s.[EmailRenderFormat] , s.[EmailPriority] , [DeliveryExtension] = CASE WHEN c.[Type] = 13 THEN 'Scheduled Refresh' ELSE s.[DeliveryExtension] END , [DeliveryExtensionImage] = CASE WHEN s.[DeliveryExtension] = 'Report Server Email' THEN 'email' WHEN s.[DeliveryExtension] = 'Report Server FileShare' THEN 'folder_table' ELSE '' END , s.[SubscriptionType] , s.[SubscriptionLastStatus] , s.[StatusFail] , s.[MatchData] , s.[RunTime] , s.[LastRunDate] , s.[LastRunTime] , s.[MinutesInterval] , s.[DaysInterval] , s.[WeeksInterval] , s.[DaysOfWeek] , s.[DaysOfMonth] , s.[Month] , s.[MonthlyWeek] , [JobName] = NULL , s.[ScheduleName] , s.[ScheduleDays] , s.[SchDaySun] , s.[SchDayMon] , s.[SchDayTue] , s.[SchDayWed] , s.[SchDayThr] , s.[SchDayFri] , s.[SchDaySat] , s.[ScheduleStartDate] , s.[ScheduleEndDate] , s.[Flags] , s.[RecurrenceType] , s.[State] , [EventStatus] = el.[Status] , [EventDateTime] = el.[TimeEnd] , s.[LastStatus] , s.[ScheduleID] FROM report_catalog AS c INNER JOIN report_subscription AS s ON s.[Report_OID] = c.[ItemID] LEFT JOIN ( SELECT b.[ReportID] , b.[Status] , b.[TimeEnd] , [LastRunTime] = CAST(CONVERT(CHAR(16), b.[TimeEnd], 113) AS DATETIME) FROM [dbo].[ExecutionLog] AS b WITH (NOLOCK) INNER JOIN (SELECT [ReportID], MAX([TimeEnd]) AS [TimeEnd] FROM [dbo].[ExecutionLog] WITH (NOLOCK) GROUP BY [ReportID]) a ON b.[ReportID] = a.[ReportID] AND b.[TimeEnd] = a.[TimeEnd] GROUP BY b.[ReportID] , b.[Status] , b.[TimeEnd] ) AS el ON el.[ReportID] = c.[ItemID] LEFT JOIN report_status AS rs ON el.[Status] = rs.[StatusName] LEFT JOIN report_users AS urc ON c.[CreatedById] = urc.[UserID] LEFT JOIN report_users AS urm ON c.[ModifiedById] = urm.[UserID] LEFT JOIN report_users AS usc ON s.[OwnerID] = usc.[UserID] LEFT JOIN report_users AS usm ON s.[ModifiedByID] = usm.[UserID] WHERE 1=1 AND s.[SubscriptionType] IN(@SubscriptionType) AND (@all_value IN (@ReportFolder) OR c.[ReportFolder] IN(@ReportFolder)) AND (@all_value IN(@ReportName) OR c.[Name] IN(@ReportName)) AND (@all_value IN(@StatusGroup) OR rs.[StatusGroup] IN(@StatusGroup)) AND (@all_value IN(@EventStatus) OR el.[Status] IN(@EventStatus)) AND (@all_value IN(@SubscriptionStatus) OR s.[SubscriptionLastStatus] LIKE '%' + @SubscriptionStatus + '%') AND (@all_value IN(@LastStatus) OR s.[LastStatus] IN(@LastStatus)) AND (@IsDisabled IS NULL OR s.[IsDisabled] = @IsDisabled) AND (@IsServiceAccount IS NULL OR s.[IsServiceAccount] = @IsServiceAccount) --AND (@IsDisabled IS NULL OR CASE WHEN s.[ScheduleEndDate] IS NULL THEN 0 WHEN s.[ScheduleEndDate] IS NOT NULL THEN 1 END = @IsDisabled) AND (el.[TimeEnd] >= @StartDateTime OR @StartDateTime IS NULL) AND (el.[TimeEnd] <= @EndDateTime OR @EndDateTime IS NULL) AND ( (SUBSTRING(s.[ExtensionSettings], LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.[ExtensionSettings]), CHARINDEX('</Value>', s.[ExtensionSettings], CHARINDEX('<Name>TO</Name><Value>', s.[ExtensionSettings]) + 1) - (LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.[ExtensionSettings]))) LIKE '%' + @EmailLike + '%' OR @EmailLike IS NULL ) OR ( CASE CHARINDEX('<Name>CC</Name><Value>', s.[ExtensionSettings]) WHEN 0 THEN '' ELSE SUBSTRING(s.[ExtensionSettings], LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.[ExtensionSettings]), CHARINDEX('</Value>', s.[ExtensionSettings], CHARINDEX('<Name>CC</Name><Value>', s.[ExtensionSettings]) + 1) - (LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.[ExtensionSettings]))) END LIKE '%' + @EmailLike + '%' ) OR ( CASE CHARINDEX('<Name>BCC</Name><Value>', s.[ExtensionSettings]) WHEN 0 THEN '' ELSE SUBSTRING(s.[ExtensionSettings], LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.[ExtensionSettings]), CHARINDEX('</Value>', s.[ExtensionSettings], CHARINDEX('<Name>BCC</Name><Value>', s.[ExtensionSettings]) + 1) - (LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.[ExtensionSettings]))) END LIKE '%' + @EmailLike + '%') ) true ItemID System.Guid Name System.String Description System.String Type System.Int32 Parameter System.String ReportFolder System.String ReportPath System.String URL_ReportFolder System.String URL_Report System.String URL System.String URL2 System.String ReportCreatedBy System.String ReportCreationDate System.DateTime ReportModifiedBy System.String ReportModifiedDate System.DateTime SubscriptionOwner System.String SubscriptionModifiedBy System.String SubscriptionModifiedDate System.DateTime SubscriptionID System.Guid SubscriptionDescription System.String ExtensionSettings System.String EventType System.String EmailSubject System.String EmailTo System.String EmailCc System.String EmailBcc System.String EmailComment System.String EmailIncludeLink System.String EmailRenderFormat System.String EmailPriority System.String DeliveryExtension System.String DeliveryExtensionImage System.String SubscriptionType System.String SubscriptionLastStatus System.String StatusFail System.String MatchData System.String RunTime System.String LastRunDate System.String LastRunTime System.DateTime MinutesInterval System.Int32 DaysInterval System.Int32 WeeksInterval System.Int32 DaysOfWeek System.Int32 DaysOfMonth System.Int32 Month System.Int32 MonthlyWeek System.Int32 JobName System.Int32 ScheduleName System.String ScheduleDays System.String SchDaySun System.Int32 SchDayMon System.Int32 SchDayTue System.Int32 SchDayWed System.Int32 SchDayThr System.Int32 SchDayFri System.Int32 SchDaySat System.Int32 ScheduleStartDate System.DateTime ScheduleEndDate System.DateTime Flags System.Int32 RecurrenceType System.Int32 State System.Int32 EventStatus System.String EventDateTime System.DateTime LastStatus System.String ScheduleID System.Guid ReportServer =Parameters!all_value.Value WITH catalog_type_description AS ( SELECT tbl.* FROM (VALUES ( 1, 'Folder') , ( 2, 'Report') , ( 3, 'Resources') , ( 4, 'Linked Report') , ( 5, 'Data Source') , ( 6, 'Report Model') , ( 7, 'Report Part') , ( 8, 'Shared Dataset') , ( 11, 'KPI') , ( 13, 'Power BI') , ( 14, 'Excel') ) tbl ([TypeID], [TypeDescription]) WHERE [TypeID] IN(2, 4, 13) ) SELECT [Report_Folder] = SUBSTRING(c.[Path], 2, Len(c.[Path])-Len(c.[Name])-2) FROM [dbo].[Subscriptions] AS s WITH (NOLOCK) INNER JOIN [dbo].[Catalog] AS c WITH (NOLOCK) ON s.[Report_OID] = c.[ItemID] INNER JOIN catalog_type_description AS ctd ON ctd.[TypeID] = c.[Type] UNION SELECT @all_value ORDER BY 1 true Report_Folder System.Object ReportServer =Parameters!ReportFolder.Value =Parameters!all_value.Value SELECT [Report_Name] = c.[Name] FROM [dbo].[Subscriptions] AS s WITH (NOLOCK) INNER JOIN [dbo].[Catalog] AS c WITH (NOLOCK) ON s.[Report_OID] = c.[ItemID] WHERE 1=1 AND c.[Type] IN(2, 4, 13) AND (SUBSTRING(c.[Path], 2, Len(c.[Path])-Len(c.[Name])-2) IN(@ReportFolder) OR @all_value IN (@ReportFolder)) UNION SELECT @all_value ORDER BY 1 true Report_Name System.Object ReportServer =Parameters!all_value.Value SELECT DISTINCT [Status] FROM [dbo].[ExecutionLog] WITH (NOLOCK) UNION SELECT @all_value true Status System.Object ReportServer =Parameters!StartDateTime.Value =Parameters!EndDateTime.Value =Parameters!all_value.Value SELECT DISTINCT [s].[LastStatus] FROM [dbo].[Subscriptions] AS [s] WITH(NOLOCK) WHERE 1 = 1 AND ( [s].[LastRunTime] >= @StartDateTime OR @StartDateTime IS NULL ) AND ( [s].[LastRunTime] <= @EndDateTime OR @EndDateTime IS NULL ) UNION SELECT @all_value ORDER BY 1; LastStatus System.Object ReportServer =Parameters!all_value.Value SELECT tbl.* FROM (VALUES ( 'Success') , ( 'Failure') ) tbl ([StatusGroup]) UNION SELECT @all_value StatusGroup System.Object ReportServer SELECT tbl.* FROM (VALUES ( 2, 'Report') , ( 4, 'Linked Report') , ( 13, 'Power BI') ) tbl ([Type], [TypeDescription]) Type System.Int32 TypeDescription System.String 0.6cm 3.5cm 0.6cm 6cm 0.6cm 0.6cm 5cm 3cm 2cm 3.5cm 2.25cm 1.15cm 1.15cm 1.15cm 1.15cm 1.15cm 1.15cm 1.15cm 3.25cm 3.25cm 9.75cm 6.25cm 6cm 6cm 10cm 1.5cm 2.75cm 2.25cm 5.02646cm 3cm 10.5cm 2.25cm 2.25cm 3.25cm 2.25cm 3.25cm 2.25cm 3.25cm 5.5cm 0.6cm true true 0.75pt =Code.CandyStripe(True, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) Embedded folder Clip 2pt 2pt 2pt 2pt true true =Fields!ReportFolder.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) Embedded =Switch(Fields!Type.Value = 2, "report", Fields!Type.Value = 4, "report_link", Fields!Type.Value = 13, "powerbi") Clip 2pt 2pt 2pt 2pt true true =Fields!Name.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) Embedded =IIF(Fields!DeliveryExtensionImage.Value = "folder_table", "folder_table", "email") Clip 2pt 2pt 2pt 2pt true true 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) Embedded log Clip 2pt 2pt 2pt 2pt true true =Fields!SubscriptionDescription.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!DeliveryExtension.Value DeliveryExtension 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!SubscriptionType.Value SubscriptionType1 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!ScheduleName.Value 28 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!ScheduleDays.Value 27 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =IIf(Fields!SchDaySun.Value = "0", "", Fields!RunTime.Value) 26 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =IIf(Fields!SchDayMon.Value = "0", "", Fields!RunTime.Value) 25 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =IIf(Fields!SchDayTue.Value = "0", "", Fields!RunTime.Value) 24 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =IIf(Fields!SchDayWed.Value = "0", "", Fields!RunTime.Value) 23 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =IIf(Fields!SchDayThr.Value = "0", "", Fields!RunTime.Value) 22 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =IIf(Fields!SchDayFri.Value = "0", "", Fields!RunTime.Value) 21 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =IIf(Fields!SchDaySat.Value = "0", "", Fields!RunTime.Value) 20 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!EventDateTime.Value 19 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!ScheduleEndDate.Value 18 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!EmailSubject.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true =Fields!EmailTo.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true =Fields!EmailCc.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true =Fields!EmailBcc.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true =Fields!EmailComment.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!EmailIncludeLink.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!EmailRenderFormat.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!EmailPriority.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true =Fields!Parameter.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!EventStatus.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true =Fields!SubscriptionLastStatus.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!SubscriptionOwner.Value 6 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!SubscriptionModifiedBy.Value 5 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!SubscriptionModifiedDate.Value 4 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!ReportCreatedBy.Value 3 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!ReportCreationDate.Value 2 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!ReportModifiedBy.Value 1 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!ReportModifiedDate.Value 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt true true =Fields!ScheduleID.Value ScheduleID 0.75pt =Code.CandyStripe(False, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value) 2pt 2pt 2pt 2pt 1cm true true 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt true 1cm true =Fields!ReportFolder.Value true Report Folder 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt true 1cm true true 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt true =IIF(Globals!RenderFormat.Name = "EXCELOPENXML", true, false) 1cm true =Fields!Name.Value true Report Name 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt true 1cm true true 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt true 1cm true true 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt true 1cm true true Subscription Description 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Delivery Extension Textbox2 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt =IIF(Globals!RenderFormat.Name = "EXCELOPENXML", true, false) 1cm true true Subscription Type Textbox4 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Shared Name 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt =IIF(Globals!RenderFormat.Name = "EXCELOPENXML", true, false) 1cm true true Schedule 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Sun 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt =IIF(Globals!RenderFormat.Name = "EXCELOPENXML", true, false) 1cm true true Mon 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Tue 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Wed 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Thu 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Fri 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Sat 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Subscription Run Time 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Subscription End Date 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Subject 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Email To 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Email Cc 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Email Bcc 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Email Comment 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Include Link 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Render Format 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Priority 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Parameters 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Last Run Status 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Subscription Status 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Subscription Owner 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Subscription Modified By 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true =Fields!SubscriptionModifiedDate.Value true Subscription Modified Date 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Report Created By 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Report Created Date 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Report Modified By 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Report Modified Date 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt 1cm true true Schedule ID Textbox1 0.75pt =Variables!ColorTableHeaderBackground.Value Middle 2pt 2pt 2pt 2pt Detail =Fields!ReportFolder.Value =Fields!Name.Value =Fields!EmailSubject.Value Detail_Collection Output true true true true true Subscriptions 1.6cm 129.47646cm 0.75pt Tahoma 8pt 1.6cm =IIF(IsNothing(Parameters!StartDateTime.Value) And IsNothing(Parameters!EndDateTime.Value), "", vbcrlf) + IIF(IsNothing(Parameters!StartDateTime.Value), "", "From: ") + Format(Parameters!StartDateTime.Value, Variables!FormatDateTime.Value) + IIF(IsNothing(Parameters!EndDateTime.Value), "", " To: ") + Format(Parameters!EndDateTime.Value, Variables!FormatDateTime.Value) 2pt Textbox8 2.75cm 24.86472cm =IIF(Globals!RenderFormat.Name = "EXCELOPENXML", true, false) Middle 25pt 2pt 2pt 2pt Embedded PBIRS FitProportional 24.86472cm 2.75cm 4.30514cm 1 =IIF(Globals!RenderFormat.Name = "EXCELOPENXML", true, false) 0.75pt 0.92063cm true true true true true ="© " + Variables!CompanyName.Value + " " + Year(Today).ToString 2pt =Variables!UrlReport.Value ="javascript:void(window.open('" + Variables!UrlReportWithParameters.Value + "','_blank'))" Open the report with the current parameters in a separate tab 2pt 0.92063cm 21cm 2pt 2pt 2pt 2pt true true true ="Page: " & Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString() + Environment.NewLine + "Produced By: " + Variables!UserName.Value + " @ " + Variables!ReportRunDateTime.Value 2pt 21cm 0.92063cm 8.16986cm 1 0.75pt 21cm 29.7cm 11in 8.5in 0.25cm 0.25cm 0.25cm 0.25cm 1cm