 Using OfficeWriter with SQL Server Reporting Services
Adding runtime control to Reporting Services Reports
By Michael Campbell
Read
Complete Review on ASPAlliance
Microsoft's SQL Server Reporting Services (SSRS) is revolutionizing the way organizations use data. Prior to the arrival of SSRS, most reporting solutions were either costly or technically complex. This typically kept reporting out of the reach of small to medium sized businesses that weren't able to afford custom 'in house' solutions or high-priced services and analysts to pour over their mountains of data and render it intelligently. But SSRS is changing all of that by putting a dependable reporting platform easily within reach of small to medium sized businesses that lets them access powerful reporting functionality without spending a fortune or making them wait on long development cycles.
Larger businesses and enterprises can also benefit from SSRS and its extensible architecture. SSRS proffers an open Web Services interface which allows larger companies (with dedicated resources) to create custom Data Processing, Delivery, and Rendering extensions to meet their own particular needs. This open architecture also allows 3rd parties to offer software products and services to help address recurring customer needs - generating an entire market of SSRS add-ins and extensions.
As an example of meeting market demand, well-known ISV SoftArtisans supports customized SSRS design and rendering extensions in their highly acclaimed OfficeWriter product. With these extensions, businesses can create native Microsoft Excel and Microsoft Word reports with SQL Server Reporting Services. And, unlike the out of the box rendering extensions provided by SSRS that output static 'copies' of SSRS reports into Word and Excel, reports generated by OfficeWriter enjoy full 'native' functionality including formatting, formulas, macros, data-bound charts, and a host of other goodies. It's therefore no wonder that this extension has met with great success - garnering a finalist position for Best Business Intelligence Product at the 5th Annual Best of Tech Ed in Boston of 2006.
SoftArtisans to the Rescue While SQL Server Reporting Services is without equal in terms of affordability, approachability, extensibility, stability, and (increasingly) ubiquity, it isn't without a few warts. One of the main problems that SSRS suffers from is the inability to render reports with 'rich' Excel (or Word) functionality. While lacking no MS Word support whatsoever, out of the box SSRS provides the ability to export reports to Excel, Tiff, Adobe Acrobat, and a handful of other document types - which is really quite spiffy. The problem, however, is that when reports are exported to Excel SSRS doesn't provide any support for formulas, macros, conditional formatting, and a host of other 'rich' functionality that most Office users have come to rely upon in making sense of data. Worse, while SSRS does output charts to Excel, it renders them as static images - disconnected from the data they are graphing. Of course, this is better than nothing, but in today's world where organizations and users demand ever more complex solutions, stat data and missing functionality just isn't enough.
Which is where SoftArtisans enters the scene - and shines. With SoftArtisans' OfficeWriter, native SSRS functionality has been extended to allow SQL Server Reporting Services reports to be faithfully rendered in Excel and Word - with all of their native formatting and functionality completely intact. Better yet, leveraging OfficeWriter for natively rendered reports doesn't require you to update all of your existing reports to a new proprietary format. Nor does it 'break' existing functionality when you do chose to upgrade. With OfficeWriter-enabled reports, specialized rendering instructions for each report are merely injected into the RDL descriptor for each report - and processed only when routed through the customized OfficeWriter Renderer.
With OfficeWriter SSRS users get the 'best of both' worlds from a reporting standpoint. Because OfficeWriter just extends existing report definitions, none of the existing SSRS benefits are lost. This means that SSRS users retain access to scheduling, granular security, an easy-to-use web interface, quick development cycles, support for heterogeneous data sources, and the ability to export reports in a number of different formats.
SoftArtisans OfficeWriter
With OfficeWriter, Reporting Services reports can be designed directly from Word and Excel with the OfficeWriter Designer - a special toolbar that is very easy to use. This gives Microsoft Office users the ability to modify existing reports without requiring them to wait on developers, open Visual Studio, or even leave Office. Office users can even create their own reports - but this functionality is disabled by default as most organizations prefer to use OfficeWriter Designer to help create a layer of separation between logic and presentation. Under this paradigm, developers and IT professionals can create data models joining data from various sources and place it into a report definition where business users can modify the presentation of the report data to their heart's content (assuming they've been given necessary permissions).
Once designed, reports created or modified with the OfficeWriter Designer are modified with a special bit of base-64 encoded goodness that is 'injected' in to the report's definition file. On the reporting server, these extra instructions are completely ignored unless the report is requested for export via OfficeWriter's custom rendering extensions. This means that any existing reports modified by the OfficeWriter Designer will continue to function just as they always did. In fact, they can still be natively exported to low-functionality versions of Excel. However, when requested for export via the custom OfficeWriter rendering extensions, the base-64 encoded goodness is used to recreate a native Excel or Word document that faithfully matches the template created in the OfficeWriter Designer. With this template hydrated on the server, OfficeWriter's rendering extensions then replace specialized 'data-markers' with actual report data pulled from the datasets defined in the report's RDL file. The result is a beautiful fusion of Reporting Services data into a completely native Word document or Excel spreadsheet that preserves native formatting and functionality as defined during design.
Adding Runtime Control to Reports
Better yet, because OfficeWriter is just generating native Word and Excel documents on the server and programmatically populating them with Reporting Services data, it is possible to 'intercept' rendered reports and use the object models exposed by OfficeWriter to programmatically add embellishments, modifications, and other formatting as desired. In this way a single Reporting Services report can be customized for individual users, or specific regions and needs. For example, a sales company that wanted to give 'customized' quotes to customers could use a single SSRS report to output data with various parameters, but contact information within the report could easily be switched programmatically for each sales associate creating the report.
Best of all, using OfficeWriter to dynamically modify reports on the fly lets organizations meet complex needs without the need to create hundreds of report variants. With functionality like this, it's no wonder that OfficeWriter is gaining critical acclaim for its ability to extend and improve upon the existing success of SQL Server Reporting Services. |
