Introduction
In Reporting Services’ Report
Manager you can export reports as Excel, CSV, PDF, and other
formats. But what if you want to add a watermark to every
PDF? Or, change an exported Excel's sheet names from 'Sheet2' to something meaningful? Or
do any kind of processing for that matter on an existing
(built-in) Rendering Extension?
This article shows how to make an SQL Server Reporting Services (SSRS) Rendering Extension that can change the output of other renderers before it is sent to the client - in this case, it compresses one or more reports in a .zip file.
There's also a general explanation of deployment and troubleshooting that is useful for any Rendering Extension. I have tried to make things as clear and complete as possible. Some documentation on deployment is out there but spread out all over the place (particularly on MSDN). This article should help anyone get started with Rendering Extensions.
Table of Contents:
List of MSDN links:
- Rendering Extension
- Configuration, Device Info
- Guide
In one of my projects I created a report
that contained tens of megabytes of data. This report was
later scheduled to be sent daily via e-mail. That is of
course way too large to e-mail, but if you want to send data to customers you
often have no choice.
So I started looking for a smaller
alternative. The smallest existing export available is .CSV, however you can only export data
structured as tables in a .CSV file. Also dealing with .CSVs is just not practical except when you set up automated data transfer.
Then I read this
excellent article about rendering extensions by one of my fellow
countrymen: http://www.broes.nl/2011/02/pdf-watermarkbackground-rendering-extension-for-ssrs-part-1/ This article details the modification of an exported pdf (watermark).
So I thought, if you can change a pdf, surely you can also zip the output and return a zip file? The basic idea of this ZipRenderer is the same: Get a reference to an existing renderer, make it render a report. Get the output and do something with it.
Although the article on broes.nl is a great
start (and I recommend you read it, too) there were still a
lot of things to find out. For example, the PdfRenderer that was available in 2005, isn't anymore from 2008 onward, so the method from the article doesn't work anymore.
Using the code
This part is about deployment; the steps below apply to any rendering extension. (except those that are obviously related to the ZipRenderer)
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.
- Add references to:
Microsoft.ReportingServices.ExcelRendering.dll
Microsoft.ReportingServices.ProcessingCore.dll
Microsoft.ReportingServices.Interfaces.dll
the SSRS 2005 project also needs
Microsoft.ReportingServices.CsvRenderering.dll
Microsoft.ReportingServices.ImageRenderering.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)
- For the zip
functionality we need the DotNetZip library:
http://dotnetzip.codeplex.com/releases Download the
latest Runtime, extract Ionic.Zip.dll from one of the
bin/Release folders (zip-v#.# contains the normal Zip
compression) and add a reference to
Ionic.Zip.dll.
- Build the project.
- Move ZipRenderer.dll and Ionic.Zip.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).
Now there is some configuring to do to make our extension
show up in the report manager. Thanks to this article that
isn’t too hard.
http://www.broes.nl/2011/02/pdf-watermark-background-rendering-extension-for-ssrs-part-2/
- Add this to [SQL Reporting Services
folder]\ReportServer\rsreportserver.config directly above
the
</Render> tag:
<Extension Name="ZIPEXCEL" Type="ZipRenderer.ZipRenderingProvider,ZipRenderer">
<Configuration>
<DeviceInfo>
<ZipRenderer description ="Zipped Excel">
<SubRenderers>
<SubRenderer extension="xls" format="EXCEL" />
</SubRenderers>
</ZipRenderer>
</DeviceInfo>
</Configuration>
</Extension>
(Note the custom tags inside DeviceInfo.)
- And add this to [SQL Reporting Services
folder]\ReportServer\rssrvpolicy.config:
-
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="Zip Renderer"
Description="This code group grants Zip Renderer code full trust.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="[SQL Reporting Services folder]\ReportServer\bin\ZipRenderer.dll" />
</CodeGroup>
Place this XML in the config file as explained on MSDN:
For extensions and custom assemblies that you develop, it is recommended that you place your custom code groups directly below the existing entry
for the URL membership "$CodeGen$/*"
This is an UrlMembershipCondition that uses a path, or URL, to the DLL. Remember to change the above Url to the correct path to ZipRenderer.dll!
You can also use a StrongNameMembershipCondition, see Broes’ article part 2
again. It takes some more actions to set up. Take a look there if you want to deploy your assembly in that way.
- Finally, restart Reporting Services service to apply the
changes to the .config files.
*newer versions of SSRS detect changes to the config files
and automatically restart / reconfigure the service. This is visible in the Application Event log as an Information event like "The
RSReportServer.config file has been modified".
Now if everything is set up OK, you should see the extension
show up as "Zipped excel" in the
export drop down menu when you run a report. If that is the case, congratulations, you have just deployed the extension. Click the export link and you will download a zip file
containing a .xls.
Now, a lot of things could have gone wrong - I think I have seen them all - here are some tips if it
doesn't show up or work:
- If the extension does not show up at all, most likely the
configuration files were not changed correctly (wrong path to
ZipRenderer.dll), the files were not saved to disk or not applied. Make sure they are saved, then restart
SQL Server reporting services. In SSRS 2005 it might also help
to restart the entire website from IIS.
- Also check the Application Event log for error messages such as "Report Server (MS SQL Server) cannot load the
ZIPEXCEL extension." which will point you in the
right direction. There may be useful information in the
stack trace of ASP.NET Warning events nearby the Error event.
- Check the latest log files folder in [Reporting
Services Folder]\LogFiles. Sort by date modified and open
the latest log files. Then scroll down and look for a stack trace.
- If you get a Server error page when selecting ZipExcel, you can
also find details in the page itself. Open the report page on
the server to see the error details if they aren't shown.
If you have Visual Studio running on your SSRS machine, debugging is easy, however this is not documented on MSDN.
(The page Debugging Delivery Extensions comes closest)
First, copy the ZipRenderer.pdb file to the server. Otherwise the debugger will complain that Symbols are not loaded. To start debugging:
- Run Visual Studio as Administrator and open the project
- Select Tools - Attach to Process.
- Check "Show processes from all users" and "Show process from all sessions" if necessary
- Find and select the executable that is hosting the ZipRenderer
DLL
- SSRS 2008+: ReportServicesService.exe
- IIS6: w3wp.exe
- IIS7: aspnet_wp.exe
- Attach.
If you cannot run Visual Studio on the server, you can use Remote Debugging. How to set it up is explained here on MSDN: Remote Debugging Setup. How much work this is depends on your server configuration. I'll leave that for other dedicated articles to explain.
Points of Interest
Now.. finally it is time for all the nifty bits of code in the project. The actual zipping process in the code is quite small - it is getting to the renderers and the right datastreams that's a challenge.
Problem 1: read the output of the built-in ExcelRenderer
One of my main concerns at first was how to get to the output from other built-in renderers, especially the ExcelRenderer. In SSRS 2005, 2008 and 2012 the Excel renderer is public in the Microsoft.ReportingServices.ExcelRendering namespace.
So far so good, you can add
ExcelRenderer to your references, instantiate it and make it render some reports. However:
ExcelRenderer does not behave nicely; it closes the MemoryStream you give it from the
CreateAndRegisterStream callback. There's no way to get the results. Even
ToArray() on the MemoryStream does not work: only part of the excel file is returned and the rest chopped off so you end up with a corrupt file if you try to write it.
I’m no Stream expert but I think it’s behaving this way because the
Excelrenderer calls the CreateAndRegisterStream multiple times; once for every sheet or embedded image.
Solution: the UnclosableMemoryStream
After a lot of hair-pulling I decided to just subclass
MemoryStream to stop the ExcelRenderer from closing my
MemoryStreams. A simple solution, but it worked quite well!
class UnclosableMemoryStream: MemoryStream
{
private bool allowClose;
public bool AllowClose
{
get { return allowClose; }
set { allowClose = value; }
}
public override void Close()
{
if (AllowClose)
base.Close();
}
}This stream will not close until you allow it to.. So with this class in place you can pass instances of it to the
ExcelRenderer in the CreateAndRegisterStream callback and still read the contents later.
Problem 2: Multiple streams
With the
MemoryStream issue solved, something also needs to be done about the multiple streams the ExcelRenderer needs.
The renderer's
CreateAndRegisterStream is called only once with the StreamOper.CreateAndRegister parameter value. That is the main Stream that contains all of the excel contents in the end so that is the one we will store as
RegisteredStream.
Below the Excel version of CreateAndRegisterStream.
(Note: The actual UnclosableMemoryStream instance is created in the wrapper class
CreateAndRegisterStreamUnclosableMemoryStream. This class also stores the parameters for the call to
CreateAndRegisterStream for later use in the Render method.)
public Stream IntermediateCreateAndRegisterStreamExcel(
string name,
string extension,
Encoding encoding,
string mimeType,
bool willSeek,
StreamOper operation)
{
CreateAndRegisterStreamStream crss =
new CreateAndRegisterStreamUnclosableMemoryStream(
name, extension, encoding, mimeType, willSeek, operation);
intermediateStreams.Add(crss);
if (operation == StreamOper.CreateAndRegister)
RegisteredStream = crss.Stream;
return crss.Stream;
} You may have noticed all renderers including the existing ones can be configured from
rsreportserver.config. The place to read your own renderer’s configuration and apply it is in IExtension.SetConfiguration:
void IExtension.SetConfiguration(string configuration)
The parameter "configuration" contains the Inner xml of the
<Configuration> tag from rsreportserver.config.
The
IRenderingExtension.Render method also has a configuration parameter called deviceInfo, a NameValueCollection.
DeviceInfo is also a configuration item from rsreportserver.config, but simplified: It contains every direct child of
<DeviceInfo> and its XmlNode.InnerText as the value.
More info on
DeviceInfo on MSDN here and here
UPDATE: Added support for deviceInfo parameters as settings for the zipmodule. See chapter below this one.
For the zip renderer I have set up a configuration format that allows for multiple SubRenderers - as I call them - to be wrapped in the zip file, AND they can have their own separate
DeviceInfo configuration. The contents of each <SubRenderer> are passed through to their respective Renderer objects and used as Configuration string.
An example configuration:
<Extension Name="ZIPEXCELPDF" Type="ZipRenderer.ZipRenderingProvider, ZipRenderer">
<Configuration>
<DeviceInfo>
<ZipRenderer description="Zipped Excel + PDF">
<SubRenderers>
<SubRenderer extension="xls" format="EXCEL" />
<SubRenderer extension="pdf" format="PDF">
<DeviceInfo>
<StartPage>3</StartPage>
<EndPage>4</EndPage>
</DeviceInfo>
</SubRenderer>
</SubRenderers>
</ZipRenderer>
</DeviceInfo>
</Configuration>
</Extension>
An this is how it’s interpreted:
- description="Zipped Excel + PDF" is the name you see in Report Manager export list
- <Subrenderers> tells the extension to use the following SubRenderers:
- SubRenderer: format EXCEL: Use the ExcelRenderer, use “xls” as the file extension
- (No additional DeviceInfo)
- SubRenderer: format PDF: Use the PdfRenderer, use “pdf” as the file extension
- Add additional DeviceInfo when calling the pdfrenderer: Start at page 3, end rendering at page 4 (Yes, I know, it’s nonsense but just for demonstration)
Code to parse the XML:
void IExtension.SetConfiguration(string configuration)
{
this.configuration = configuration;
XmlDocument doc = new XmlDocument();
try
{
doc.LoadXml(configuration);
if (doc.DocumentElement.Name == "DeviceInfo")
{
XmlNode zipRendererNode = doc.DocumentElement.SelectSingleNode("ZipRenderer");
if (zipRendererNode == null)
throw new System.Configuration.ConfigurationErrorsException(
"Missing ZipRenderer node in configuration", doc.DocumentElement);
description = zipRendererNode.Attributes["description"].Value;
subRenderers = new List<SubRenderer>();
foreach (XmlNode zippedReportNode in zipRendererNode.SelectNodes("SubRenderers/SubRenderer"))
{
try
{
subRenderers.Add(SubRenderer.CreateSubRenderer(
zippedReportNode.Attributes["format"].Value,
zippedReportNode.Attributes["extension"].Value,
zippedReportNode.InnerXml));
}
catch (System.Configuration.ConfigurationErrorsException ex)
{
throw new System.Configuration.ConfigurationErrorsException(
String.Format("The SubReport format {0} could not be found",
zippedReportNode.Attributes["format"].Value),
ex, zippedReportNode);
}
}
}
}
catch (XmlException ex)
{
throw new System.Configuration.ConfigurationErrorsException(
"Failed to read configuration data: " + ex.Message, ex);
}
} The actual SubRenderer is determined in SubRenderer.CreateSubRenderer(…).
Note that the InnerXml of the SubRenderer nodes is passed through unchanged to the
SubRenderer; this may or may not contain extra
DeviceInfo. The SubRenderers are responsible for parsing their own configuration string.
Updated: DeviceInfo for the zip process
Following a reader suggestion the included ziprenderer now also supports the following settings for Ionic.zip
CompressionLevel, CompressionMethod, Strategy, Comment, EnableZip64, IgnoreCase, Encryption and Password.
Follow the links for the available values for each configurationitem or have a look at the Ionic.Zip Zip reference
The configuration items do not need to be present however if you enable Encryption, you must also enter a Password value.
Example configuration:
<Extension Name="ZIPEXCELOPENXML" Type="ZipRenderer.ZipRenderingProvider,ZipRenderer">
<Configuration>
<DeviceInfo>
<CompressionLevel>BestCompression</CompressionLevel>
<Encryption>WinZipAes256</Encryption>
<Password>testpass</Password>
<ZipRenderer description ="Zipped Excel 2012">
<SubRenderers>
<SubRenderer extension="xlsx" format="EXCELOPENXML">
</SubRenderer>
</SubRenderers>
</ZipRenderer>
</DeviceInfo>
</Configuration>
</Extension>The code that reads the settings ( in ZipRenderingProvider.Render )
(...)
using (ZipOutputStream zipOutput = new ZipOutputStream(outputMemoryStream, true))
{
try
{
if (deviceInfo["CompressionLevel"] != null)
zipOutput.CompressionLevel = (Ionic.Zlib.CompressionLevel)Enum.Parse(
typeof(Ionic.Zlib.CompressionLevel), deviceInfo["CompressionLevel"], true);
if (deviceInfo["CompressionMethod"] != null)
zipOutput.CompressionMethod = (Ionic.Zip.CompressionMethod)Enum.Parse(
typeof(Ionic.Zip.CompressionMethod), deviceInfo["CompressionMethod"], true);
if (deviceInfo["Strategy"] != null)
zipOutput.Strategy = (Ionic.Zlib.CompressionStrategy)Enum.Parse(
typeof(Ionic.Zlib.CompressionStrategy), deviceInfo["Strategy"], true);
if (deviceInfo["Comment"] != null)
zipOutput.Comment = deviceInfo["Comment"];
if (deviceInfo["EnableZip64"] != null)
zipOutput.EnableZip64 = (Ionic.Zip.Zip64Option)Enum.Parse(
typeof(Ionic.Zip.Zip64Option), deviceInfo["EnableZip64"], true);
if (deviceInfo["IgnoreCase"] != null)
zipOutput.IgnoreCase = Boolean.Parse(deviceInfo["IgnoreCase"]);
if (deviceInfo["Encryption"] != null)
zipOutput.Encryption = (Ionic.Zip.EncryptionAlgorithm)Enum.Parse(
typeof(Ionic.Zip.EncryptionAlgorithm), deviceInfo["Encryption"], true);
if (deviceInfo["Password"] != null)
zipOutput.Password = deviceInfo["Password"];
}
catch (Exception ex)
{
throw new System.Configuration.ConfigurationErrorsException(
"Invalid DeviceInfo configuration value", ex);
}
foreach (SubRenderer sr in subRenderers)
{<span style="font-size: 9pt;">
(...)</span>
In SSRS 2005 you can use the built-in PdfRenderer just like the ExcelRenderer because it is public.
No such luck in SSRS 2008 (R2) / 2012, though. Microsoft has made the PdfRenderer an internal sealed class, so you have to resort to Reflection methods to reach it. Yes, you heard right. Below is the code necessary to do so.
if (pdfRendererType == null)
{
Assembly IR = Assembly.Load(new AssemblyName("Microsoft.ReportingServices.ImageRendering,
Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"));
pdfRendererType = IR.GetType("Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer");
}
renderer = (IRenderingExtension)pdfRendererType.GetConstructor(BindingFlags.Public |
BindingFlags.Instance, null, Type.EmptyTypes, null).Invoke(null);
The trick to this is that at the end the pdfRenderer is cast to the interface IRenderingExtension.
Unless you want to use pdfRender-specific functions, this is where reflection ends. Good thing, too.
With this code in place the PDF watermark becomes possible in SSRS 2008/2012 !
For every built-in renderer we need to get a reference to the SSRS renderer class. There are also all kinds of issues with streams to address.
You may think: can't you tell the report server to create the report and then get it back?
Well, we can: with a call
to the ReportExecutionService or reportserver URL Access. But, the server has already provided us with our report data: in the report parameter
of the IRenderingExtension.Render method. It would be a waste to do nothing with this and rerun the entire report, wouldn't it?
the ReportViewer to the rescue (or not?)
You may have noticed in the Report Manager there is no delay if you export a report. This is because it doesn't run the entire report again: it passes
the SessionID to the SSRS Engine. This way SSRS knows it needs to reuse the data already shown and just render it differently. This is also possible in code,
see the ReportViewerSubRenderer class (modified code from this blog comment). It generates a
HttpWebRequest that looks like this:
/Reports/Reserved.ReportViewerWebControl.axd?ReportSession=4obc0z550
su1si2em2oxv445&Culture=1043&CultureOverrides=False&UICulture=9&UICultureOverrides=
False&ReportStack=1&ControlID=ab4010a279a44dffbe1b15e6c7182d7b&OpType=Export&
FileName=Report1&ContentDisposition=OnlyHtmlInline&Format=EXCEL
There's a catch. Two actually:
In Subscriptions there is no SessionID available. Without a SessionID the
ReportViewerWebControl cannot be used to render a report, it doesn't know which one to render..
And, while this worked in SSRS 2005 (from the reportmanager), in SSRS 2008 it seems the engine got smart and blocks additional calls to
ReportViewerWebControl; the above request will time out. I assume this is some protection against repeated requests, to safeguard server resources.
Here's a diagram to illustrate this - remember, this is all founded on observation and may not reflect the actual process.
Solution
Using the webservice / URL access may still be a viable option if you use it together with report data caching. Just set the cache expiration to a small amount like 5 minutes, just enough for the report data to be stored and reused. Just subclass
SubRenderer and point to your class in CreateSubRenderer.. - I may add that myself in the future.
Other than that, the built-in renderers are the most efficient solution. You just need to code every one separately.
Everything would be so much easier if we could just access the <a href="http://msdn.microsoft.com/en-us/library/microsoft.reportingservices.interfaces.report.render.aspx">Report.Render</a>(format, deviceInfo) method like in a Delivery Extension...
History
- Nov 11, 2012 v1.0: Published first article!
- Jan 26, 2012 v1.1: Added support for Zip options like CompressionLevel
