SQL Server

Display Your Data Your Way with Custom Renderers for Reporting Services

James Yip

This article was based on Beta 2 of Microsoft SQL Server 2005 and all information contained herein is subject to change.

Code download available at:CustomRenderers.exe(123 KB)

Contents

Reporting in Reporting Services
Getting Started
Rendering the Report
Rendering a Report Object
Let's Try It
Conclusion

SQL Server™ 2005 Reporting Services from Microsoft is a great tool that offers a centralized approach to storing and rendering reports. It also allows users to view and download reports without installing additional software. But what's most convenient for users is that reports can be saved in any number of different formats using custom report renderers. In this article, I will demonstrate how to develop one such report renderer that outputs HTML reports, but the skills you'll learn can easily be used to create a renderer for Microsoft® Word documents or any other format of your choosing.

SQL Server 2005 includes many new features and enhancements. These include support for stored procedures written in managed code, user defined functions, and managed user-defined data type (UDT) support, which provides flexibility and reduces the need for data type conversion when reading and saving data to and from the database. As you will see, these particular features will come in handy when you build your custom report renderer.

Reporting in Reporting Services

Figure 1** Deployment Scenario **

Before I create a custom renderer for use with Reporting Services, let's briefly review the basics. Reporting Services is a server-based reporting tool that produces transactional and business intelligence reports. Figure 1 shows a typical deployment scenario.

Reporting Services is modular, so you can easily extend its functionality. Existing extensions to the model include those for data delivery, data processing, and report rendering. Data delivery extensions can be used with the subscription service in Reporting Services. They are used to send rendered reports to a particular recipient. Data processing extensions enable Reporting Services to process data from a variety of data sources. Report rendering extensions process the Report Definition Language (RDL) and create reports in a predefined format (such as PDF, HTML, and Excel) specific to the particular rendering extension being used.

Figure 2 illustrates the architecture of Reporting Services. Using these extensions, you can turn your report servers into a complete reporting solution. Reporting Services will be able to process data from DB2 and SAP, to name two, and output the report in your preferred format.

Previously, to create a report using Reporting Services you used the Report Designer, which works with Microsoft Visual Studio® .NET 2003. In SQL Server 2005, you use the Business Intelligence Development Studio, which is installed when you select Reporting Services or Analysis Services during the installation of SQL Server 2005. RDL, an XML document with a specific schema used to store the definition of the report, can be created with the Report Designer or with other third-party tools.

Figure 2** Reporting Services Architecture **

Getting Started

Before you begin, you need to get your development environment ready. First, create a Visual Studio 2005 Class Library Project. (You cannot use Visual Studio .NET 2003 for creating rendering extensions for SQL Server 2005 but you can use it for creating rendering extensions for SQL Server 2000. An assembly that targets the .NET Framework 2.0 is the only supported assembly for all extensions of SQL Server 2005. In this article, I will be using Visual Basic®.) Next, add a reference to two of the Reporting Services assemblies, which are located at \Program Files\Microsoft SQL Server\ MSSQL.3\Reporting Services\ReportServer\bin (see Figure 3).

Figure 3 The Reporting Services Libraries

A report renderer must implement the IRenderingExtension interface so that Reporting Services can recognize it as a renderer. The IExtension interface is also required to be implemented by all Reporting Services extensions. Implementing these interfaces requires you to implement five core members: the SetConfiguration method, the LocalizedName property, the GetRenderingResource method, the Render method, and the RenderStream method.

The SetConfiguration method inherits from IExtension interface and is used for processing any additional configuration data stored in the report server configuration file. LocalizedName is a read-only property derived from the IExtension interface. The string returned will be the user-friendly name of the Reporting Services Extension that is displayed to the user by Report Manager. The GetRenderingResource method comes from the IRenderingExtension interface and is used for rendering any embedded resources of a report, such as images and icons. The Render method is required for the IRenderingExtension interface and contains the logic used for report rendering. Finally, the RenderStream method is required for the IRenderingExtension interface. This method is used to support the report rendering method—Render—to return additional resources which are not created during the report rendering process, for example images in an HTML report.

The skeleton of a custom renderer should look like Figure 4. Before you write the code to render the report, you need to configure the report server to make sure it recognizes the existence of the new renderer and can be used in Report Manager. The first thing to do is to add code in the LocalizeName property, which returns a descriptive name that will be shown in Report Manager. You can return a different name of the renderer based on the CurrentCulture property of class System.Globalization.CultureInfo. Figure 5 shows an implementation of this property and the return name of the renderer based on the user's current culture.

Figure 5 Implementing LocalizedName

Public ReadOnly Property LocalizedName() As String _ Implements IRenderingExtension.LocalizedName Get Dim p_strName As String = "MSDN Magazine Custom Renderer" Dim p_CultureInfo As CultureInfo = CultureInfo.CurrentCulture ' Determine the text to be returned (displayed) by ' the Name property of CultureInfo class If p_CultureInfo.Name = "zh-HK" Then p_strName += " (Traditional Chinese)" ElseIf p_CultureInfo.Name = "zh-CN" Then p_strName += " (Simplified Chinese)" End If Return p_strName End Get End Property

Figure 4 Skeleton Code for Custom Renderer

Imports System.Collections.Specialized Imports Microsoft.ReportingServices.Interfaces Imports Microsoft.ReportingServices.ReportRendering Public Class Renderer Implements IRenderingExtension Public Function RenderStream( _ ByVal streamName As String, ByVal report As Report, _ ByVal reportServerParameters As NameValueCollection, _ ByVal deviceInfo As NameValueCollection, _ ByVal clientCapabilities As NameValueCollection, _ ByVal headerFooterExpressions As EvaluateHeaderFooterExpressions, _ ByVal createAndRegisterStream As.CreateAndRegisterStream) _ As Boolean Implements IRenderingExtension.RenderStream ... ' Code to render report Return False End Function Public Function Render( _ ByVal report As Report, _ ByVal reportServerParameters As NameValueCollection, _ ByVal deviceInfo As NameValueCollection, _ ByVal clientCapabilities As NameValueCollection, _ ByVal headerFooterExpressions As EvaluateHeaderFooterExpressions, _ ByVal createAndRegisterStream As CreateAndRegisterStream) _ As Boolean Implements IRenderingExtension.Render ... ' Code to render the report Return False End Function Public Sub GetRenderingResource( _ ByVal createAndRegisterStreamCallback _ As CreateAndRegisterStream, _ ByVal deviceInfo As NameValueCollection) _ Implements IRenderingExtension.GetRenderingResource ... ' Render any embedded resource in the report End Sub Public ReadOnly Property LocalizedName() As String _ Implements IRenderingExtension.LocalizedName Get ... ' Returns the name of this extension End Get End Property Public Sub SetConfiguration(ByVal configuration As String) _ Implements IRenderingExtension.SetConfiguration ... ' Obtain configuration from RS config file End Sub End Class

Once you have everything ready, compile the project and configure the report server to use this renderer. All Reporting Services extensions need to be placed in the \Program Files\Microsoft SQL Server\ MSSQL.3\Reporting Services\ReportServer\bin folder, so copy all your compiled assemblies there. Alternatively, you could set the output directory of your renderer project to this directory. If you do, make sure that you do not remove any references to the Reporting Services library, as doing so will also remove those library files from the build directory (the build directory in this case is the location of the Report Server used to store those libraries). This will remove library files from the report server causing it to fail. There will be a large number of files created after compiling the project, but you only need to copy files related to your project.

After placing the files into the Bin directory of your report server, you need to update two report server configuration files that allow Reporting Services to load and use this new renderer. In \Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer, you'll find the configuration file, RSReportServer.config, to which you need to add an entry as a child node of \Configuration\Extensions\Render, like so:

<Extension Name="CUSTOM_RENDERER" Type="MSDNMagazine.CustomRSRenderer.Renderer, MSDNMagazine.CustomRSRenderer" />

The name attribute, which must be unique among all Reporting Services extensions, will be used when you want to tell the report server to render a report using this renderer. The type attribute tells Reporting Services which class to load and which assembly contains the class. You can also add another attribute to this element called visible. This attribute is set to true by default. Setting it to false allows the renderer to be referenced programmatically but keeps it hidden from the Report Manager.

Reporting Services uses Code Access Security to control the permissions granted to different components. All Reporting Services extensions need to run with full trust, so you need to add an entry in the report server's security configuration file to grant full trust to your custom assembly. To do so, open rssrvpolicy.config, which is located in the same folder as RSReportServer.config. Here is an example of the entry that you need to add as child node of \configuration\mscorlib\security\policy\PolicyLevel\CodeGroup:

<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Custom Render" Description="This code group grants Custom Renderer code full trust."> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\MSDNMagazine.CustomRSRenderer.dll" /> </CodeGroup>

Once everything is ready, you simply need to restart your Report Server service. You should then be able to see the new output format displayed in Report Manager.

Rendering the Report

When you create a report renderer, you need a component to parse the content of the RDL file. Luckily, Reporting Services has a built-in component that will parse the RDL and construct a hierarchy of data-bound objects with a top-level object, Microsoft.ReportingServices.ReportRendering.Report, that represents the report. When you're rendering the report, you can use this top-level object in order to retrieve all the report configuration information as well as its content.

In order to construct the report output, you need to add code to the Render and RenderStream methods. The Render method renders the body or content of the report, while the RenderStream method renders all the associated objects, such as charts and other embedded objects. For simplicity, I'll focus on the Render method.

A number of parameters can be passed to the Render and RenderStream methods which can affect the way the renderer renders the report. The reportServerParameters NameValueCollection is passed to the report server, typically for filtering. The deviceInfo NameValueCollection contains report properties, while the clientCapabilities NameValueCollection informs the renderer about client technologies such as scripting support. Figure 6 shows a sample implementation of the Render method. This will list all the values stored in these parameters as well as a sample output.

Figure 6 Render Method

Public Function Render( _ ByVal report As Report, _ ByVal reportServerParameters As NameValueCollection, _ ByVal deviceInfo As NameValueCollection, _ ByVal clientCapabilities As NameValueCollection, _ ByVal headerFooterExpressions As EvaluateHeaderFooterExpressions, _ ByVal createAndRegisterStream As CreateAndRegisterStream) _ As Boolean Implements IRenderingExtension.Render Dim s As System.IO.Stream = createAndRegisterStream( _ "parameters", "htm", System.Text.Encoding.UTF8, "text/html", _ true, StreamOper.CreateAndRegister) Dim sw As New System.IO.StreamWriter(s) sw.Write("<html><body>") sw.Write("<b><u>reportServerParameters</u></b><br>"); For Each key As String in reportServerParameters.AllKeys sw.Write(key+"="+reportServerParameters(key)+"<br>") Next sw.Write("<br>"); sw.Write("<b><u>deviceInfo</u></b><br>") For Each key As String in deviceInfo.AllKeys sw.Write(key+"="+deviceInfo(key)+"<br>") Next sw.Write("<br>") sw.Write("<b><u>clientCapabilities</u></b><br>") For Each key As String in clientCapabilities.AllKeys sw.Write(key+"="+clientCapabilities(key)+"<br>") Next sw.Write("</body></html>") sw.Close() return True End Function

Besides these parameters, two very important delegates are also passed. The headerFooterExpressions delegate invokes a Reporting Services internal function to determine if a page needs to include a header or footer when the page header or footer depends on the content of the page. The createAndRegisterStream delegate invokes a Reporting Services internal function to create a stream to which the renderer writes the rendered report. The stream created acts as the output stream and is ultimately sent to the client.

Now that you know what kind of information is sent to the renderer, you can start writing the rendering code. Before I talk about rendering a specific kind of object in the report, let's look at rendering a report using the Render method. First, invoke the createAndRegisterStream delegate. This will create a stream to which your renderer will write the rendered report to pass to the client. Second, use the information stored in the report object to construct the report. Of course, the report object passed to the Render/RenderStream method represents the report that the Reporting Services user selected. You may also need to make use of other information passed to the Render method to control the look of your report. For example, you probably don't want to render any JavaScript in your HTML report if the clientCapabilities object tells you the client does not support JavaScript.

The following is an example of how to use the createAndRegisterStream delegate to create an HTML stream:

System.IO.Stream s = createAndRegisterStream("sample", "htm", System.Text.Encoding.UTF8, "text/html", true, StreamOper.CreateAndRegister);

At this point you can use a variety of techniques to add content to the stream. For simplicity, I will be using a StringBuilder to construct the HTML output; you should use HtmlTextWriter in order to construct the HTML in your production report renderer.

Now that you have created an empty report, let's look at how to render the content of the report. I will focus on rendering Matrix and Textbox objects to the report output. There are lots of objects available in the Report Designer, and all of them must be handled accordingly since their behaviors differ quite a bit.

Rendering a Report Object

I've selected Matrix as an example to render because it's one of the most widely used objects in Reporting Services. Most of my team's projects use matrices to present business information.

The report object is passed into the two Render methods. The Report object's Body property contains the information relating to the Body, such as formatting. It contains a property, ReportItemCollection, which holds references to all rendered instances of the report elements in the Body section of your report in the Report Designer. To get the objects contained in the Body section of the report, each of which is a ReportItem, you need to loop through the ReportItemCollection. You need to cast to a ReportItem-derived class in order to access specific properties related to each element. This information can then be used in your code to render the report element to the output.

All report element types such as Matrix and Textbox are derived from the ReportItem type, and each container object exposes a property to allow access to the contained child objects. As with the Body's ReportItemCollection, these must be cast to a ReportItem-derived type to access properties specific to those types. The code in Figure 7 enumerates all of the elements contained in the Body by using the ReportItemCollection property.

Figure 7 Enumerate Elements

Public Function Render( _ ByVal rpt As Report, _ ByVal reportServerParameters As NameValueCollection, _ ByVal deviceInfo As NameValueCollection, _ ByVal clientCapabilities As NameValueCollection, _ ByVal headerFooterExpressions As EvaluateHeaderFooterExpressions, _ ByVal createAndRegisterStream As CreateAndRegisterStream) _ As Boolean Implements IRenderingExtension.Render For Each rptItem as ReportItem in report.Body.ReportItemCollection ... ' Implement your report element rendering code here Next End Function

I suggest you create an individual object to handle each report element (one class for Matrix, one class for Textbox, and so on) for ease of maintenance and development. When rendering the report, you need to pay attention to the structure of the matrix. For example, first you render the Corner, then the Row Member Collections together with the Column Member Collections, and lastly, you render the content of the cell.

One of the challenges I faced when rendering the matrix is that I first needed to determine the levels in the row member collection and column member collection. To compensate, I loop through all levels of the row and column member collection to find out the RowSpan and ColSpan of the corner (that is, the number of columns in the row member collection and the number of rows in the column member collection).

When drawing the content of the matrix to the report output, you need to make use of the Cell property of the matrix object. In the case of matrix, the cell will be a textbox object by default. Any other kind of object, like Chart or Table, can also be returned by this property. Some checking should be in place to ensure that you use the right internal renderer to render the returned object. For simplicity, I assume all cell properties will return the textbox object. A textbox rendering class is created to handle the rendering job of the textbox. You can also use your own custom code to handle the textbox in the matrix, but you really should make it a separate renderer so that it can be reused later on.

One interesting thing I found in the Style property in all kinds of ReportItem objects is that the formatting information is stored in a key-value pair collection (similar to a Hashtable). Note, however, that the key used by Reporting Services is for internal use (or more specifically, for the use of the report renderer). They are not necessarily aligned with the property names used in HTML. The rendering object model property names match the RDL property names. Various rendering targets all have their own property names, many of which don't match each other, so it makes sense that the RDL property names don't match the HTML property names. In the sample code that comes with this article, I created a class to convert the Reporting Services styles to those used in HTML.

Figure 8 shows a sample Textbox Renderer together with style handling functions for converting styles stored in the Style property of objects representing elements.

Figure 8 TextBoxRender

Friend Class TextBoxRender Friend Sub New() End Sub Friend Function Render(ByVal textBox As _ Microsoft.ReportingServices.ReportRendering.TextBox) As String Dim pHtml As New System.Text.StringBuilder Dim sh As New MSDNMagazine.CustomRSRenderer.StyleHandler( _ textBox.Style) pHtml.AppendFormat("<DIV STYLE=""backgroundcolor:{0}"">", _ sh.GetStyleColorRGBCode("BackgroundColor")) pHtml.AppendFormat("<FONT FONT=""{0}"" SIZE=""{1}""" & _ "COLOR=""{2}"" STYLE=""font-size:{1};"">", _ sh.GetStyleString("FontFamily"), _ sh.GetStyleFontSize.ToPoints().ToString() + "pt", _ sh.GetStyleColorRGBCode("Color")) If sh.GetStyleFontStyle = FontStyle.Italic Then pHtml.Append("<I>") End If If sh.GetStyleFontWeight = FontStyle.Bold Then pHtml.Append("<B>") End If If sh.GetStyleTextAlignment = ContentAlignment.MiddleCenter Then pHtml.Append("<CENTER>") ElseIf sh.GetStyleTextAlignment = ContentAlignment.MiddleLeft _ Then pHtml.Append("<DIV ALIGN=""LEFT"">") ElseIf sh.GetStyleTextAlignment = ContentAlignment.MiddleRight _ Then pHtml.Append("<DIV ALIGN=""RIGHT"">") End If pHtml.Append(textBox.Value) If sh.GetStyleTextAlignment = ContentAlignment.MiddleCenter Then pHtml.Append("</CENTER>") Else pHtml.Append("</DIV>") End If If sh.GetStyleFontWeight = System.Drawing.FontStyle.Bold Then pHtml.Append("</B>") End If If sh.GetStyleFontStyle = System.Drawing.FontStyle.Italic Then pHtml.Append("</I>") End If pHtml.Append("</FONT></DIV>") Return pHtml.ToString() End Function End Class

Let's Try It

Once everything is ready, compile the code and then copy the generated assemblies to the bin directory of the ReportServer directory. One simple way to test if the renderer is working correctly is to use Report Manger. To do so, first select the report that you want to use with the renderer, select the name of the renderer you created, and then click Export. A new window will be launched which will display the rendered and downloaded report.

Using our sample renderer, you should then see the screen shown in Figure 9—a user-friendly HTML error message telling you an error occurred in your extension code. Unfortunately, user-friendly error messages are usually not very useful to developers. However, there is some valuable information in the HTML source of the user-friendly error screen. If you open and view the HTML source of the error page, inside the head tag, the stack trace of the error is stored as a meta tag which can aid in debugging the program. This is especially useful when the renderer is deployed to the machines in the production environment.

Figure 9** User-Friendly Error Message **

Another place to look when you reach an error is the log file located at \Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles. In order to use the debugging features provided by Visual Studio, you have to configure and test the renderer with Reporting Services running on your development machine. After adding the configuration to the RSReportServer.config for the renderer, try to render the report using the preview report function in Visual Studio. Doing so means the debugger can be attached to your custom renderer.

If everything has gone well, you should be able to see the report rendered as shown in Figure 10. Figure 11 shows the same report rendered by the HTML renderer that comes with Reporting Services. As you can see the rendered report is very similar to the one rendered by Reporting Services.

Figure 10 The Rendered Report

Figure 11 The Rendered Report in HTML

You can use this custom HTML renderer to render an Excel file. The trick is that Excel is able to open an HTML file and display it in a workbook style. To start, change the parameters that are passed to the createAndRegisterStream delegate, as shown in the following code:

createAndRegisterStream(report.Name, "xls", System.Text.Encoding.UTF8, _ "application/vnd.ms-excel", true, StreamOper.CreateAndRegister);

Doing so makes the report server think that this is an Excel file, which tells the browser that the rendered report is an Excel file even though the content is HTML. The browser will then use Excel to open the rendered report, making it appear to the user that the report is an Excel file rather than an HTML file. This approach does have some limitations when compared to a native Excel report renderer, such as the lack of image and multiple worksheet support. To render a native Excel, Word, or PowerPoint® file, you can use the Office Automation in your report renderer, or a third-party component for construction of the type of document required.

Conclusion

After mastering the basics for creating a renderer for Reporting Services, you can create renderers that render reports into the format of your choice, such as Word. Once more, although this article focuses on Reporting Services in SQL Server 2005, all the techniques I've described are applicable to Reporting Services in SQL Server 2000. Have fun creating your own report renderers!

James Yip, MCT, MCSE, MCDBA, MCSD, MSF Practitioner, is a consultant with Edvance Limited, a Hong Kong-based technologies consulting company. James is also a trainer teaching MOC classes in both Windows Server Systems Administration and .NET development for a local Microsoft Certified Partner - Learning Solution.