Backup Compression

Sample backup compression figures – SQL Server 2012.  The relative sizes and timings are interesting. This was a database with data that compressed well.

  • Uncompressed backup file:  73.4GB.  18 minutes to create
  • Compressed backup:  17.5GB.  About 20 minutes to create
  • Compressing the 73.4GB backup file using 7-zip:  9.7GB.  About 2 hours to compress.

 

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?

Powershell Commands for SQL Server

SQLPS

The SQLPS utility is being replaced by the sqlps module:

Import-Module "sqlps" -DisableNameChecking

To run this interactively against a remote server, enter a remoting session:

Enter-PSSession -ComputerName HOST-NAME -Credential DOMAIN\user

Add a User to a SSAS Role

This solves the problem of adding a domain user to a SSAS database role from a different domain which cannot be done from the SSAS project or via SSMS (XMLA) from outside the domain since these methods attempt to resolve credentials locally.

Import-Module "sqlps" -DisableNameChecking
cd sqlas
cd "database name"
Add-RoleMember -Database "Telecoms Sales" -RoleName "ReportingServices" -M
emberName "DOMAIN\UserName"

(It looks like the SSRS default execution account can’t be used implicitly as with sql datasources)

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"))

 

 

 

Data-Tier Application (dac) and SqlPackage.exe

Can you install tools to export/deploy DacPac files without requiring a Visual Studio installation?

Favourite SqlPackage commands

Command Line reference  https://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx

Extract DACPAC I didn’t find that the /p:IgnoreUserLoginMappings={True | False} switch made any difference. Users were still created on default deployment.

The definition in the reference is not easy to interpret: “Specifies whether relationships between users and logins will be ignored.”  Does that mean it will still attempt to create users, or not?

Deploy DACPAC   “C:\Program Files\Microsoft SQL Server\120\DAC\bin\SQlPackage.exe” /a:publish /tcs:”Data Source=localhost;Initial
Catalog=mydb;Integrated Security=true” /sf:”C:\Temp\db_backup\ADatabase.dacpac”
/p:ExcludeObjectTypes=RoleMembership;users;Logins;

This excludes users and logins, necessary if deploying to environment that doesn’t support users that were in the source.  If there are objects owned by a custom user the deployment will still fail.

Master Data Services 2012 Notes

Notes from a first brush with MDS.

Installation (2102)

  • The ApplicationPool credential should be a member of MDS_Services (?) local user group. (not convinced that setup does this for you).
  • The installation is not complete until you get the chance to click Apply on the Web Configuration page of Master Data Services Configuration Manager.   If this button isn’t enabled, it’s not fully installed (for example, the Authentication mode won’t be correctly configured and the WCF Service (which is used by the Data Explorer and the Excel Add-in) won’t work.

{Code} not updating in Excel

Symptom the Code column seems to have become disconnected from the recordset.  The cell is not highlighted when it is changed, and its value is not submitted on Publishing. This causes “The code cannot be empty” errors.

Cause check that the pixel width of Code is not zero.   Pixel width can be set to zero to hide columns in Excel, but for the Code column this causes the column to appear but to be disconnected (non-updateable).

Deploying Models

  • Model deployment does not include integration views.  Each view has to be recreated manually on production environment – there are not many settings.
  • Model deployment does not include permissions, reasonably since these will often vary between environments.

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/

Duration Dimension

For measuring short durations, such as telephone calls or handling times.

Q1 Linear Grain?

The question I haven’t answered is whether the dimension should be at the finest grain for all durations.  The grain I am using is seconds but probably after 30 minutes that’s finer than is needed. That’s 86,400 rows for a day.   An alternative would be using a log (ish) scale for DimDurationKey:  seconds up to 1 hour; minutes up to 24 hours, hours up to ? 7 days, then days… weeks… months.  This would allow a single Duration Dimension to cover a wide range of periods without becoming huge.  DimDimensionKey would have to be calculated during load.

Q2 Range Titles

What is a clear way of creating range titles:  0-1 minutes, 1-5 minutes, etc.   Strictly these are 0-59 secs, 1min – 1:59, 2min – 4:59, although users probably think of them as 1min, 2min, 5min.

  • 0:00 – 0:59
  • 1:00 – 1:59
  • 2:00 – 4:59
  • 5:00 – 59:59

Or

  • 0 – 59 secs
  • 1 min – 1:59
  • 2 min – 4:59
  • 5 min – 59:59

Neither of those is clear or elegant.  The second emphasises the start of each range whereas the information is that it’s in the “5 minute” band, whereas “2 min – 4:59” most salient value is “2 min”.

Based on this I looked at rounding the end of each range.

  • 0 – 60 secs
  • 1:01- 2 min
  • 2:01 – 5 min
  • 5:01 – 1 hour

That looks better but it gets complicated once you move beyond 1 hour.  How to express the next segment up to say 1.5 hours:

  • 1:00:01 to 1 hour 3o mins
  • 1:00:01 to 1:30:00
  • 1:00:01 to 1 and a half hours

All are ugly mixes of units.

I think for short durations I am resigned to accept the one-second inaccuracy of “0-1 minute”, “2-5 minutes” for the sake of clarity and fitting in with the way that people actually look at these figures.  So long as the second in question is not double counted I don’t think it matters that it’s location is indeterminate. ( In contrast this wouldn’t be acceptable for age ranges (0-65, 65-70).)

In doing this I revert to rounding the start of each round. That accords then with the MinuteOfHour value which looks more logical when viewing the table as a whole.

So that leaves

  • 0 – 59 secs
  • 1 – 2 mins   [60 – 119 secs]
  • 2 – 5 mins
  • 45 – 60 mins
  • 60 – 90 mins
  • 90 mins – 1 hour

Creation Script

IF ( (SELECT COUNT(*) AS C FROM dbo.DimDuration) = 0 ) 
BEGIN
	print 'Seed DimDuration...';

		INSERT DimDuration
		(
			[Seconds],
			[Minutes],
			Hours,
			MinuteOfHour,
			DurationAnalysis1,
			DurationAnalysis1_MaxSeconds
         
		)
		VALUES
		(
			0, 0, 0, 0, '0 secs', 0
		);


	WITH
	  Pass0 as (select 1 as C union all select 1), --2 rows
	  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
	  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
	  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
	  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
	  Tally as (select row_number() over(order by C) as Number from Pass4)

	INSERT DimDuration
		(
			[Seconds],
			[Minutes],
			Hours,
			MinuteOfHour,
			DurationAnalysis1,
			DurationAnalysis1_MaxSeconds
         
		)

		SELECT TOP(15000) 
			Number,
			Number / 60,
			Number / (60 * 60),
			(Number / 60) % 60,
			CASE 
				WHEN (Number)       < 60	    THEN '1 - 59 secs'
				WHEN (Number)       < 120	    THEN '1 - 2 mins'
				WHEN (Number / 60)  < 3		    THEN '2 - 3 mins'
				WHEN (Number / 60)  < 4		    THEN '3 - 4 mins'
				WHEN (Number / 60)  < 5		    THEN '4 - 5 mins'
				WHEN (Number / 60)  < 6		    THEN '5 - 6 mins'
				WHEN (Number / 60)  < 7		    THEN '6 - 7 mins'
				WHEN (Number / 60)  < 8		    THEN '7 - 8 mins'
				WHEN (Number / 60)  < 9		    THEN '8 - 9 mins'
				WHEN (Number / 60)  < 10	    THEN '9 - 10 mins'
				WHEN (Number / 60)  < 15	    THEN '10 - 15 mins'
				WHEN (Number / 60)  < 20	    THEN '15 - 20 mins'
				WHEN (Number / 60)  < 30	    THEN '20 - 30 mins'
				WHEN (Number / 60)  < 40	    THEN '30 - 40 mins'
				WHEN (Number / 60)  < 50	    THEN '40 - 50 mins'
				WHEN (Number / 60)  < 60	    THEN '50 - 60 mins'
				WHEN (Number / 60)  < 90	    THEN '60 - 90 mins'
				WHEN (Number / 60)  < 120	    THEN '90 mins - 2 hours'
				WHEN (Number / (60 * 60)) < 3	THEN '2 - 3 hours'
				WHEN (Number / (60 * 60)) < 4	THEN '3 - 4 hours'
				WHEN (Number / (60 * 60)) < 10	THEN '4 - 10 hours'
				WHEN (Number / (60 * 60)) < 15	THEN '10 - 15 hours'
				WHEN (Number / (60 * 60)) < 24	THEN '15 - 24 hours'
				ELSE  'over 24 hours'
			END
			,0
			FROM Tally;

	-- Add a column that can be used as a sort-key for ranges.

		WITH CTE AS
		(
			SELECT DurationAnalysis1,
				DurationAnalysis1_MaxSeconds  = MAX(Seconds)
			FROM dbo.DimDuration
			GROUP BY DurationAnalysis1
		)
	
		UPDATE DD 
		SET
			DurationAnalysis1_MaxSeconds = CTE.DurationAnalysis1_MaxSeconds
		FROM
			DimDuration DD 
			INNER JOIN CTE ON DD.DurationAnalysis1 = CTE.DurationAnalysis1;

	print 'Seed DimDuration complete.';

 

SSRS Linked Reports

Is it worth the effort?

  • Advantages
    • Can encapsulate a set of defaults. Pre-configurations of a very general report, for example.
  • Disadvantages
    • Links between reports take users to the master source folder.
    • (!) Dynamic parameter defaults don’t survive linking.  Instead the value of those defaults at the time the linked report is created will be saved as literal values.

It is useful to keep “master” reports (.rdl) files in a restricted area and present reports as linked reports for users to browse.  The linked report mechanism allows distinct parameter defaults to be set.

There are some challenges:

Permissions

Permissions are those of the folder that is linked from.

If the master report contains a subreport, the subreport will be referenced from its original location.  Permissions are not transferred by the linking mechanism to the subreport.  Hence it is necessary to give the user View report permissions (only?) on the subreport.  Do this with a new ‘View report’ user role.

Open Report Action

Linked Reports are also run from their original location.   (Proof:  create a set of reports with Open Report actions established between them.  Create a link from another folder to one only of these reports.  Open the linked report and click the Open Report action object:  the click-through will work, and the new report is being served from the master location.

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.