Invoke SSRS Subscription from Database

Take the first block of code and search by your report name. Take the SQLAgent Job Name and apply it to the msdb.dbo.sp_start_job to fire off your SSRS Subscription.

USE ReportServer
GO

/Execute SSRS Subscription Manually/
/Connect to Database ReportServer/
SELECT
sub.LastStatus
,s.ScheduleID AS SQLAgent_Job_Name
,SUB.Description AS Sub_Desc
,SUB.DeliveryExtension AS Sub_Del_Extension
,C.Name AS ReportName
,C.Path AS ReportPath
FROM ReportServer.dbo.ReportSchedule RS
INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE C.Name LIKE ‘%AFTEROWL%’ — Enter Report Name to find Job_Name

/Connect to Database MSDB on the Reporting Server/
/Enter SQLAgent_Job_Name to execute the subscription based on Job ID/
EXEC msdb.dbo.sp_start_job @job_name = ‘7229B588-626C-43B9-8B18-028582673464’

Advertisement

Alternate Row Color

When you’re displaying data in a Table or Tabular, sometimes the report may look very busy to the eyes. By alternating the row colors, the results will look more easy on the eyes. Here’s how you do it.

Here’s our “Before” results

Step 1: Click on the Design tab

Step 2: Click on the detail row. In the BackgroundColor drop-down, select “Expression”

Step 3: Type this  =IIF(RowNumber(Nothing) MOD 2,”White“, “Silver“)

Step 4: Preview the report

SSRS Subscription List

If you need to get all SSRS that has a Subscription.

SELECT SubscriptionOwner = u.UserName
,s.ModifiedDate
,s.[Description]
,s.EventType
,s.DeliveryExtension
,s.LastStatus
,s.LastRunTime
,sch.NextRunTime
,ScheduleName = sch.Name
,ReportName = c.Name
,ReportPath = c.[Path]
,ReportDescription = c.[Description]
FROM ReportServer.dbo.Subscriptions AS s
INNER JOIN ReportServer.dbo.Users AS u ON s.OwnerID = u.UserID
INNER JOIN ReportServer.dbo.[Catalog] AS c ON s.Report_OID = c.ItemID
INNER JOIN ReportServer.dbo.ReportSchedule AS rs ON s.Report_OID = rs.ReportID
AND s.SubscriptionID = rs.SubscriptionID
INNER JOIN ReportServer.dbo.Schedule AS sch ON rs.ScheduleID = sch.ScheduleID
ORDER BY u.UserName
,c.[Path];