Script to Configure SSRS 2017 Reporting Services

This is an attempt to create a script that will configure an instance of SSRS that has been left at the “installed but not configured” stage by the installer.   The script is required to run during the spin-up of a virtual SQL Server machine (e.g. AWS) to result in a fully functioning SSRS site with no further configuration to be done.

(Work in progress)

Versions

  • SSRS Native Mode 2017 (v14) Standard edition
  • Windows Server 2016 Standard

Actions to be Scripted

  • Reserve /Reports and /ReportServer URLs  [done]
  • Greate SSRS databases and assign rights [done]
  • SMTP Settings
  • Specify Execution Account
  • Create and Backup Encryption Keys
  • Set File Share Account for Subscriptions

Resources

The Script

Main changes to update from SSRS v12 to v14:

  • WMI object namespace is now “root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin”   (v12 was “…\RS_MSSQLSERVER\v12\Admin”) – reflecting the fact that SSRS as an application is now separate to SQL Server.
  • The default service account is now “NT Service\SQL Reporting Services” (instead of “NT Service\ReportServer” for v12).
  • The Report Manager (/ReportServer) web application name is “ReportServerWebApp” (formerly “ReportManager”).  This means there are two very similarly, but logically, named applications:
    • ReportServerWebService  the service that emits the actual reports – /ReportServer
    • ReportServerWebApp (formerly ReportManager) the full SSRS user and admin interface – /Reports
## This script configures a Native mode report server without HTTPS
## SSRS Version :  2017 - v14
## Script source: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sqlclassic/virtual-machines-windows-classic-ps-sql-report
## Amended by Andrew Ing for v14
## Script Version 25-11-2018
 
 
$ErrorActionPreference = "Stop"
 
$server = $env:COMPUTERNAME
$HTTPport = 80 # change the value if you used a different port for the private HTTP endpoint when the VM was created.
 
## Set PowerShell execution policy to be able to run scripts
Set-ExecutionPolicy -Scope CurrentUser RemoteSigned -Force
 
## Utility method for verifying an operation's result
function CheckResult
{
     param($wmi_result, $actionname)
     if ($wmi_result.HRESULT -ne 0) {
         write-error "$actionname failed. Error from WMI: $($wmi_result.Error)"
     }
}
 
$starttime=Get-Date
write-host -foregroundcolor DarkGray $starttime StartTime
 
## ReportServer Database name - this can be changed if needed
$dbName='ReportServer'
 
## Register for MSReportServer_ConfigurationSetting
$RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin"
 
## Report Server Configuration Steps

## Setting the web service URL ##
write-host -foregroundcolor green "Setting the web service URL"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## SetVirtualDirectory for ReportServer site
     write-host 'Calling SetVirtualDirectory'
     $r = $RSObject.SetVirtualDirectory('ReportServerWebService','ReportServer',1033)
     CheckResult $r "SetVirtualDirectory for ReportServer"
 
## ReserveURL for ReportServerWebService - port $HTTPport (for local usage)
     write-host "Calling ReserveURL port $HTTPport"
     $r = $RSObject.ReserveURL('ReportServerWebService',"http://+:$HTTPport",1033)
     CheckResult $r "ReserveURL for ReportServer port $HTTPport" 
 
## Setting the Database ##
write-host -foregroundcolor green "Setting the Database"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## GenerateDatabaseScript - for creating the database
     write-host "Calling GenerateDatabaseCreationScript for database $dbName"
     $r = $RSObject.GenerateDatabaseCreationScript($dbName,1033,$false)
     CheckResult $r "GenerateDatabaseCreationScript"
     $script = $r.Script
 
## Execute sql script to create the database
     write-host 'Executing Database Creation Script'
     $savedcvd = Get-Location
     Import-Module SQLPS              ## this automatically changes to sqlserver provider
     Invoke-SqlCmd -Query $script
     Set-Location $savedcvd
 
## GenerateGrantRightsScript 
     $DBUser = "NT SERVICE\SQLServerReportingServices"
     write-host "Calling GenerateDatabaseRightsScript with user $DBUser"
     $r = $RSObject.GenerateDatabaseRightsScript($DBUser,$dbName,$false,$true)
     CheckResult $r "GenerateDatabaseRightsScript"
     $script = $r.Script
 
## Execute grant rights script
     write-host 'Executing Database Rights Script'
     $savedcvd = Get-Location
     cd sqlserver:\
     Invoke-SqlCmd -Query $script
     Set-Location $savedcvd
 
## SetDBConnection - uses Windows Service (type 2), username is ignored
     write-host "Calling SetDatabaseConnection server $server, DB $dbName"
     $r = $RSObject.SetDatabaseConnection($server,$dbName,2,'','')
     CheckResult $r "SetDatabaseConnection"  

## Setting the Report Manager URL ##
 
write-host -foregroundcolor green "Setting the Report Manager URL"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## SetVirtualDirectory for Reports (Report Manager) site
     write-host 'Calling SetVirtualDirectory'
     $r = $RSObject.SetVirtualDirectory('ReportServerWebApp','Reports',1033)
     CheckResult $r "SetVirtualDirectory"
 
## ReserveURL for ReportManager  - port $HTTPport
     write-host "Calling ReserveURL for ReportServerWebApp, port $HTTPport"
     $r = $RSObject.ReserveURL('ReportServerWebApp',"http://+:$HTTPport",1033)
     CheckResult $r "ReserveURL for ReportManager port $HTTPport"
 
write-host -foregroundcolor green "Open Firewall port for $HTTPport"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## Open Firewall port for $HTTPport
     New-NetFirewallRule -DisplayName "Report Server (TCP on port $HTTPport)" -Direction Inbound –Protocol TCP –LocalPort $HTTPport
     write-host "Added rule Report Server (TCP on port $HTTPport) in Windows Firewall"
 
## Restart SSRS service ##
 write-host -foregroundcolor green "Restart SQLServerReportingServices"
 write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
 $time=Get-Date
 write-host -foregroundcolor DarkGray $time

Restart-Service SQLServerReportingServices

write-host 'Operations completed, Report Server is ready'
write-host -foregroundcolor DarkGray $starttime StartTime
$time=Get-Date
write-host -foregroundcolor DarkGray $time

Restoring the Unconfigured state

During iterative testing it’s necessary to restore the SSRS instance to the unconfigured state.   This is not possible from the SSRS Configuration Tool.  There are some WMI methods to remove URLs, which I haven’t tried yet.  Here are some manual methods.

  • Remove URL Reservations .
    • Open C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\rsreportserver.config  in notepad++, find <URLReservations>  and remove all <URL> nodes  (there will be at least two once configured: for the ReportServerWebService and ReportServerWebApp applications.
    • Run commands –  assuming default names (Use netsh http show urlacl to show all current reservations):
      • netsh http delete urlacl URL=http://+:80/Reports
      • netsh http delete urlacl URL=http://+:80/ReportServer
    • Restart SQLServerReportingServices service.
    • Alternatively use this script, which calls the WMI RemoveURL methods:
      ## UN-CONFIGURE a native instance of SQL Server Reporting Services.
      ##
      ## This is intended for test iterations where SSRS needs to be returned to its freshly-installed state.
      ## SSRS Version :  2017 - v14
      ## Script Version 25-11-2018
      
      
       $ErrorActionPreference = "Stop"
      
       $server = $env:COMPUTERNAME
       $HTTPport = 80 # change to match what was configured.
      
       ## Set PowerShell execution policy to be able to run scripts
       Set-ExecutionPolicy -Scope CurrentUser RemoteSigned -Force
      
       ## Utility method for verifying an operation's result
       function CheckResult
       {
           param($wmi_result, $actionname)
           if ($wmi_result.HRESULT -ne 0) {
               write-error "$actionname failed. Error from WMI: $($wmi_result.Error)"
           }
       }
      
       $starttime=Get-Date
       write-host -foregroundcolor DarkGray $starttime StartTime
      
       ## ReportServer Database name - this can be changed if needed
       $dbName='ReportServer'
      
       ## Register for MSReportServer_ConfigurationSetting
       $RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin"
      
       
       ## Unset Web Service (/Reports) URL ##
       write-host -foregroundcolor green "Unset Web Service (/Reports) URL"
       write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
       $time=Get-Date
       write-host -foregroundcolor DarkGray $time
      
      
           write-host "Calling RemoveURL port $HTTPport"
           $r = $RSObject.RemoveURL('ReportServerWebService',"http://+:$HTTPport", 1033)
           CheckResult $r "RemoveURL for ReportServer port $HTTPport" 
      
       ## Unset Web App (/Reportserver) URL ##
       write-host -foregroundcolor green "Unset Web App (/Reportserver) URL"
       write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
      
      
          write-host "Calling RemoveURL for ReportServerWebApp, port $HTTPport"
          $r = $RSObject.RemoveURL('ReportServerWebApp',"http://+:$HTTPport",1033)
          CheckResult $r "RemoveURL for ReportManager port $HTTPport"
      
      
       
       ## Restart SSRS service ##
       write-host -foregroundcolor green "Restart SQLServerReportingServices"
       write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
       $time=Get-Date
       write-host -foregroundcolor DarkGray $time
      
       Restart-Service SQLServerReportingServices
      
       $time=Get-Date
       write-host -foregroundcolor DarkGray $time

       

  • Drop the SSRS databases
    ALTER DATABASE ReportServer SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE ReportServerTempDB SET SINGLE_USER WITH ROLLBACK IMMEDIAT
    USE master
    DROP DATABASE ReportServer
    DROP DATABASE ReportServerTempDB

     

Selected Exception Messages

…you might encounter during testing

  • Error from WMI: The Url has already been set for this application. Remove existing URL reservations (see above) first.  
  • Error from WMI: The Url has already been reserved.   (as above)
  • GenerateDatabaseRightsScript failed. Error from WMI: [blank!]    WMI returned no error information, but at this point the script has already created the databases so is able to execute SQL commands.  This script is assigning rights, therefore it is likely  that the database user specfied in the script ($DBUser)  is not valid – which turned out to be the case in this instance.
  • Error from WMI: A virtual directory must first be created before performing this operation.   This occurs if the whole <Application> node is deleted from rsreportserver.config. Only delete the <URL> nodes.  Restore the config file and restart SQLServerReportingServices.

Further Reading

  • Powershell ReportingServicesTools (repository: https://github.com/Microsoft/ReportingServicesTools) contains commands for configuring as well as administering SSRS.  See Claudio Silva’s SQL Bits presentation: https://sqlbits.com/Sessions/Event17/Administrating_SSRS_without_boring_web_based_clicks.

Colours

Colour Choices for Graphs and Tables

Heatmap Colouring for SSRS

Sample colours for a range of 10 levels with mid-point roughly yellow.

Code for BackgroundColor property of cell:

= IIF(Fields!FieldName.Value >= .98, "#63be7b", 
  IIF(Fields!FieldName.Value >= .95, "#86c87d", 
  IIF(Fields!FieldName.Value >= .92, "#b9d780", 
  IIF(Fields!FieldName.Value >= .88, "#e1e383", 
  IIF(Fields!FieldName.Value >= .84, "#feea83", 
  IIF(Fields!FieldName.Value >= .78, "#fedf81", 
  IIF(Fields!FieldName.Value >= .72, "#fdd780", 
  IIF(Fields!FieldName.Value >= .68, "#fba977", 
  IIF(Fields!FieldName.Value >= .65, "#fa9573", 
									 "#f8696b" )
))))))))

 

Standard Colours

Sample Code Note
 123 #DDEBF7 Excel Pivot Header Blue
 123 #FFF2CC Excel Light Yellow Background

 

Mobile Reports in SSRS 2016

A tiled, dashboard style data presentation derived from DataZen and presentable from SQL Server Reporting Services (traditional rdl reports are differentiated as “Paginated Reports”).

Verdict:  it looks very nice, scales to different displays well, and can be presented in a browser via SSRS.   It lacks any update features, so usage as a dashboard is limited.

SSRS Mobile Report

These are initial comments on Build 1.0.3911.0.

Help

  • There is no help or instructions.

Development

  • developed in a basic application called “SQL Server Mobile Report Publisher” which is not integrated into Visual Studio.
  • The app can publish direct to report server or to file system as a .rsmobile file.
  • .rsmobile is a zipped file containing several xml files (sources, assets, metadata, definition), thumbnail images, a resources subfolder with colour specifications, and a data subfolder.
    • The data subfolder only exists if Enable client data caching is ticked in Settings.  Does this mean that data is fixed at design time and will not refresh?  If not, how long is data cached?
    • If present, the data subfolder contains a file for each data set defined containing the full data set returned, as far as I can see.  The format is JSON – encrypted if Encrypt data stored on the client is selected in Settings.

Data

  • Takes data from standard SSRS Datasets or from Excel.  Presumably the Excel data has to be cached in the .rsmobile file.
  • Parameterised Datasets (without defaults) cannot be used.  However MS stated that these would be supported by general release.   However, there would seem to be little value in supporting parameterised datasets unless the parameters could be exposed to the mobile report users – as query string parameters, for example.  Maybe that is what is being considered.
  • Enable client data caching defaults to ON, Encryption defaults OFF.

Auto Refresh

It would make an attractive dashboard if it could refresh automatically.

  • There is no refresh option in the UI.  The full browser page can be refreshed, which a fair amount of distracting animation.
  • An auto-refresh browser plugin could be used.
  • From tests I can confirm that setting Dataset cache expiry on a scheduled doesn’t cause the report automatically to update.
  • Is there a refresh button in the mobile rendering of it?

SSRS Date Range Parameter

Summary

A method for providing a selection of pre-set date ranges plus the ability to enter a custom range of dates.

Until the SSRS improvements in 2016, this will require Nullable date parameter boxes to be used. (Why could not the date boxes be pre-filled with the date range selected?  that would be a good solution. However exhaustive testing has proved this to be impossible.  Two text boxes can be set to the selected dates in a date range but then they can never be edited.  An editable date parameter can be set to show the date range selected and be editable only if the date range parameter has a default but after initial selection the dates shown will never be updated even on selection of a different date range.)

The DateRange data source

This is a table valued function. It has a single parameter (@DateRangeKey) that is set to 0 to return all rows – the full range of options.   Once the choice has been made, set @DateRangeKey to the selected range’s ID and only that row is returned. This is then used to set defaults of SSRS parameters.

 

CREATE FUNCTION [parm].[fnDateRanges](@DateRangeKey int)
/*
	Use to populate SSRS date ranges to provide a set of standard relative date ranges
	(e.g. Last Day, Last Month).

	Note that the DateRangeKey should not alter because this value will be saved in report
	subscriptions and other preset range definitions.

	PARAMETER
		@DateRangeId : set to 0 to return all date ranges.  Use this mode for populating a
			dropdown list parameter named "DateRangeId", Label "Date Range".
	HISTORY
		21/7/2016 AI : Added "Week (starting Sun) to Yesterday".
		03/02/2017 AI:  Corrected calculation of @LastMonday.
		22/02/2017 AI:  + "4 Months to Yesterday".
		27/03/2017 AI: Similar correction to calculation of @LastSunday.
*/
RETURNS @retDateRanges TABLE 
(
    -- Columns returned by the function
    DateRangeKey int PRIMARY KEY NOT NULL, 
    DateRange varchar(50) NOT NULL, 
    StartDate date  NULL, 
    EndDate date  NULL,
	StartDateKey int  NULL,
	EndDateKey int  NULL
)
AS 
BEGIN

	DECLARE @KnownMonday date = '1900-01-01';
	DECLARE @KnownSunday date =  '1905-12-31';

	DECLARE @Today date = CAST(GETDATE() AS date);
	DECLARE @Yesterday date = DATEADD(d, -1, CAST(getdate() AS date));

	DECLARE @LastMonday date =  DATEADD(DAY, ( -1 * (DATEDIFF(DAY, @KnownMonday, @Today) % 7) ), @Today);
	IF (@LastMonday = @Today) SELECT @LastMonday = DATEADD(DAY, -7, @LastMonday);

	DECLARE @LastSunday date =  DATEADD(DAY, ( -1 * (DATEDIFF(DAY, @KnownSunday, @Today) % 7) ), @Today);
	IF (@LastSunday = @Today) SELECT @LastSunday = DATEADD(DAY, -7, @LastSunday);

	DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) ;
	DECLARE @FirstDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1), @LastDayOfPrecedingMonth) AS date) ;
	DECLARE @FirstDayOfYesterdaysMonth date = CAST(DATEADD(dd,-(DAY(@Yesterday) - 1), @Yesterday)  AS date);

	

	WITH CTE AS (
	SELECT * FROM
		( VALUES
			-- Daily values
			( -1, 'Yesterday', @Yesterday, @Yesterday ),
			(  1, 'Today', CAST(GETDATE() AS date), CAST(GETDATE() AS date) ),
			(  2, 'Yesterday and Today',  @Yesterday, @Today ),
			(  7, '7 Days to Today', DATEADD(dd, -6, @Today), @Today),
			( 14, '14 Days to Today', DATEADD(dd, -13, @Today), @Today),
			( 21, '21 Days to Today', DATEADD(dd, -20, @Today), @Today),
			( 28, '28 Days to Today', DATEADD(dd, -27, @Today), @Today),
			( 30, '30 Days to Today', DATEADD(dd, -29, @Today), @Today),
			( 60, '60 Days to Today', DATEADD(dd, -59, @Today), @Today),
			( 90, '90 Days to Today', DATEADD(dd, -89, @Today), @Today),
		
			
			(107, 'Last 7 Days',  DATEADD(dd,  -6,	@Yesterday), @Yesterday ),
			(110, 'Last 10 Days', DATEADD(dd,  -9,  @Yesterday), @Yesterday ),
			(114, 'Last 14 Days', DATEADD(dd, -13,  @Yesterday), @Yesterday ),
			(121, 'Last 21 Days', DATEADD(dd, -20,  @Yesterday), @Yesterday ),
			(128, 'Last 28 Days', DATEADD(dd, -27,  @Yesterday), @Yesterday ),
			(130, 'Last 30 Days', DATEADD(dd, -29,  @Yesterday), @Yesterday ),
			(131, 'Last 31 Days', DATEADD(dd, -30,  @Yesterday), @Yesterday ),
			(156, 'Last 56 Days', DATEADD(dd, -55,  @Yesterday), @Yesterday ),
			(160, 'Last 60 Days', DATEADD(dd, -59,  @Yesterday), @Yesterday ),
			(190, 'Last 90 Days', DATEADD(dd, -89,  @Yesterday), @Yesterday ),
			(195, 'Last 141 Days (20 weeks + 1 day)', DATEADD(dd, -140,  @Yesterday), @Yesterday ),
			(196, 'Last 365/6 Days', DATEADD(year, -1,  @Yesterday), @Yesterday ),

			-- Weekly Values
			(201, 'Week (starting Mon) to Yesterday', @LastMonday, @Yesterday),
			(202, 'Week (starting Sun) to Yesterday', @LastSunday, @Yesterday),
			(205, 'Last week', CASE @@datefirst 
				WHEN 1 THEN CAST(dateadd(day,-datepart(weekday,GETDATE())+1,dateadd(week,-1,GETDATE())) AS date) 
				WHEN 7 THEN CAST(dateadd(day,-datepart(weekday,GETDATE())+2,dateadd(week,-1,GETDATE())) AS date)
				END, CASE @@datefirst WHEN 1 THEN CAST(dateadd(day,-datepart(weekday,GETDATE()),dateadd(week,0,GETDATE())) AS date) 
				WHEN 7 THEN CAST(dateadd(day,-datepart(weekday,GETDATE())+1,dateadd(week,0,GETDATE())) AS date) END),
			(210, '4 Weeks (starting Mon) to Yesterday', DATEADD(week, -3, @LastMonday), @Yesterday),
			(211, '4 Weeks (starting Sun) to Yesterday', DATEADD(week, -3, @LastSunday), @Yesterday),

			-- Monthly Values
			(301, 'Calendar Month to Yesterday', @FirstDayOfYesterdaysMonth, @Yesterday),
			(302, 'Two Months to Yesterday', DATEADD(MM, -1, @FirstDayOfYesterdaysMonth), @Yesterday),
			(303, 'Three Months to Yesterday', DATEADD(MM, -2, @FirstDayOfYesterdaysMonth), @Yesterday),
			(304, 'Four Months to Yesterday', DATEADD(MM, -3, @FirstDayOfYesterdaysMonth), @Yesterday),
			(306, 'Six Months to Yesterday', DATEADD(MM, -5, @FirstDayOfYesterdaysMonth), @Yesterday),


			(311, 'Last month', @FirstDayOfPrecedingMonth, @LastDayOfPrecedingMonth ),
			(312, '2 months ago', DATEADD(MM, -1, @FirstDayOfPrecedingMonth),   DATEADD(DD, -1, DATEADD(MM, -0, @FirstDayOfPrecedingMonth)) ),
			(313, '3 months ago', DATEADD(MM, -2, @FirstDayOfPrecedingMonth),   DATEADD(DD, -1, DATEADD(MM, -1, @FirstDayOfPrecedingMonth)) ),
			(314, '4 months ago', DATEADD(MM, -3, @FirstDayOfPrecedingMonth),   DATEADD(DD, -1, DATEADD(MM, -2, @FirstDayOfPrecedingMonth)) ),
			(316, '6 months ago', DATEADD(MM, -5, @FirstDayOfPrecedingMonth),   DATEADD(DD, -1, DATEADD(MM, -4, @FirstDayOfPrecedingMonth)) ),
			(322, '12 months ago', DATEADD(MM, -11, @FirstDayOfPrecedingMonth), DATEADD(DD, -1, DATEADD(MM, -10, @FirstDayOfPrecedingMonth)) ),
			(999, 'Custom date range',  CAST(NULL as date),  CAST(NULL AS date) )
	
	
		) DT (DateRangeKey, DisplayName, StartDate, EndDate) 
	)

	INSERT @retDateRanges
	SELECT
		CTE.DateRangeKey,
		CTE.DisplayName,
		CTE.StartDate,
		CTE.EndDate,
		StartDateKey = CAST(CONVERT(VARCHAR, CTE.StartDate, 112) AS INT),
		EndDateKey = CAST(CONVERT(VARCHAR, CTE.EndDate, 112) AS INT)	
	FROM
		CTE
	WHERE
		(@DateRangeKey = 0)
		OR
		(@DateRangeKey = CTE.DateRangeKey)

	RETURN
END

Shared Datasets

Create a shared dataset named DateRangeParameter

Query

SELECT        DateRangeKey, DateRange, StartDate, EndDate, StartDateKey, EndDateKey
FROM            parm.fnDateRanges(@DateRangeKey)

Parameter:  @DateRangeKey

Report Datasets

Create two report datasets, each based on the shared data source DateRangeParameter

  • DateRangeParameter
    set @DateRangeKey to 0 (fixed value).  This is used as the available values source of the Date Range parameter option list.
  • DateRangeParameterSelected
    set  @DateRangeKey to the report parameter of the same name.

 

Report Parameters

Create the following parameters

  • DateRangeKey
    (“Date Range”, visible, Integer)
    Use DateRangeParameter as source of Available Values.
  • StartDateKey, EndDateKey
    (Integer, Allow Null, Hidden)
    Use DateRangeParameterSelected as AvailableValues source (StartDateKey for both Value and Label field) and as Default Values source (StartDateKey for Value field).
  • ManualStartDate
    (“Start Date”, Date/Time,  allow Null, visible)
  • ManualEndDate
    (“End Date”, Date/Time, allow Null, visible)

Dataset Parameters

Use the following expressions in the dataset date range parameters.

  • =IIf(Parameters!DateRangeKey.Value = 999, Format(Parameters!ManualStartDate.Value, “yyyyMMdd”), Parameters!StartDateKey.Value )
  • =IIf(Parameters!DateRangeKey.Value = 999, Format(Parameters!ManualEndDate.Value, “yyyyMMdd”), Parameters!EndDateKey.Value )

Revision to allow Fixed Start Date

This parameter arrangement doesn’t allow scheduling of a report with, say, a fixed start date and a moving end date (say, yesterday).  Now although we don’t often want to have fixed start dates – leading to ever-increasing report size – sometimes it’s useful.  Here’s a revision for the start parameter. It makes it slightly less intuitive in that the manual start date will be taken even if the Date Range is not ‘manually entered’.

=IIf(Not IsNothing(Parameters!ManualStartDate.Value), 
Format(Parameters!ManualStartDate.Value, "yyyyMMdd"), 
Parameters!StartDateKey.Value )

Date Range Limitation

Modification to limit the maximum period selectable in a report.

  1. Add report parameter MaxDateRangeDays (Integer, Hidden). Set the AvailableValues and Default values to specified values, e.g. 92.

This is difficult – maybe create a function.

 

Date Range Label

Expression source.   Note that changes should be made if the Revision for Fixed Start Date is deployed.

=IIf(Parameters!DateRangeKey.Value = 999, Format(Parameters!ManualStartDate.Value, "dd/MM/yyyy") & " to " & Format(Parameters!ManualEndDate.Value, "dd/MM/yyyy"), Format(First(Fields!StartDate.Value, "DateRangeParameterSelected"), "dd/MM/yyyy") & " to " & Format(First(Fields!EndDate.Value, "DateRangeParameterSelected"), "dd/MM/yyyy"))

 

 

 

Parse SSRS Subscriptions Table

Information about Report subscriptions is held in ReportServer.dbo.Subscriptions table.

ExtensionSettings

XML Fragment with root node <ParameterValues>  Contains information about the format and destination of the report.

Each <ParameterValue> element has child <Name> and <Value> nodes : named according to classical EAV theory.

  • Windows share parameters
    • PATH : windows share
    • FILENAME : filename (excludingExtension)
    • FILEEXTN : True or False
    • USERNAME , PASSWORD   encrypted values
    • RENDER_FORMAT : Excel, etc
    • WRITEMODE : Overwrite…
  • Email parameters
    • TO | CC | BCC | ReplyTo :  email addresses
    • IncludeReport : True or False
    • RenderFormat :  HTML, Excel, etc
    • Subject : Subject of the email
    • Comment : text included in the email body

Fragment of SQL to parse useful values.

WITH S AS
(
  SELECT TOP 1000 [SubscriptionID],
  ExtensionSettings = cast(ExtensionSettings as xml)

  FROM [ReportServer].[dbo].[Subscriptions]
)

SELECT
	S.SubscriptionID,
	TransmissionMethod = CASE ExtensionSettings.value('(/ParameterValues/ParameterValue/Name)[1]', 'varchar(50)')
		WHEN 'TO' THEN 'Email'
		WHEN 'PATH' THEN 'File Share'
		ELSE 'Unknown' END,
	FileSharePath = ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "PATH"
		return data($value)'),
	FileName = ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "FILENAME"
		return data($value)'),
RenderFormat = ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "RENDER_FORMAT" or $name = "RenderFormat"
		return data($value)'),
	EmailSubject = ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "Subject"
		return data($value)')
	,ExtensionSettings
FROM S

 

References

  • https://www.simple-talk.com/sql/database-administration/ad-hoc-xml-file-querying/

SSRS Notes

Display All Values on a Graph’s Axis

Set Interval in Axis Options section to 1. Auto is normally too sparse for my liking on date axes.  Setting an Interval expression disables auto-settings although the layout will still be adjusted automatically (staggered horizontal, level horizontal, rotated).

Expression for auto-adjusting axis Interval.  This example is for a date axis and uses the report parameters to establish the number of dates to be shown.  Assuming no more than 30 values should be shown on the axis.

= Ceiling(DateDiff(DateInterval.Day, Parameters!StartDate.Value, Parameters!EndDate.Value) / 30)

Conditional Formatting of Graph Lines

For example, to make today’s line stand out against previous days’:

SSRS-HighlightedGraphTrace

Use these expressions

Border

= IIF(Fields!IsTodayFlag.Value = 1, “0.15cm”, “0.05cm”)

Although the default is expressed as “1pt” it seems necessary to use another unit such as cm.

Line Style

= IIF(Fields!IsTodayFlag.Value = 1, “Solid”, “Dotted”)

Fill Color

= IIF(Fields!IsTodayFlag.Value = 1, “DarkBlue”, “Automatic”)

A side effect of this is that the graph lines will become invisible in design view.  Additionally this warning is generated:

Warning 1 [rsInvalidColor] The value of the Color property for the chart ‘Chart1’ is “Automatic”, which is not a valid

Custom User Roles

  • Browser with Subscriptions    “Everything that a browser can do plus subscribe.”
    • View reports, View resources, View folders, Manage individual subscriptions, Manage all subscriptions.
  • View Reports  Use this to grant users ability to view (not browse to) reports.  Use for linked reports that refrence subreports in restricted foldrs.

Disable Actions for Export

Disable click-throughs when reports are rendered for transmission to third parties.

For the Go To Report action, specify this expression for the report name:

=IIF(Globals!RenderFormat.IsInteractive = true, “../OtherProject/ReportName”, false)

Also use expressions to change format to blue, underlined and add tooltip:

=IIF(Globals!RenderFormat.IsInteractive, “Underline”, “Default”)

IsInteractive includes HTML presentation, so that might include the MHTML version that can be included in the body of an email.

Configure SSRS Datasources to use Execution Account

The SSRS Execution Account is a default Windows credential used by SSRS when running a report to access resources (e.g. file shares for image files) or databases for report datasources that do not require credentials and where Integrated Security is specified.

If an Execution Account is not specified the SSRS service credentials are used – which is not ideal

It seems convenient to me to use the Execution Account as a default baseline credential for accessing most data sources, provided you don’t need the additional cast iron security (and administration overhead) of setting user access permissions at the database level.

Configuration

  1. Create a domain credential – e.g. …DataSourceLogin.
  2. Specify this account and password in Reporting Services Configuration Manager  –> Exection Account
  3. Edit the SSRS Data Source:
    1. Add “Integrated Security=SSPI;” to the connection string.
    2. Select Credentials are not required  option of “Connect Using”.

 

(SQL Server 2012)

 

SSRS Formatting Choices

Hiding Objects for Export

CSV

Titles and headings (other than column headings) are typically not required in CSV export.  Text and column objects can be individually hidden from csv output:

  • set the DataElementOutput property of the object to NoOutput

PDF, Excel, etc

  • Show or hide an object conditionally based on  Globals!RenderFormat.Name = “PDF”
  • Other format names are available.

Report Parameter Defaults (SSRS)

Snippets of code to create date-range parameter defaults for SSRS reports.

PrecedingWeek

The seven days up to and including yesterday.

/*
Yesterday = Yesterday's date.
SevenDaysAgo = Seven days before today: i.e. a week up to and including yesterday.

12/08/2014 A Ing.

*/

DECLARE @Yesterday date = DATEADD(d, -1, CAST(getdate() AS date))
DECLARE @SevenDaysAgo date = DATEADD(d, -6, @Yesterday)
SELECT @Yesterday AS Yesterday,
@SevenDaysAgo AS SevenDaysAgo

Preceding Calendar Week (VB)

VBA to return start day of most recent complete week. Note the FirstDayOfWeek enumeration, which is different to the DayOfWeek enum shown in intellisense in the expression editor.

= DateAdd(DateInterval.Day,  0 - WeekDay( Today, FirstDayOfWeek.Tuesday), Today).ToString

Last Six Months – Full Weeks

That is, starts on Monday ends on a Sunday.

/*
	6 month period of full weeks, i.e. from Monday to Sunday.
	Uses two known dates - a Monday and a Sunday.

	To check:
		select datename(dw, @Monday), datename(dw, @Sunday)

	17/10/2014 A Ing.
*/

DECLARE @Monday DATE = '1900-01-01';
DECLARE @Sunday DATE = '1900-01-07';

SELECT 
	MondaySixMonthsAgo = DATEADD(WEEK, DATEDIFF(WEEK, @Monday, DATEADD(month, -6, getdate())), @Monday)
	,LastSunday = DATEADD(WEEK, DATEDIFF(WEEK, @Sunday, CURRENT_TIMESTAMP), @Sunday)

Month to Last Full Day

/*
	Month to up to last full day.
	@Yesterday : last full day
	@FirstDayOfYesterdaysMont : first day of month that Yesterday fell in.
 
	06/11/2014 A Ing.
 
*/

DECLARE @Yesterday date =  DATEADD(d, -1, CAST(getdate() AS date))
DECLARE @FirstDayOfYesterdaysMonth date = CAST(DATEADD(dd,-(DAY(@Yesterday) - 1), @Yesterday)  AS date) 
select
	 @FirstDayOfYesterdaysMonth AS FirstDayOfYesterdaysMonth 
	 ,@Yesterday AS Yesterday

 

Preceding Month – Preferred Version

/*
Start and end of previous month with respect to today's date.

12/08/2014 A Ing.
*/

DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) 
DECLARE @FirstDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1),@LastDayOfPrecedingMonth) AS date) 
SELECT
@FirstDayOfPrecedingMonth AS FirstDayOfPrecedingMonth
,@LastDayOfPrecedingMonth AS LastDayOfPrecedingMonth

Preceding Three Calendar Months

/*
Preceding Three Calendar Months
Previous three calendar months with respect to today's date.

29/01/2015 A Ing.
*/

DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) 
DECLARE @FirstDayOfThirdPrecedingMonth date = CAST(DATEADD(m, -2, DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1),@LastDayOfPrecedingMonth)) AS date) 
SELECT
@FirstDayOfThirdPrecedingMonth AS FirstDayOfThirdPrecedingMonth
,@LastDayOfPrecedingMonth AS LastDayOfPrecedingMonth

 Preceding Six Calendar Months

/*
Preceding Six Calendar Months
Previous six calendar months with respect to today's date.

20/04/2015 A Ing.
*/

DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) 
DECLARE @FirstDayOfSixthPrecedingMonth date = CAST(DATEADD(m, -5, DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1),@LastDayOfPrecedingMonth)) AS date) 
SELECT
@FirstDayOfSixthPrecedingMonth AS FirstDayOfSixthPrecedingMonth
,@LastDayOfPrecedingMonth AS LastDayOfPrecedingMonth

Preceding Month – Version 1

Ideal for reports scheduled for First of the Month, but won’t give current month-to-yesterday for any other day of the month.

/*
Yesterday = Yesterday’s date.
@FirstDayOfYesterdaysMonth = First day of the month that Yesterday fell in.

12/08/2014 A Ing.

*/

DECLARE @Yesterday date = DATEADD(d, -1, CAST(getdate() AS date))
DECLARE @FirstDayOfYesterdaysMonth date = CAST(DATEADD(dd,-(DAY(@Yesterday)-1),@Yesterday) AS date)
SELECT @Yesterday AS Yesterday,
@FirstDayOfYesterdaysMonth AS FirstDayOfYesterdaysMonth

Monitoring SSRS Reports and Subscriptions

Interpreting data in ReportServer database

Observations from SSRS 2008 R2

Subscriptions

table dbo.Subscriptions.

InactiveFlags

There’s no way to disable a subscription from SSRS UI though articles suggest setting InactiveFlags to 1 will achieve this.

  • 0 : schedule is active.
  • 8 : “One or more report parameters have an invalid parameter value.” (text displaed in subscription editor)
  • 16 : “An unkown parameter was found.” (text displayed in subscription editor).  i.e. a parameter name was set that no longer exists in the report specification.

Schedules

table dbo.Schedule

Column Description
 DaysOfWeek  Bitmap.
Sun = 1 … Sat = 64
All days = 127
 WeeksInterval  1 for Daily
 Month Bitmap for months
 MonthlyWeek  nth week of month
or Null
 Recurrence Type 1 : Once
2 : Hourly
3 : [not yet achieved]
4 : Day / Week
6 : Month

Re-triggering Scheduled Reports

This is the code that appears in the report-initiating Job.

EXEC ReportServer.dbo.AddEvent@EventType=‘TimedSubscription’,@EventData=‘<SubscriptionID>’

Values for EventType:

  • Timed Subscription : individually scheduled subscription for  a single report.
  • SharedSchedule : this job triggers a shared schedule.