Mscomctl32.ocx ActiveX Control

Phenomenally useful. Not supported at 64bit.


Normally:  C:\Windows\SysWOW64.


I’m using version 6.01.9846 of 9/12/2015


  • C:\Windows\system32>Regsvr32  c:\windows\syswow64\mscomctl.ocx
  • Unregister C:\Windows\system32>Regsvr32 /u  c:\windows\syswow64\mscomctl.ocx


First check References.  If it shows as MISSING, untick it and close the References dialog box.  Compile the code (which will fail, naturally), then re-add the reference.

Problem Library is  correctly (Re)Registered but you get The Expression MouseDown you entered …  error.  Or there is a The OLE server may not be registered error when double-clicking a control in design view:

The operation on the ListViewCtrl object failed.  The OLE server may not be registered.  To register the OLE server, reinstall it.

The OLE server may not be registered

Solution:  open the reference dialog and increase the Priority of the Microsoft Windows Common Controls 6.0 (SP6) entry  as far as it will go (it won’t go higher than the Access Object Library reference). This has immediate effect.





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.


  • There is no help or instructions.


  • 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.


  • 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


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


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.

		@DateRangeId : set to 0 to return all date ranges.  Use this mode for populating a
			dropdown list parameter named "DateRangeId", Label "Date Range".
		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

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


			-- 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
		StartDateKey = CAST(CONVERT(VARCHAR, CTE.StartDate, 112) AS INT),
		EndDateKey = CAST(CONVERT(VARCHAR, CTE.EndDate, 112) AS INT)	
		(@DateRangeKey = 0)
		(@DateRangeKey = CTE.DateRangeKey)


Shared Datasets

Create a shared dataset named DateRangeParameter


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

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”

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.


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.

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

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

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




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


  • 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 ) 
	print 'Seed DimDuration...';

		INSERT DimDuration
			0, 0, 0, 0, '0 secs', 0

	  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

		SELECT TOP(15000) 
			Number / 60,
			Number / (60 * 60),
			(Number / 60) % 60,
				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'
			FROM Tally;

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

			SELECT DurationAnalysis1,
				DurationAnalysis1_MaxSeconds  = MAX(Seconds)
			FROM dbo.DimDuration
			GROUP BY DurationAnalysis1
			DurationAnalysis1_MaxSeconds = CTE.DurationAnalysis1_MaxSeconds
			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 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.