Archive

Posts Tagged ‘SSRS’

[Powershell] Set user permission in SSRS Item (SQL Reporting Server) using powershell

February 22, 2012 3 comments

Combining both (article1 and article2), i was able to come up with a powershell script to set user permission in SQL Reporting Services (SSRS)

<#
.SYNOPSIS
	Set user permissions in SQL Reporting Services using Web Service

.DESCRIPTION
	Set user permissions in SQL Reporting Services using Web Service

.EXAMPLE
	Add-SSRSItemSecurity -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -itemPath "MyReportFolder" -groupUserName RPAULO\User1 -role Browser

.EXAMPLE
	Add-SSRSItemSecurity -url "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -itemPath "MyReportFolder" -u RPAULO\User1 -r "Content Manager"

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

}


[Powershell] How to Install/deploy SSRS (rdl files) using Powershell

February 21, 2012 19 comments

Since Powershell v2.0 introduce the new cmdlet New-WebServiceProxy, it’s no longer needed to generate a class library to deploy/undeploy SSRS file (RDL), below are two powershell scripts Install-SSRSRDL and Uninstall-SSRSRDL that can be useful when doing an automated deployment or silent installation of rdl files to Reporting Services.

Install SSRS RDL Script:


<#
.SYNOPSIS
	Installs an RDL file to SQL Reporting Server using Web Service

.DESCRIPTION
	Installs an RDL file to SQL Reporting Server using Web Service

.NOTES
	File Name: Install-SSRSRDL.ps1
	Author: Randy Aldrich Paulo
	Prerequisite: SSRS 2008, Powershell 2.0

.PARAMETER reportName
	Name of report wherein the rdl file will be save as in Report Server.
	If this is not specified it will get the name from the file (rdl) exluding the file extension.

.PARAMETER force
	If force is specified it will create the report folder if not existing
	and overwrites the report if existing.

.EXAMPLE
	Install-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -rdlFile "C:\Report.rdl" -force

.EXAMPLE
	Install-SSRSRDL "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" "C:\Report.rdl" -force

.EXAMPLE
	Install-SSRSRDL "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" "C:\Report.rdl" -force -reportName "MyReport"

.EXAMPLE
	Install-SSRSRDL "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" "C:\Report.rdl" -force -reportFolder "Reports" -reportName "MyReport"

#>
function Install-SSRSRDL
(
	[Parameter(Position=0,Mandatory=$true)]
	[Alias("url")]
	[string]$webServiceUrl,

	[ValidateScript({Test-Path $_})]
	[Parameter(Position=1,Mandatory=$true)]
	[Alias("rdl")]
	[string]$rdlFile,

	[Parameter(Position=2)]
	[Alias("folder")]
	[string]$reportFolder="",

	[Parameter(Position=3)]
	[Alias("name")]
	[string]$reportName="",

	[switch]$force
)
{
	$ErrorActionPreference="Stop"

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

	if($force)
	{
		#Check if folder is existing, create if not found
		try
		{
			$ssrsProxy.CreateFolder($reportFolder, $reportPath, $null)
			Write-Host "[Install-SSRSRDL()] Created new folder: $reportFolder"
		}
		catch [System.Web.Services.Protocols.SoapException]
		{
			if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
			{
				Write-Host "[Install-SSRSRDL()] Folder: $reportFolder already exists."
			}
			else
			{
				$msg = "[Install-SSRSRDL()] Error creating folder: $reportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
				Write-Error $msg
			}
		}

	}

	#Set reportname if blank, default will be the filename without extension
	if($reportName -eq "") { $reportName = [System.IO.Path]::GetFileNameWithoutExtension($rdlFile);}
	Write-Host "[Install-SSRSRDL()] Report name set to: $reportName"

	try
	{
		#Get Report content in bytes
		Write-Host "[Install-SSRSRDL()] Getting file content (byte) of : $rdlFile"
		$byteArray = gc $rdlFile -encoding byte
		$msg = "[Install-SSRSRDL()] Total length: {0}" -f $byteArray.Length
		Write-Host $msg

		$reportFolder = $reportPath + $reportFolder
		Write-Host "[Install-SSRSRDL()] Uploading to: $reportFolder"

		#Call Proxy to upload report
		$warnings = $ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null)
		if($warnings.Length -eq $null) { Write-Host "[Install-SSRSRDL()] Upload Success." }
		else { $warnings | % { Write-Warning "[Install-SSRSRDL()] Warning: $_" }}
	}
	catch [System.IO.IOException]
	{
		$msg = "[Install-SSRSRDL()] Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
		Write-Error msg
	}
	catch [System.Web.Services.Protocols.SoapException]
	{
		$msg = "[Install-SSRSRDL()] Error while uploading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Detail.InnerText
		Write-Error $msg
	}

}

Uninstall SSRS RDL Script:


<#
.SYNOPSIS
	Uninstalls an RDL file from SQL Reporting Server using Web Service

.DESCRIPTION
	Uninstalls an RDL file from SQL Reporting Server using Web Service

.NOTES
	File Name: Uninstall-SSRSRDL.ps1
	Author: Randy Aldrich Paulo
	Prerequisite: SSRS 2008, Powershell 2.0

.EXAMPLE
	Uninstall-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -path "MyReport"

.EXAMPLE
	Uninstall-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -path "Reports/Report1"

#>
function Uninstall-SSRSRDL
(
	[Parameter(Position=0,Mandatory=$true)]
	[Alias("url")]
	[string]$webServiceUrl,

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

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

	#Set Report Folder
	if(!$reportPath.StartsWith("/")) { $reportPath = "/" + $reportPath }

	try
	{

		Write-Host "[Uninstall-SSRSRDL()] Deleting: $reportPath"
		#Call Proxy to upload report
		$ssrsProxy.DeleteItem($reportPath)
		Write-Host "[Uninstall-SSRSRDL()] Delete Success."
	}
	catch [System.Web.Services.Protocols.SoapException]
	{
		$msg = "[Uninstall-SSRSRDL()] Error while deleting report : '{0}', Message: '{1}'" -f $reportPath, $_.Exception.Detail.InnerText
		Write-Error $msg
	}

}

Install-SSRSRDL Powershell Script
Uninstall-SSRSRDL Powershell Script

For permission check this blog: https://randypaulo.wordpress.com/2012/02/22/powershell-set-user-permission-in-ssrs-item-sql-reporting-server-using-powershell/

SSRS Chart Label Missing (X/Y Axis missing)

February 20, 2012 Leave a comment

When designing an SSRS Report using Chart you may notice that some of the labels whether in X or Y axis are not being displayed, this is because it’s so smart that it detects what you actually want (not!). 

To display all labels follow the steps below:

Right click either the X or Y axis, Click Properties:

Set the Interval to 1:

Viola!

 

Categories: Microsoft SQL Server Tags:

SSRS – Calling Reporting Services using WCF and using the HTML output for email notification

October 27, 2011 6 comments

Recently we had a project that requires us to use Reporting Server for email notification. In short,  the HTML output of the Report is assigned to the body of the email notification.

1) First step is to create a proxy library utility containing reference to Reporting Server Web Service. To do this Click Add Service Reference and set url to:

 http://{ReportServerName}/ReportServer/ReportExecution2005.asmx, In the collection type set it to System.Collections.Generic.List

Note: Version of SSRS is 2005

Update!: Problem with Authorization:

“The HTTP request is unauthorized with client authentication scheme ‘Negotiate’. The authentication header received from the server was ‘NTLM’.”

To fix problem with Authorization issue in LoadReport method:

1. Added new method called GetDefaultBinding.

2.Also a new parameter in Export method named  useDefaultBinding with value set to false was added. If this value is set to true, it would use the binding from GetDefaultBinding and will use DefaultNetworkCredentials.

Note: Network credential is not being used when useDefaultBinding is set to true.

Updated code is as follows:

  /// <summary>
    /// Utility class that renders and exports a SQL Reporting Services report into the specified output format.
    /// </summary>
    public static class ReportExporter
    {
        private static string GetExportFormat(ExportFormat f)
        {
            switch (f)
            {
                case ExportFormat.XML: return "XML";
                case ExportFormat.CSV: return "CSV";
                case ExportFormat.Image: return "IMAGE";
                case ExportFormat.PDF: return "PDF";
                case ExportFormat.MHTML: return "MHTML";
                case ExportFormat.HTML4: return "HTML4.0";
                case ExportFormat.HTML32: return "HTML3.2";
                case ExportFormat.Excel: return "EXCEL";
                case ExportFormat.Word: return "WORD";

                default:
                    return "PDF";
            }
        }

        /// <summary>
        /// Exports a Reporting Service Report to the specified format using Windows Communication Foundation (WCF) endpoint configuration specified.
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="format"></param>
        /// <param name="output">rendering output result in bytes</param>
        /// <param name="extension">output format file extension</param>
        /// <param name="mimeType">output MIME type</param>
        /// <param name="encoding">output encoding</param>
        /// <param name="warnings">warnings (if any)</param>
        /// <param name="streamIds">stream identifiers for external resources (images, etc) that are associated with a given report</param>
        /// <param name="dataSourceCredentials"></param>
        public static void Export(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters,
            ExportFormat format, out byte[] output, out string extension, out string mimeType, out string encoding, out Warning[] warnings, out string[] streamIds, DataSourceCredentials[] dataSourceCredentials=null, bool UseDefaultBinding=false)
        {

            Binding binding;
            if (UseDefaultBinding)
            {
                binding = GetDefaultBinding();
            }
            else
            {
                binding = GetBinding();
            }

            using (var webServiceProxy = new ReportExecutionServiceSoapClient(binding, new EndpointAddress(url)))
            {
                webServiceProxy.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

                if (UseDefaultBinding)
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
                }
                else
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = clientCredentials;
                }

                // Init Report to execute
                ServerInfoHeader serverInfoHeader;
                ExecutionInfo executionInfo;

                try
                {

                    ExecutionHeader executionHeader = webServiceProxy.LoadReport(null, report, null, out serverInfoHeader, out executionInfo);

                    // Attach Report Parameters
                    webServiceProxy.SetExecutionParameters(executionHeader, null, parameters, null, out executionInfo);

                    //Set Credentials
                    if(dataSourceCredentials!=null)
                        webServiceProxy.SetExecutionCredentials(executionHeader, null, dataSourceCredentials, out executionInfo);

                    // Render
                    webServiceProxy.Render(executionHeader, null, GetExportFormat(format), null, out output, out extension, out mimeType, out encoding, out warnings, out streamIds);
                }
                catch (FaultException faultExc)
                {
                    throw new Exception(string.Format("Encountered error while generating the report: '{0}', Report Url: '{1}', Web Service Url: '{2}' Message: '{3}'",  reportName, report, url, faultExc.Message ), faultExc);
                }
            }
        }

        /// <summary>
        /// Export a Reporting Services to HTML
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="dataSourceCredentials"></param>
        /// <returns></returns>
        public static string ExportToHTML(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters, DataSourceCredentials[] dataSourceCredentials = null, bool UseDefaultBinding = false)
        {
            Warning[] warn;
            string[] p;

            byte[] output;
            string extension, mimeType, encoding;

            //Get HTML
            ReportExporter.Export(url,
                                  clientCredentials,
                                  reportName,
                                  report,
                                  parameters.ToArray(),
                                  ExportFormat.HTML4,
                                  out output,
                                    out extension,
                                    out mimeType,
                                    out encoding, out warn, out p, dataSourceCredentials, UseDefaultBinding
                                    );

            System.Text.Encoding enc = System.Text.Encoding.GetEncoding("UTF-8");
            string html = enc.GetString(output);
            return html;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
              {
                  MaxArrayLength = 2147483647,
                  MaxBytesPerRead = 2147483647,
                  MaxDepth = 2147483647,
                  MaxNameTableCharCount = 2147483647,
                  MaxStringContentLength = 2147483647
              };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;
            return binding;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetDefaultBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
            {
                MaxArrayLength = 2147483647,
                MaxBytesPerRead = 2147483647,
                MaxDepth = 2147483647,
                MaxNameTableCharCount = 2147483647,
                MaxStringContentLength = 2147483647
            };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
            return binding;
        }
        #endregion
    }

Updated Unit Test to use default bindings:

[TestMethod]
        public void GetHTMLReportTest()
        {

            //Set network credentials
            string userName = "NotUsed";
            string password = "NotUsed";
            string domain = "NotUsed";
            System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(userName, password, domain);
            string outputPath = @"C:\TestFolder";

            //Url
            string path = "/MyReport/Invoice";

            //Parameters
            List<ParameterValue> parameters = new List<ParameterValue>();
            parameters.Add(new ParameterValue { Name = "AcctId", Value = "0001" });

            //Get HTML
            string html = ReportExporter.ExportToHTML("http://HypV-Rpaulo/ReportServer/ReportExecution2005.asmx",
                                  credentials,
                                  "My invoice report",
                                  path ,
                                  parameters.ToArray(),UseDefaultBinding:true
                                  );

            Assert.AreEqual(true, html.Length > 0, "No HTML generated");
            Console.WriteLine(html);
            System.IO.File.WriteAllText(outputPath + "result.html", html);

        }

The library can be used in BizTalk (deployed in GAC) , SSIS 2005 or 2008(deployed in GAC, .NET version is <=3.5 and is copied to %Windir%\Microsoft.NET\Framework\v2.0.50727 folder to be able to referenced in a SSIS Package..

%d bloggers like this: