How to add a custom renderer for SSRS 2012 with SharePoint/Native Mode

A rendering extension is a component or module of a report server that transforms report data and layout information into a device-specific format. SQL Server Reporting Services includes seven rendering extensions: HTML, Excel, Word, CSV or Text, XML, Image, and PDF. You can create additional rendering extensions to generate reports in other formats.

Writing Custom Rendering Extensions

Before you decide to create a custom rendering extension, you should evaluate simpler alternatives.

You can:
• Customize rendered output by specifying device information settings for existing extensions.
• Add custom formatting and presentation features by combining XSL Transformations (XSLT) with the output of the XML rendering format.

Writing a custom rendering extension is difficult. A rendering extension must typically support all possible combinations of report elements and requires that you implement hundreds of classes, interfaces, methods, and properties.

If you must render a report in a format that is not included with Reporting Services and decide to write your own managed code implementation of a rendering extension, the rendering extension code must implement the Microsoft.ReportingServices.OnDemandReportRendering.IRenderingExtension interface, which is required by the by the report server.

Build the custom extension renderer dll

Open the appropriate project for your environment. The 2012 project is usable for both SSRS 2008 and 2012. Because this is a normal .NET assembly project, BIDS is not necessary.

1. Add required references. E.g.-
Microsoft.ReportingServices.ExcelRendering.dll
Microsoft.ReportingServices.ProcessingCore.dll
Microsoft.ReportingServices.Interfaces.dll

all of these are in [SQL Reporting Services folder]\ReportServer\Bin\ on the SQL Server machine. I found it easiest to copy the entire contents of that bin folder to the development machine for investigation and future use in extensions.

The ExcelRendering and ProcessingCore will give a warning about a newer .NET version, you can safely ignore this. (see http://support.microsoft.com/kb/2722683).
Implementing the IRenderingExtension Interface

The rendering extension takes the results from a report definition that is combined with the actual data and renders the resulting data to a format that is useable. The transformation of the combined data and formatting is done by using a common language runtime (CLR) class that implements IRenderingExtension. This transforms the object model into an output format that is consumable by a viewer, printer, or other output target.
The IRenderingExtension has three methods that must be implemented:
• Render – renders the report.
• RenderStream – renders a specific stream from the report.
• GetRenderingResource – gets additional information, such as icons, that are required for the report.

Sample code for Render Method-

--

public bool Render(Microsoft.ReportingServices.OnDemandReportRenderingReport report, System.Collections.SpecializedNameValueCollection reportServerParameters, System.Collections.SpecializedNameValueCollection deviceInfo, System.Collections.SpecializedNameValueCollection clientCapabilities, ref System.Collections.Hashtable renderProperties, CreateAndRegisterStream createAndRegisterStream)
{

	//Reader for excel file
	IExcelDataReader excelReader = null;
	//Excel file
	FileStream fileStream = null;
	try
	{
		//Byte array to copy Excel to Memory Stream
		byte[] buffer = new byte[32768];

		//Final output stream which wil write all the data to CSV file
		Stream outputStream = createAndRegisterStream(report.Name, "CSV", Encoding.UTF8, "text/csv", true, StreamOper.CreateAndRegister); 

		using (MemoryStream outputMemoryStream = new MemoryStream())
		{
				foreach (SubRenderer sr in subRenderers)
				{
					if (sr.Render(report, reportServerParameters, deviceInfo, clientCapabilities, ref renderProperties))
					{
						while (true)
						{
							int read = sr.RegisteredStream.Read(buffer, 0, buffer.Length);
							if (read <= 0)
							{
							break;
							}
							outputMemoryStream.Write(buffer, 0, read);
						}
						sr.CloseStreams();
					}
				}

				//Write to the excel file
				using (Stream file = File.Create(excelFileName))
				{
				outputMemoryStream.WriteTo(file);
				}
		}

		//Read the excel file into a Dataset
		fileStream = File.Open(excelFileName, FileMode.Open, FileAccess.Read);
		excelReader = ExcelReaderFactory.CreateBinaryReader(fileStream, ReadOption.Loose);
		DataSet dataSet = excelReader.AsDataSet(true); 

		string connectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

		string query = "SELECT * from [" + report.Name + "$]";
		DataTable dt = new DataTable();
		DataSet ds = new DataSet();
		
		using (OleDbConnection conn = new OleDbConnection(connectionString))
		{
			conn.Open();
			using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn))
			{
				dataAdapter.Fill(ds);
				dt = ds.Tables[0];
			}
			conn.Close();
		}


		foreach (var column in ds.Tables[0].Columns.Cast<DataColumn>().ToArray())
		{

		if (ds.Tables[0].AsEnumerable().All(dr => dr.IsNull(column)))
		ds.Tables[0].Columns.Remove(column);
		}

		//Convert this DataSet to csv file
		string csvText = converToCSV(ds, report.Name);
		
		//Write the CSV data to a file
		StreamWriter csvStreamWriter = new StreamWriter(csvFileName, false);
		csvStreamWriter.Write(csvText);
		csvStreamWriter.Close();

		//Now read the output of local CSV file and write to the output stream
		using (FileStream csvFileOutput = File.Open(csvFileName, FileMode.Open, FileAccess.Read))
		{
			using (MemoryStream newMemoryStream = new MemoryStream())
			{
				newMemoryStream.SetLength(csvFileOutput.Length);
				csvFileOutput.Read(newMemoryStream.GetBuffer(), 0, (int)csvFileOutput.Length);
				newMemoryStream.WriteTo(outputStream);
			}
		}
	}

	catch (Exception ex)
	{
		//Close all the connections
		excelReader.Close();
		fileStream.Close();
		throw ex;
	}
	finally
	{
		//Close all the connections
		excelReader.Close();
		fileStream.Close();
	}

	//Finally return false
	return false;
}


--

Build the project.

1. Move [Your].dll from the output folder to [SQL Reporting Services folder]\ReportServer\Bin\.You may notice there are a -lot- of other dlls in the output folder. These are all reportserver related and do not need to be copied to the reportserver. (In fact, they originated there).

Server Configuration you need perform for SharePoint Mode

You can modify SSRS 2012 with SharePoint Mode using PowerShell Cmdlets.

To achieve the above functionality we need to modify the config files – rsreportserver.config, and rssrvpolicy.config. Run the below PowerShell Cmdlets on SharePoint command prompt to modify the config files.

--

$apps = Get-SPRSServiceApplication | where {$_.name -like “NAME OF YOUR SSRS APPLICATION“}
$GetName = Get-SPRSExtension -identity $apps -ExtensionType “Render” -name “NAME OF THE CUSTOM RENDERER”

if ($GetName)

{

Remove-SPRSExtension -identity $apps -ExtensionType “Render” -name “NAME OF THE CUSTOM RENDERER”

}

New-SPRSExtension -identity $apps -ExtensionType “Render” -name “NAME OF THE CUSTOM RENDERER” -TypeName “NAME OF THE NEWLY CREATED DLL” -CodeGroup “<CodeGroup class='UnionCodeGroup' version='1′ PermissionSetName='FullTrust' Name=' NAME OF THE CUSTOM RENDERER‘ Description='This code group grants Zip Renderer code full trust.'>

<IMembershipCondition class='UrlMembershipCondition' version='1′ Url='C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting\ReportServer\bin\[YourDLL].dll' />

</CodeGroup>” -ExtensionAttributes “<Visible>True</Visible>” -ServerDirectives “<OverrideNames>

<Name Language='en-US'>CustomRendererName</Name>

</OverrideNames>” 

--

Note- In order to execute the above PowerShell cmdlet, a local farm must exist, and you must have farm-level administrator privileges.

2. Uses below PowerShell to copy the new DLLs to SharePoint hive 14 folders.

--
Copy-Item $DeploymentPackageFolder\[Your].dll "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting\bin"

--

Uses below PowerShell to copy the new DLL to windows assembly folder.

--
$dllpath = "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting\bin\[Your].dll"

[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a") | Out-Null

$publish = New-Object System.EnterpriseServices.Internal.Publish

$assembly = [System.Reflection.Assembly]::LoadFile( $dllpath )

$publickey = $assembly.GetName().GetPublicKey()

$aicstring = [System.Reflection.Assembly]::LoadFile( $dllpath ).FullName

$publish.GacInstall( $dllpath )

--

After running the PowerShell Cmdlets please restart reporting service and reset IIS only if you are not able to view the new rendering extension.Note- Adding new rendering extension parameters only affects rendering operations on the report server. You cannot override rendering extension settings in report preview in Report Designer or Visual Studio.Specifying rendering extension parameters in the configuration files affects rendering extensions globally means all the reports on that server will get the functionality of new renderer.

Server Configuration you need perform for Native Mode

Copy your assembly from your staging location to the bin directory of the report server on which you want to use the rendering extension.

The default location of the report server Bin directory is %ProgramFiles%\Microsoft SQL Server\MSRS11.\Reporting Services\ReportServer\Bin.

1. After the assembly file is copied, open the rsreportserver.config file. The rsreportserver.config file is also located in the report server bin directory. You need to make an entry in the configuration file for your extension assembly file. You can open the file with Visual Studio or a simple text editor.
2. Locate the Render element in the Rsreportserver.config file. An entry for your newly created extension should be made in the following location:

--

<Extension Name="CustomRendererName" Type="[Name Space].[Class Name],[DLL NAME]" Visible="true">

<OverrideNames>

<Name Language="en-US">CSVExtension</Name>

</OverrideNames>

</Extension>

--

Add an entry for your rendering extension in rssrvpolicy.config.

--

<CodeGroup>

class="UnionCodeGroup"

version="1"

PermissionSetName="FullTrust"

Name="[Name of YourDLL]"

Description="This code group grants your dll full trust.">

<IMembershipCondition

class="UrlMembershipCondition"

version="1"

Url="C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\[YourDLL].dll" />

</CodeGroup>

--

The value for Name is the unique name of the rendering extension. The value for Type is a comma-separated list that includes an entry for the fully qualified namespace of your IRenderingExtension implementation, followed by the name of your assembly (not including the .dll file extension). By default, rendering extensions are visible. To hide an extension from user interfaces, such as Report Manager, add a Visible attribute to the Extension element, and set it to false.

Verifying the deployment

You can open Report Manager and verify that your extension is included in the list of available export types for a report.

Keep Learning.

Pawan Khowal

MSBISkills.com