SSRS bulk permission update 2008R2 to 2014

powershellssrs-2008-r2ssrs-2014

I'm moving SSRS reports from a 2008R2 server to a 2014 server. I've moved all the RDLs with a 3rd party tool only to find none of the permissions exist on the target. I'm attempting to copy the permissions over with Powershell but the script errors when attempting to set the policy with .SetPolicies. The object members appear to be incompatible between 2008 R2 and 2014. Is it possible to move the perms from 2008R2 to 2014 via Powershell?

$ReportServerUriSource = 
'http://my2008Source/ReportServer/ReportService2010.asmx?wsdl'
$ReportServerUriTarget = 
'http://my2014target/ReportServer/ReportService2010.asmx?wsdl'
$InheritParent = $true


 $rsProxy =       New-WebServiceProxy -Uri $ReportServerUriSource -
 UseDefaultCredential
$rsProxyTarget = New-WebServiceProxy -Uri $ReportServerUriTarget -
UseDefaultCredential


$SourceItems = $rsProxy.ListChildren("/MyReports", $true) | `
     SELECT TypeName, Path, ID, Name 

$targetFolder = $rsProxyTarget.GetPolicies($SourceItem.Path,
  [ref]$InheritParent)     

foreach($SourceItem in $SourceItems)
{
$Policies = $rsProxy.GetPolicies($SourceItem.Path, [ref]$InheritParent)

  if($InheritParent -eq $false)
 {
        $Policy = $rsProxy.GetPolicies($SourceItem.Path, 
[ref]$InheritParent)
        $rsProxyTargetPath = $rsProxyTarget.GetPolicies($SourceItem.Path,
[ref]$InheritParent) 
        if($rsProxyTargetPath)
        {
          $rsProxyTarget.SetPolicies($SourceItem.Path, $Policy)

           }

     }

}

Error:

Cannot convert argument "Policies", with value: "Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1er_ReportService2010_asmx_wsdl.Policy[]", 
for "SetPolicies" to type "Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3er_ReportService2010_asmx_wsdl.Policy[]": "Cannot convert the 
"Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1er_ReportService2010_asmx_wsdl.Policy" value of type 
"Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1er_ReportService2010_asmx_wsdl.Policy" to type 
"Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3er_ReportService2010_asmx_wsdl.Policy"."
At line:21 char:15
+               $rsProxyTarget.SetPolicies($SourceItem.Path, $Policy)
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

Best Answer

I was able to hack together a solution from a few different blogs-

function Add-SSRSItemSecurity
(
    [Parameter(Position=0,Mandatory=$true)]
    [Alias("url")]
    [string]$webServiceUrl,

    [Parameter(Position=1,Mandatory=$true)]
    [Alias("path")]
    [string]$itemPath,

    [Parameter(Position=2,Mandatory=$true)]
    [Alias("u")]
    [string]$groupUserName,

    [Parameter(Position=3,Mandatory=$true)]
    [Alias("r")]
    [string]$role,

    [Parameter(Position=2)]
    [bool]$inherit=$true
)

{

    #Fix item path if not starting with /
    if(!$itemPath.StartsWith("/")) { $itemPath = "/" + $itemPath}

    #Create Proxy
    Write-Host "[Add-SSRSItemSecurity()] Creating Proxy, connecting to : $webServiceUrl"
    $ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential

    $type = $ssrsProxy.GetType().Namespace;
    $policyType = "{0}.Policy" -f $type;
    $roleType = "{0}.Role" -f $type;

    Write-Host "[Add-SSRSItemSecurity()] Retrieving all existing policies."
    $policies = $ssrsProxy.GetPolicies($itemPath, [ref]$inherit);

    $a = 1;
    foreach($policy in $policies)
    {

        foreach($r in $policy.Roles)
        {
            $msg = "[Add-SSRSItemSecurity()]  Existing Policy # {0} Group Name: {1}, Role: {2}" -f $a, $policy.GroupUserName, $r.Name
            Write-Host $msg
        }
        $a+=1;
    }

    $msg = "[Add-SSRSItemSecurity()] Total Existing Policies: " + $policies.Length;
    Write-Host $msg

    $Policy = $policies | 
    Where-Object { $_.GroupUserName -eq $groupUserName } | 
    Select-Object -First 1

    if (-not $Policy) {
        $Policy = New-Object ($policyType)
        $Policy.GroupUserName = $GroupUserName
        $Policy.Roles = @()
        $Policies += $Policy
        $msg = "[Add-SSRSItemSecurity()] Adding new policy: '{0}'" -f $GroupUserName
        Write-Host $msg
    }

    $r = $Policy.Roles |
        Where-Object { $_.Name -eq $role } |
        Select-Object -First 1
    if (-not $r) {
        $r = New-Object ($roleType)
        $r.Name = $role
        $Policy.Roles += $r
        $msg = "[Add-SSRSItemSecurity()] Adding new role: '{0}'" -f $role
        Write-Host $msg
    }

    #Set the policies
    $ssrsProxy.SetPolicies($itemPath,$policies);

}

$ReportServerUriSource = 'http://server1/ReportServer/ReportService2010.asmx?wsdl'
$ReportServerUriTarget = 'http://server2/ReportServer/ReportService2010.asmx?wsdl'
$rsProxy =       New-WebServiceProxy -Uri $ReportServerUriSource -UseDefaultCredential
$SourceItems = $rsProxy.ListChildren("/", $true) | `
         SELECT TypeName, Path, ID, Name



 $targetFolder = $rsProxyTarget.GetPolicies($SourceItem.Path,[ref]$InheritParent)     
 foreach($SourceItem in $SourceItems)
 {
  $Policies = $rsProxy.GetPolicies($SourceItem.Path, [ref]$InheritParent)
      if($InheritParent -eq $false)
     {
 $x = $Policies.Count
 $y = 0
 while($y -lt $x)
 {
     $name = ($Policies[$y]).Roles[0].Name

     $z = ($Policies[$y]).Roles.Count
     $q = 0

 while($q -lt $z)
     {
  Add-SSRSItemSecurity -webServiceUrl $ReportServerUriTarget `
                     -itemPath $SourceItem.Path`
                     -groupUserName ($Policies[$y]).GroupUserName`
                     -role ($Policies[$y]).Roles[$q].Name
         $q++
  }

     $y++
 }
}
}

Related Solutions

Conditional formatting in SSRS 2008r2

Here's how you hide How to: Hide an Item (Reporting Services)

Here's the expression

=IIF( Fields!yourField1.Value = 0  and Fields!yourField2.Value = 0 
  and Fields!yourField3.Value = 0  , True, False )

SSRS pivot dynamic columns

As suggested in my comment I would not PIVOT the data within the SQL and let SSRS do the PIVOTING by using a matrix. Why? Because your report will not be flexible should you choose to allow users to specify their own number of periods.

This kind of report is much easier with a Date dimension table so you can easily figure out the week start/end date between a set of dates, this is what happens in the first part of the solution but if you had a Date table permanently stored it would be as case of simply querying that table.

The CTEs named N1 through to N5 are creating a tally table, a table full of numbers, which I use to generate the CTE DateAndWeek which contains a list of dates and the week start date, based on the @@DATEFIRST value.

WITH N1 AS
(
    SELECT 1 AS N
    UNION ALL
    SELECT 1 AS N
)
, N2 AS
(
    SELECT A.N FROM N1 AS A
    CROSS JOIN N1 AS B
)
, N3 AS
(
    SELECT A.N FROM N2 AS A
    CROSS JOIN N2 AS B
)
, N4 AS
(
    SELECT A.N FROM N3 AS A
    CROSS JOIN N3 AS B
)
, N5 AS
(
    SELECT A.N FROM N4 AS A
    CROSS JOIN N4 AS B
)
, DateAndWeek AS
(
    SELECT TOP 365
        DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101') AS [Date]
        , DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
            + (1-DATEPART(WEEKDAY, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101'))), '20140101') AS WeekStartDate
    FROM
        N5
)

Depending on your requirements you may or may not need to create dummy/blank rows for items which have no sales during the period and this is what happens in the ReportTemplate CTE. It returns a dataset with all the weeks within the period specified and performs a 'CROSS JOIN' against all unique ItemID and Description values.

, ReportTemplate AS
(
    SELECT DISTINCT
        WeekStartDate
        , ItemId
        , Description

    FROM
        DateAndWeek
        CROSS JOIN
        (
            SELECT DISTINCT
                ItemID
                , Description

            FROM
                #Sales
        ) AS Sales

    WHERE
        [Date] BETWEEN @StartDate AND @EndDate
)

The final CTE perform the AGGREGATION of your sale data by JOINING to the DateAndWeek CTE to figure out which week the sale belongs in.

, SalesInPeriod AS
(
    SELECT
        ItemID
        , WeekStartDate
        , SUM(QTYOrdered) AS Total

    FROM
        #Sales
        JOIN DateAndWeek
            ON  CAST(#Sales.ReceiptDate AS DATE) = DateAndWeek.Date

    WHERE
        DateAndWeek.Date BETWEEN @StartDate AND @EndDate

    GROUP BY
        ItemID
        , WeekStartDate
)

Finally, LEFT JOIN the ReportTemplate and SalesInPeriod CTEs to get the result set for your report.

Here is the full solution

IF OBJECT_ID('tempdb..#Sales', 'U') IS NOT NULL
    DROP TABLE #Sales
GO

DECLARE
    @StartDate      DATE = DATEADD(WEEK, -6, CAST(GETDATE() AS DATE))
    , @EndDate      DATE = CAST(GETDATE() AS DATE)


CREATE TABLE #Sales
(
    ID              INT
    , ItemID        NVARCHAR(6)
    , [Description] NVARCHAR(10)
    , QTYOrdered    NUMERIC
    , ReceiptDate   DATETIME
)

INSERT INTO #Sales
VALUES 
(1, N'AAL100', 'Ice Cream', 21 , DATEADD(WEEK, -1, GETDATE()))
, (2, N'AAL100', 'Ice Cream', 40 , DATEADD(WEEK, -1, GETDATE()))
, (3, N'AAL100', 'Ice Cream', 50 , DATEADD(WEEK, -3, GETDATE()))
, (4, N'AAL101', 'Burger', 43 , DATEADD(WEEK, -3, GETDATE()))
, (5, N'AAL101', 'Burger', 42 , DATEADD(WEEK, -5, GETDATE()))
, (6, N'AAL101', 'Burger', 76 , DATEADD(WEEK, -5, GETDATE()))
, (7, N'AAL102', 'Pizza', 76 , DATEADD(WEEK, -7, GETDATE()))
, (8, N'AAL102', 'Pizza', 34 , DATEADD(WEEK, -7, GETDATE()))
, (9, N'AAL103', 'Burrito', 56 , DATEADD(WEEK, -9, GETDATE()))
, (10, N'AAL103', 'Burrito', 75 , DATEADD(WEEK, -9, GETDATE()))
;

WITH N1 AS
(
    SELECT 1 AS N
    UNION ALL
    SELECT 1 AS N
)
, N2 AS
(
    SELECT A.N FROM N1 AS A
    CROSS JOIN N1 AS B
)
, N3 AS
(
    SELECT A.N FROM N2 AS A
    CROSS JOIN N2 AS B
)
, N4 AS
(
    SELECT A.N FROM N3 AS A
    CROSS JOIN N3 AS B
)
, N5 AS
(
    SELECT A.N FROM N4 AS A
    CROSS JOIN N4 AS B
)
, DateAndWeek AS
(
    SELECT TOP 365
        DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101') AS [Date]
        , DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
            + (1-DATEPART(WEEKDAY, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101'))), '20140101') AS WeekStartDate
    FROM
        N5
)
, ReportTemplate AS
(
    SELECT DISTINCT
        WeekStartDate
        , ItemId
        , Description

    FROM
        DateAndWeek
        CROSS JOIN
        (
            SELECT DISTINCT
                ItemID
                , Description

            FROM
                #Sales
        ) AS Sales

    WHERE
        [Date] BETWEEN @StartDate AND @EndDate
)
, SalesInPeriod AS
(
    SELECT
        ItemID
        , WeekStartDate
        , SUM(QTYOrdered) AS Total

    FROM
        #Sales
        JOIN DateAndWeek
            ON  CAST(#Sales.ReceiptDate AS DATE) = DateAndWeek.Date

    WHERE
        DateAndWeek.Date BETWEEN @StartDate AND @EndDate

    GROUP BY
        ItemID
        , WeekStartDate
)

SELECT
    ReportTemplate.WeekStartDate
    , ReportTemplate.ItemID
    , ReportTemplate.Description
    , SalesInPeriod.Total

FROM
    ReportTemplate
    LEFT JOIN SalesInPeriod
        ON  ReportTemplate.ItemID = SalesInPeriod.ItemID
            AND ReportTemplate.WeekStartDate = SalesInPeriod.WeekStartDate

Best Answer

Related Solutions

Conditional formatting in SSRS 2008r2

SSRS pivot dynamic columns

Related Question