• Article

January 2012

Volume 27 Number 01

SQL Server Reporting Services - Extending SSRS: Developing Custom Charting Components and Rendering Extensions

By Manpreet Singh | January 2012

SQL Server Reporting Services (SSRS) provides excellent charting capabilities that allow you to represent data and statistics visually in the form of both conventional and unconventional charts, and a variety of report-rendering extensions let you save a report in a variety of different formats such as PDF, CSV, XML, Word and Excel. Still, the native options may not always satisfy your business requirements or render a report exactly the way you want. Fortunately, you can extend the reporting services to create your own custom charting components and rendering extensions using the various extensibility features SSRS provides. In this article, I’ll give you an overview of the process of creating a custom charting component and the various ways to integrate it with an SSRS report. I’ll also describe how you can develop a custom report renderer (by extending a native one) to render a report just as you’d like. To try this yourself, download the complete code at msdn.com/magazine/msdnmag0112 and use it as your starting point. Note that the process of creating a rendering extension and report items hasn’t changed much since SQL Server 2005. Although I built the code samples using a SQL Server 2008 R2 environment, the concepts discussed in this article are very much applicable to SQL Server 2005 and 2008 as well. If you’re developing for SQL Server 2008 R2 Reporting Services, you can build custom components using the Microsoft .NET Framework 4 as well. For SQL Server 2008 Reporting Services, however, the .NET Framework 3.5 is the highest version of the .NET Framework supported.

The Web Site Users Report

To appreciate the process of developing a custom charting component and rendering extension, consider a scenario where an organization wants to generate a monthly Web Site Usage Report to illustrate the popularity of its, say, e-commerce Web site in different regions. The heart of the report is a Web Site Users Chart, which graphically represents the geographic distribution of users per region, as depicted in Figure 1.

Web Site Users Chart
Figure 1 Web Site Users Chart

Although the chart is quite similar to a bar graph, none of the native charting components fit the bill because you can’t replace the bars of the chart with a custom image representing a person, like the one in Figure 1.

Such a scenario definitely calls for creating your own custom charting component. Using the GDI+ base class libraries (part of the .NET Framework) and a little mathematics, it’s easy to draw the chart using basic shapes like circles and rectangles and generate the chart in the form of a regular bitmap image. The output from such a component, which generates an image by using the .NET Framework graphics libraries, can be integrated with the SSRS report in a number of ways:

  • Using Custom Report Items: SSRS supports the creation of custom report items, such as built-in charting components, that can be integrated into a report. The custom report items are rendered as images. The article, “Jazz Up Your Data Using Custom Report Items in SQL Server Reporting Services” (msdn.microsoft.com/magazine/cc188686), demonstrates how to create a custom report item.
  • Using a SQL CLR Function: You can also use a SQL CLR user-defined function to integrate a GDI+ based charting component with SSRS. This requires loading system.drawing.dll into SQL Server. Once the core assembly for chart generation is loaded into SQL Server, you can create a user-defined function based on it. The image reporting item can then be configured to render the chart image from a database.
  • Using an ASP.NET Handler: You can use an ASP.NET handler to integrate the charting component with both an SSRS report and a Web application. An ASP.NET handler can return the charting component as a downloadable bitmap image, with any parameters being passed to it using a query string. To integrate the handler output with SSRS, all you need to do is set the image source of the picture box to external and provide the URL of the handler as an expression.

The code samples accompanying this article demonstrate the process of creating the Web Site Users Chart and integrating it with the Web Site Usage Report using a SQL CLR function. The WebSiteUsersChartCore project contains the WebSiteUsersChart class, which renders the chart in the form of a JPEG image based on the monthly usage data returned by the dbo.usp_GetUsageData stored procedure. The WebSiteUsersReportDB.bak file contains a backup of the WebSiteUsersReportDB database with the necessary back-end tables and stored procedures. The SQL CLR function GetUsersChart is responsible for providing the necessary parameters to the charting component and getting the image output. The dataset DataSetWebSiteUsageChart of the RegionWiseReport gets the image from the SQL CLR function using the following SQL query, by passing in the required parameters:

SELECT dbo.usp_GetUsersChart(500, 300, N'Website Users', @RegionName,
  @DateID % 100, @DateID / 100) AS Chart

Finally, the image control ImageChart renders the image coming from the database.

The main advantage of using the SQL CLR function or the ASP.NET handler approach instead of developing a custom report item is that you can then use the same charting component outside of SSRS, say in a Web application, without the need for any further development effort or customization.

Custom Word Rendering Extension

Although the native Word rendering extension renders the Web Site Usage Report perfectly, some features, such as a table of contents or having some pages in portrait and others in landscape orientation, are not supported.

Writing a custom rendering extension from scratch isn’t a trivial task; you want it to be capable of taking various combinations of report elements and converting them to the corresponding formatting and data elements supported by the target format. Moreover, extending the native SSRS rendering extensions is not supported directly. Luckily, the output from the native Word rendering extension meets most of my needs for the report, so all I really want is a way to add a table of contents to it and to be able to control the orientation at the page level.

To accomplish my goals, I can write a custom rendering extension, which gets the report output rendered by the native Word extension, then use Word automation to modify the report output in the desired way and stream the final output to the user.

The class CustomWordRenderer in the project CustomWord­RenderingExtension in the source code highlights the important steps involved in achieving the desired effect. The output from the native Word rendering extension is merged with a standard header template that contains a title page and a table of contents, and can also contain items such as a copyright note, a disclaimer and so forth. The document map labels, which are rendered as Table Entries by the native extensions, have appropriate heading styles applied to make them appear in the table of contents. Finally, the table of contents is updated and the merged document is streamed to the user.

All the reporting services extensions must implement the IRenderingExtension and IExtension interfaces. These interfaces require you to provide implementation for the following methods and properties:

Members from the IExtension interface:

  • LocalizedName property
  • SetConfiguration function

Members from the IRenderingExtension interface:

  • GetRenderingResource function
  • Render function
  • RenderStream function

Of all these members, the Render method is most important and contains the core implementation for the Custom Word Renderer. Because I already get the formatted report output from the native Word rendering extension, I don’t have to parse the various report elements, and hence the render method won’t contain any code to deal directly with report data or formatting elements. All I need to do is get the report from the native Word rendering extension, open it with Microsoft Word (using Word automation), perform the required modifications and stream modified document to the user.

For more information on the process of developing custom rendering extensions, see the article, “Display Your Data Your Way with Custom Renderers for Reporting Services,” at msdn.microsoft.com/magazine/cc163840.

An important point to note before you decide to make use of concepts discussed here is that Microsoft doesn’t support server-side automation of Microsoft Office. If you do consider developing a custom rendering extension based on the approach I discussed, first take a look at the article, “Considerations for Server-Side Automation of Office,” at support.microsoft.com/kb/257757.

If you don’t want to use Office automation, there’s another interesting approach you can consider—rendering the report as an XML file using the native XML rendering extension and applying a custom XSLT stylesheet to generate a WordML document. The disadvantage of this approach is that you’ll end up storing report formatting in two places: one in the XSLT stylesheet and other in the report RDL file.

Using the Sample Code

The source code for this article contains the following artifacts:

  • WebSiteUsersChartCore project
  • TestHarness project
  • CustomWordRenderingExtension project
  • WebSiteUsersReport RDL file
  • RegionWiseReport RDL file
  • WebSiteUsersReportDB database backup file
  • Report header template file

The WebSiteUsersChartCore project demonstrates the process of creating the WebSite Users Charting component from region-wise usage statistics. The TestHarness project is used to test and verify the output of WebSiteUsersChartCore assembly by rendering the image generated by the charting component using a Windows Form application. The CustomWordRenderingExtension project contains the implementation for the custom Word rendering extension, based on the Word automation approach. The WebSite­UsersReport is the primary report that calls the RegionWiseReport subreport for every region, to render the region-wise contents. The RegionWiseReport report also calls the WebSite Users Charting component to render the WebSite Users Chart. It does so using the image control via a call to the usp_GetUsersChart SQL CLR scalar-valued function by passing in the appropriate parameters. The WebsiteUsersReportDB.bak file contains the backup for the WebSiteUsersReportDB database. The reports and the charting components are based on the data from this database. The header template file contains a title page and a table of contents, in portrait format. This file is appended to the beginning of the native Word document report, using the Word automation.

To deploy the sample code, build the WebSiteUsersChart solution containing the WebSiteUsersChartCore and Custom­WordRenderingExtension projects. Copy the CustomWordRenderingExtension.dll assembly to the report server’s bin directory. Make the following entry in your report server’s rssrvpolicy.config file to grant full trust to the custom rendering extension:

<CodeGroup
class="UnionCodeGroup" version="1"
PermissionSetName="FullTrust"
Name="CUSTOM WORD"
Description="This code group grants Custom Word Renderer code full trust.">
  <IMembershipCondition
    class="UrlMembershipCondition"
    version="1"
    Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting
        Services\ReportServer\bin\CustomWordRenderingExtension.dll" />
</CodeGroup>

Next, make the following entry in the rsreportserver.config file to register the rendering extension with the report server:

<Extension Name="Custom WORD" Type=
  "CustomWordRenderingExtension.CustomWordRenderer,
  CustomWordRenderingExtension">
  <Configuration>
    <DeviceInfo>
    <SourceHeaderFileName>C:\WorkingDirectory\
      Header.doc</SourceHeaderFileName>
    <SourceBodyFileName>C:\WorkingDirectory\Body.doc</SourceBodyFileName>
    <MergedFileName>C:\WorkingDirectory\MergedOutput.doc</MergedFileName>
    </DeviceInfo>
  </Configuration>
</Extension>

Create a working directory on the C: drive for the rendering extension and copy Header.doc to it. If you want to use a different directory, don’t forget to make the appropriate changes in the rsreportserver.config file as well; these configuration entries are picked up by the custom Word renderer. Of course, you must have Microsoft Word installed for the rendering extension to work.

Next, deploy the WebSiteUsersReport and RegionWiseReport reports on the report server. Run the WebSiteUsersReport report. Click on the Export menu and examine the contents of the drop-down—you should see the “CUSTOM WORD” rendering extension in the list. Go ahead and do an export. If you end up getting an empty document, examine the event log for errors.

Wrapping Up

Whenever native charting components don’t fit the bill, you should consider implementing a custom component. You can develop complex charts by leveraging your GDI+ development skill. Keep in mind that you can write custom renderers without having to write everything from scratch, by modifying the output of the native ones.

While it’s always possible to go completely custom and build new applications for report generation, it’s often very easy to achieve the same effect using SSRS with a few pieces of custom code plugged in at the right places. So go ahead and build your own charting components and report renderers without fear.

Manpreet Singh is a consultant with Microsoft Services Global Delivery, where he’s a part of the Business Intelligence and Integration Engineering group. He works primarily on design and development of .NET-centric Business Intelligence solutions based on the Microsoft Business Intelligence stack.

Thanks to the following technical expert for reviewing this article: Yaswant Vishwakarma