Designing and Delivering Rich Office Reports with SQL Server Reporting Services 2005 and SoftArtisans OfficeWriter
SQL Server Technical Article
SQL Server 2005 Reporting Services
Summary: This white paper explains how to use SQL Server Reporting Services 2005 and SoftArtisans OfficeWriter to design and deliver full fidelity, data-driven Microsoft Excel and Word reports. OfficeWriter consists of a designer component that turns Excel and Word into a powerful tool for designing Reporting Services reports, and custom rendering extensions that deliver real binary Excel and Word documents. The paper also covers the process by which these reports are created, previewed, deployed, and delivered. (15 printed pages)
Click here to download the Word document of this article, RichRptSSRSandOW.doc.
Microsoft SQL Server Reporting Services is a comprehensive, secure, server-based reporting solution that enables users to easily build reports to analyze business data. Reporting Services presents data in a useful, familiar format. Users want their data delivered to applications that they already know, and that they are comfortable and productive with. For many, these applications are Microsoft Excel and Word.
SoftArtisans OfficeWriter is a tool that extends and complements the Reporting Services extensible reporting infrastructure by delivering tighter integration with Excel and Word. OfficeWriter extends the capabilities of Reporting Services in two major ways:
- Office-integrated design experience: With OfficeWriter, Excel and Word can act as the front end to Reporting Services. Reports can be authored, previewed, and deployed to your report server without ever leaving Microsoft Office. Microsoft Visual Studio .NET is not required. Business users can use their knowledge of Office to author reports without the help of a developer.
- Rich Excel and Word document delivery: Excel and Word documents are delivered with full fidelity. Documents that are exported from Reporting Services by using OfficeWriter support every Excel and Word feature, including pivot tables, lists, charts, formulas, and macro code. Because you are designing in the application in which the report will be rendered, you exercise precise control over the layout. The final report looks exactly as it did when it was designed, and it preserves all elements that were defined in it when it was designed.
This paper explains how OfficeWriter and Reporting Services work together. It describes the architecture of the product, and explains how you can use OfficeWriter and Reporting Services to develop rich Excel and Word reports.
OfficeWriter reads, writes, and manipulates Excel and Word documents in their native binary formats. OfficeWriter was initially developed as a COM library that Active Server Pages (ASP) developers used to build Excel reporting into their Web applications. Eventually Word support was added. With the advent of .NET, the COM library evolved into a fully managed .NET library for ASP.NET and Windows Forms use.
OfficeWriter does not require that Microsoft Office be installed on the server. Web developers have long avoided solutions involving automating Office on the server because of the myriad issues that it causes. Microsoft explicitly advises against this solution in the Knowledge Base article Considerations for Server-Side Automation of Office. For that reason, OfficeWriter's lightweight server footprint is a good fit for server-based applications.
Developers have two methods of developing Word and Excel reports with OfficeWriter: by using an API or by taking a template-based approach. The OfficeWriter API exposes a full object model for manipulating elements of Word and Excel documents. A developer can use the API to create and manipulate Excel and Word reports by using only code. With the template-based approach, you design the document entirely in Office. The document is then simply merged with data at run time. Using the template approach, business users can lay out documents with the look and feel that they want, and developers can create applications to merge data with those documents with as little as five lines of code.
SoftArtisans used the template approach as a starting point for integrating OfficeWriter with Reporting Services. The result is full fidelity server-side document generation, combined with client tools that provide a unique document design and delivery experience for Reporting Services users.
OfficeWriter for Reporting Services consists of two major components: a client-side designer toolbar for Excel and Word, and server-side rendering extensions that are installed and registered with the Reporting Services report server. The template-based DOC and XLS handling code exists in the rendering extension. The OfficeWriter Designer enables Excel and Word to generate Reporting Services reports based on the layout you create in Office. The custom rendering extensions generate high-fidelity Word and Excel documents from the special type of RDL created by the toolbar.
OfficeWriter for Reporting Services Architecture
OfficeWriter lets you design, preview, and deploy full-fidelity Excel and Word reports directly from Office. The architecture that OfficeWriter employs to provide template-based layout for Reporting Services is unique, and it requires a different way of thinking about report definition than you may be familiar with if you use Visual Studio .NET to author reports. This section explores how OfficeWriter for Reporting Services works.
How OfficeWriter Generates RDL
Report Definition Language (RDL) is an XML dialect that defines a report. The RDL file stores everything about the report, including data source information, dataset queries, and report parameter definitions. RDL files that are created by using Visual Studio .NET or Report Builder also contain an XML-based layout definition. OfficeWriter differs from other report design tools in a significant way: the Excel or Word binary template file is serialized and stored separately from the XML layout. The Excel and Word templates are embedded in the RDL file and become part of the report definition. This creates a completely separate report layout definition within the RDL file. Figure 1 is an example RDL file that was created with OfficeWriter for Excel.
Figure 1 (Click on the image for a larger picture)
Note the CustomProperty element. This is the Base64-encoded Excel template file.
Rendering with OfficeWriter
Reports designed by using OfficeWriter must be rendered by using OfficeWriter rendering extensions on the report server. The OfficeWriter rendering extensions can process the binary template and merge it with data. At render time the stored binary template is extracted from the RDL file and populated with data from the datasets, which are also defined in the RDL. OfficeWriter ignores the nonbinary template RDL-based layout information that other conventional rendering extensions use. Likewise, conventional rendering extensions cannot handle the binary template and therefore generate the report based on the standard RDL-defined layout. This means that a report designed with OfficeWriter can only be rendered by OfficeWriter rendering extensions. For example, if you design the report in Excel, you must render it by using the OfficeWriter for Excel extension. If you design in Word, you must render by using the OfficeWriter for Word extension.
Managing OfficeWriter Reports
RDL files generated by the OfficeWriter Designer can be published to the report server and managed just like any other RDL file. Once the reports are published, any of the features of the Reporting Services report server such as security, scheduling, delivery, and management can be applied to them.
When OfficeWriter is installed on the report server, it adds two new rendering extension assemblies to the report server and registers the extensions in the server configuration file. The new rendering extensions are listed in the Report Manager rendering extension list, shown in Figure 2.
If you have published an OfficeWriter-designed report to the server, select these rendering extensions to invoke OfficeWriter and deliver the Excel or Word report based on the stored binary template.
Tour of the OfficeWriter Designer
The OfficeWriter Designer is the Office-based interface to Reporting Services. You use the Designer to perform several major classes of operations against the report server including publishing, previewing, dataset definition, and data binding.
The Excel and Word designers are implemented as add-in toolbars. When the OfficeWriter Designer is installed on a client computer, you see the toolbar when you start Excel or Word. For example, Figure 3 shows the OfficeWriter Designer in Word 2007.
Figure 3 (Click on the image for a larger picture)
The Open Report button opens existing RDL files for editing in Word or Excel. This button is your starting point if you are modifying an existing report designed with OfficeWriter, or if you are adding a new Excel or Word template to a report that was previously designed in Visual Studio .NET.
The Add Query and Edit Query buttons invoke the MSQuery graphical query designer that is used to create or edit datasets. The MSQuery tool is familiar to anyone who has done database data retrieval or mail merge in Office. The Select Query button opens a list that lists all of the datasets defined for the report.
The Query Range and Insert Merge Field buttons are unique to the OfficeWriter Designer for Word. Query Range enables you to define a region of the document that will be repeated for every row in the dataset. These repeating sections are called Repeat Blocks. For example, you might want to display data in a Word table. To do this, define a Word table row as a Repeat Block by using the Query Range button. For every row of detail in the corresponding dataset, whatever is enclosed in the Repeat Block is repeated. Word Repeat Blocks are explained in more detail later in this article. Use the Insert Merge Field button to add fields from your dataset into the Word template document.
The Save As button generates an RDL file that includes the serialized Word or Excel template file and the datasets you defined by using MSQuery. Use this button if you want to save your report to disk. The Publish button opens a dialog box (Figure 4) that displays a view of your report server's catalog. Use this dialog box to select a folder for your published report.
These examples feature the Word-based Designer; the Excel Designer is only slightly different and most of the same concepts apply. The Excel Designer has no Query Range button and the Insert Merge Field button is instead labeled Insert Field. Otherwise, the Excel and Word Designers are the same.
When you design a report by using OfficeWriter, the Excel or Word template acts as the definition of your report layout. OfficeWriter enables you to have a standard RDL-based layout as well as an Excel and Word template. This means that you can have up to three different layouts defined in a single RDL file: one that was designed in Visual Studio .NET or Report Builder, one for Word, and one for Excel.
Designing an Excel Report
If you design a report without starting from an existing RDL file, you use the Add Query button to bring up MSQuery and define your report's datasets. Figure 5 shows an example of a simple dataset created in MSQuery using the AdventureWorks database.
Defined datasets appear in the Select Query list. The list of available fields from the dataset is shown in the Insert Field list as shown in Figure 6.
When you select a field from the Insert Field list, a data marker is placed in the currently selected cell. Data markers are specially formatted strings used in Excel reports that the OfficeWriter rendering extension looks for and populates with data at rendering time. Data markers are in this form: %%=[Dataset Name].[Field Name]. Data markers can be placed into any cell in the Excel workbook.
After the report is published, when previewed in Report Manager it looks like the report shown in Figure 7.
Because this report was designed only in Excel and not in Visual Studio .NET Report Designer or Report Builder, there is no RDL-based layout compatible with non-OfficeWriter rendering extensions. The report definition only contains the binary template. The OfficeWriter Designer will generate the placeholder layout as shown in Figure 7 to remind users that the report needs to be rendered by using an OfficeWriter rendering extension. To see the report that was defined in Excel using the OfficeWriter Designer, select Excel (.xls) designed by OfficeWriter from the Export menu.
Adding a Word Template to an Existing Report
The previous section explained how to create an Excel report from scratch. Excel or Word documents can also be added as supplemental template-based layouts to existing reports designed by using Visual Studio .NET or Report Builder. Use the Open Report dialog box to open an RDL file from the file system or from a report server. When an existing RDL file is opened and an Excel or Word template hasn't yet been defined, the datasets are imported into the Designer Select Query list and the report author is presented with a blank Excel or Word design surface. Because the datasets are already defined in the RDL file, the author can skip the step of using MSQuery to define datasets and move on directly to defining the layout of the report.
Figure 8 shows what the report author sees if the Reporting Services 2005 AdventureWorks sample report Product Line Sales is opened with OfficeWriter Designer.
These datasets were imported from the existing RDL file. The author is then free to design the Word template part of the report. What is designed as part of the Word template is completely independent of the RDL layout that was previously defined. In this case, OfficeWriter preserves whatever layout existed in the RDL file when it was opened. The placeholder layout shown in Figure 8 does not get created.
OfficeWriter and Reporting Services together enable you to create rich Office reports that can take advantage of any Excel or Word feature. For both Excel and Word, all formatting that you apply to the document when authoring the report is preserved when the report is rendered. OfficeWriter lets report authors use powerful Excel and Word specific features in their reports.
The Excel rendering extension native to Reporting Services focuses on accurately replicating the report's look and feel within Excel. This ensures that Excel exports look the same in HTML, PDF, or any of the other export formats. The existing rendering extension does a good job of this; however, there are times when you may want to take advantage of more advanced Excel features such as formulas, charts, pivot tables, and advanced Excel formatting. This section highlights some interesting Excel features you can include in your reports with OfficeWriter.
Data markers defined in the Excel template tell OfficeWriter where data should be imported. Any formatting applied to a cell in which the data marker exists is preserved after the workbook is rendered. For example, if you have a cell with a data marker that you know will return currency values, you should format the data marker with a currency style. You can also apply conditional formatting.
Because Excel is your design tool when you build reports with OfficeWriter, you can put any Excel formula into your report. At design time you may not know how many rows of data will be returned in the dataset during execution. Therefore, it's impossible to know exactly where to put formulas in your Excel worksheets. OfficeWriter handles this by automatically expanding formulas to account for an unknown number of rows in the dataset.
For example, consider this simple report that has two fields and a SUM formula at the bottom of the second column (Figure 9).
Notice that the formula is defined as =SUM(C2:C2). It may look a bit strange that the range for the formula contains just a single cell, but when the Excel template is populated by OfficeWriter on the server, the right side of the formula range expands to fit the number of rows imported by the TerritorySales dataset. Figure 10 shows what the report and formula look like when rendered.
Note that OfficeWriter has updated the formula to: =SUM(C2:C11).
The native Excel renderer adds charts to Excel output as static images, which means that the charts lose their connection with the data and are not updateable. With OfficeWriter, you can add live Excel charts. All Excel chart types are supported. The process for adding an Excel chart is similar to that of adding a formula. Define the Category and Series references both as single-cell ranges. When the report is rendered, OfficeWriter expands the ranges. Figure 11 shows the chart definition.
Figure 11 (Click on the image for a larger picture)
Note that the Series range points to C2:C2. The Category range is B2:B2. Both of these ranges are expanded in Figure 12.
Figure 12 (Click on the image for a larger picture)
Pivot tables are a powerful tool for analyzing data in Excel. This is especially true in Excel 2007 where there have been many improvements to pivot tables over previous versions. OfficeWriter enables you to define Excel pivot tables in reports and have them delivered by Reporting Services. If the pivot table is configured to point to a range of data imported by data markers, the table will pull in the data when it is refreshed as shown in Figure 13.
Figure 13 (Click on the image for a larger picture)
OfficeWriter brings full-featured Word document exporting to Reporting Services. The OfficeWriter Designer for Word uses a combination of native Word merge fields and bookmarks to define where data is to be imported into the document. Merge fields in Word are very much like data markers in Excel—wherever a merge field exists, OfficeWriter inserts data from the dataset. Bookmarks define Repeat Blocks, which are areas of the document that are to be repeated for each row of data. Typically, you define a Repeat Block with bookmarks and then put merge fields inside of the bookmarks.
Word tables are a very useful feature for laying out documents and creating standard tabular reports such as purchase orders and mailing labels. One very common technique for OfficeWriter Word reports is to enclose a table row in a Repeat Block so that the table grows to the size of the dataset. Figure 14 is an example of such a table.
Figure 14 (Click on the image for a larger picture)
Note that the table detail row is enclosed with Word bookmarks. Bookmarks are the small bracket-shaped objects on either side of the first table row. For each row in the OrderDetailQuery dataset, the contents of the bookmark are repeated and the merge fields within are replaced with data from the current row. Any merge fields not enclosed in a Repeat Block (such as the OrderHeaderQuery shown here) are populated with just the first row of data from the dataset.
Bulleted and numbered lists can be enclosed in Repeat Blocks. To do this, define the first item of your list in a Repeat Block as shown in Figure 15.
Note how the bookmark surrounds the entire list entry including the new paragraph marker at the end. When the report is rendered, the list expands as shown in Figure 16.
This technique enables you to create Word lists that dynamically grow to the size of your dataset.
Repeat Blocks can be defined around any portion of the Word document, even whole pages. This creates a new page in the document for each detail row in the dataset to which the Repeat Block is bound. This is an easy technique for creating a large quantity of data-driven form letters and doing mail-merge tasks.
Word merge fields support a rich set of formatting codes that can be defined at report design time. This gives you full control over how your data will look when it is imported into the Word template document. For example, here is the table from Figure 14 with field codes visible. Note that the formatting code is entered as \# $#,##0.00. That is a common currency formatting code. When the document is populated, the data in that merge field assumes the formatting assigned to it by that code (Figure 17).
Figure 17 (Click on the image for a larger picture)
Like the Excel design experience, any formatting defined in the template document is preserved.
The OfficeWriter Office-based report design experience and template-based rendering is quite different from what many Reporting Services users may be familiar with. But, if you have specific Excel and Word reporting requirements that call for rich, full-fidelity documents, OfficeWriter is an ideal fit. Reporting Services and OfficeWriter together help report authors take advantage of the power and flexibility of Excel and Word, and the secure reporting infrastructure that Reporting Services offers.
For more information: