Create Hyperlink to Execute a Report in Another Browser Tab

Select a text box

Change the Text Color to DarkBlue

Add Underline (so it looks like a link)

Text Box Properties, Action, Go To URL

Expression

="JavaScript:void window.open('../../ReportServer?/REPORT%20FOLDER/REPORT%20NAME&PARAM_NAME=" & Parameters!P_QUARTER.Value & "&P_EMPLOYEE_NUMBER=" & IIF(IsNothing(Fields!TELLER_ID.Value), "-2", Fields!TELLER_ID.Value) & "&P_BRANCH=" & Fields!BRANCH.Value & "&P_BRANCH_LABEL=" & Fields!BRANCH_NUMBER_AND_NAME.Value & "');"

Expand or Collapse All Grouped Items on SSRS Report

https://www.sqlchick.com/entries/2010/9/19/expand-or-collapse-all-grouped-items-on-ssrs-report.html

How to Retrieve the XML for Objects in SSRS

SSRS stored a bunch of different objects in the Catalog table.

Sample query only gets reports ([Type] has several other "types"):

SELECT

ModifiedDate

,  [Path]

, CASE [Type]

    WHEN 2 THEN 'Report'

  END AS TypeName

, CAST(CAST(content AS varbinary(max)) AS xml)

, [Description]

FROM ReportServer.dbo.[Catalog] 

WHERE

  [Type] IN (2)

ORDER BY ModifiedDate DESC

The content CASTs will render as a hyperlink in SSMS; click and you can view the XML.

Conditional Data Driven Subscription

Here is an example of a stored procedure that can be used as the dataset query for a data-driven subscription. It conditionally executes the subscription by returning a row or no rows.

The logic is described below:

CREATE OR ALTER PROCEDURE [dbo].[SAMPLE_WEEKLY_DDQ]
AS
BEGIN
/*
Return a row to trigger the data driven subscription if the current
week is:

> the week of the last successful run of the Month End Processing job
AND
< the week of the next run of the Month End Processing job
*/
DECLARE
@TODAY DATE = GETDATE();

DECLARE
  @CURRENT_WEEK INT = DATEPART(week, @TODAY)
, @LAST_MONTH_END_WEEK INT = DATEPART(week, DATEADD(day, 1, EOMONTH(@TODAY, -1)))
, @NEXT_MONTH_END_WEEK INT = DATEPART(week, DATEADD(day, 1, EOMONTH(@TODAY)))
;

IF @CURRENT_WEEK > @LAST_MONTH_END_WEEK AND @CURRENT_WEEK < @NEXT_MONTH_END_WEEK
BEGIN
SELECT 1 -- return a row to trigger the data driven subscription
END
END
GO

How do I get the SQL Agent job that runs a subscription

SELECT
j.job_id JOB_ID
, TRY_CONVERT(uniqueidentifier, j.[name]) JOB_NAME
, c.[name] REPORT_NAME
, s.Description
FROM ReportServer.dbo.ReportSchedule rs
JOIN msdb.dbo.sysjobs j ON rs.ScheduleID = TRY_CONVERT(uniqueidentifier, j.[name])
JOIN ReportServer.dbo.Subscriptions s ON s.[SubscriptionID] = rs.[SubscriptionID]
JOIN ReportServer.dbo.Catalog c ON c.itemid = s.[Report_OID]
ORDER BY 3


The JOB_NAME is the SQL Agent job name

SSRS Reports With Optional Query Parameters

https://www.mssqltips.com/sqlservertip/3453/sql-server-reporting-services-reports-with-optional-query-parameters/

How to Create a Report URL for Embedding in an Email

DECLARE @REPORT_URL NVARCHAR(128);

-- REPLACE is doing the URL encoding on date parameters formatted as MM/DD/YYYY
-- assuming report folder name has a space; e.g. REPORT FOLDER
-- assuming report name has a space; e.g. REPORT NAME

SET @REPORT_URL =
CONCAT(
N'http://'
, @SERVER_NAME
, N'/reportserver?%2FREPORT%20FOLDER%2FREPORT%20NAME'
, N'&BeginDate=' 
, REPLACE(FORMAT(@BEGIN_DATE, 'MM/dd/yyyy'), '/', '%2F')
, N'&EndDate=' 
, REPLACE(FORMAT(@END_DATE,   'MM/dd/yyyy'), '/', '%2F')
);

SET @Subject = N'Beneficiary Checks Report: ' + FORMAT(@BEGIN_DATE, 'MM/dd/yyyy') + N' - ' + FORMAT(@END_DATE, 'MM/dd/yyyy')

SET @Body = CONCAT(
N'<div><a href="'
, @REPORT_URL
, N'">Please click this link to view the BLAH BLAH for your branch.</a></div><br><br>'

, N'<div>If you discover any discrepancies or have any questions, please email NAME GOES HERE.<b></div><div>&nbsp</div>'
);

Applying Parameter Default Value Using Custom Code

http://www.statslice.com/ssrs-applying-parameter-default-value-using-custom-code

How To Create a Multi-option Parameter Report In SQL Server Reporting Services (SSRS)

https://dzone.com/articles/how-to-creat-multi-option-parameter-report-in-sql

Setting SQL Server Reporting Services Parameters Based On Other Parameters’ Values

https://36chambers.wordpress.com/2011/02/20/setting-sql-server-reporting-services-parameters-based-on-other-parameters-values/

Expression Examples

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/expression-examples-report-builder-and-ssrs?view=sql-server-ver15

Passing multiple value parameters in data driven subscriptions

https://www.bigdatamark.com/ssrs-passing-multiple-value-parameters-in-data-driven-subscriptions/

Change PowerPoint Rendering Options

https://docs.microsoft.com/en-us/sql/reporting-services/pptx-device-information-settings?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/reporting-services/customize-rendering-extension-parameters-in-rsreportserver-config?view=sql-server-ver15

Data Driven Subscription Email Setup Notes

I wanted to deliver data-driven subscriptions via email. Here's how I used my Office 365 account for the smtp server:

Server Address: smtp.office365.com

Port Number: 587

The Report Server Configuration Manager's E-mail settings doesn't allow you to enter the STMP port number. I had to open up the rsreportserver.config file and enter it. I'm using SSRS 2019 and I found the rsreportserver.config file in the folder C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer.

Find the <Extension Name="Report Server Email" section and set <SMTPServerPort>587</SMTPServerPort>

Install SSRS 2019

Download SSRS 2019
https://www.microsoft.com/en-us/download/details.aspx?id=100122

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-reporting-services?view=sql-server-ver15

Generate T-SQL to Manually Execute Report Subscriptions

DECLARE @sql varchar(max);

set @sql = '';

select @sql = @sql + 'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' 

 + cast(Subscriptions.SubscriptionID as varchar(500)) + ''';' + '--- '+ [catalog].name + CHAR(13) 

FROM ReportSchedule 

 INNER JOIN Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID 

 INNER JOIN Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID 

 INNER JOIN [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID 

 AND Subscriptions.Report_OID = [Catalog].ItemID

 WHERE Subscriptions.DeliveryExtension = 'Report Server Email'

 -- show only failed

 --and LEFT (subscriptions.LastStatus, 12) != 'Mail sent to'

 --and convert(varchar(20),Subscriptions.lastruntime, 101) = convert(varchar(20),getdate(), 101);

--EXEC(@sql);

print @sql​

Querying the Report Server Execution Log

https://www.sqlchick.com/entries/2011/2/6/querying-the-report-server-execution-log.html