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