SQL Reporting – How to Add Excel and Word Reporting to Your Own Application – An Innovative Approach

To experience the awesomeness of OfficeWriter, I suggest you here to download the tool and work along with this blog post.

The OfficeWriter API is a .NET library by SoftArtisans that makes it easy for developers to add Excel and Word reporting and document processing to their own applications. OfficeWriter allows users to take data from any data source and turn reports into dynamic, visual presentations. Without requiring Microsoft Office on the server, OfficeWriter is optimized for high-performance, scalable server use. The .NET API integrates with business applications, including those in SSRS and SharePoint.

Why use OfficeWriter’s API with SQL Server Reporting Services (SSRS)? The default rendering extensions for Reporting Services deliver flat, static output. In order to render for multiple extensions such as HTML and PDF, the basic report design in SSRS cannot accommodate specific features, such as multiple worksheets in Excel. This also means that certain features like charts cannot be dynamic in the output because not all of the rendering extensions in SSRS support Excel charts. Therefore, charts are exported as images. Additionally, until SSRS 2008 R2, there wasn’t a default rendering extension for Word. Before SSRS 2012 there was no means of exporting to XLSX or DOCX. Using OfficeWriter for Reporting Services corrects these limitations, allowing end-users to design reports in Excel and Word and make use of many specific features in those applications.

How Creating a Report in SSRS with OfficeWriter Works:

OfficeWriter fits into SSRS with two parts. First, the OfficeWriter Renderer is a server-side rendering extension for Excel and Word, providing Excel and Word features beyond the built-in SSRS export options. Second, the OfficeWriter Designer is a client-side add-in for Excel and Word, which allows users to design reports that utilize the OfficeWriter rendering extensions.

Designing a Report

The RDL file contains information about connecting to data sources, queries, as well as report design information. The OfficeWriter Designer adds its own data and design information to the report, which will be used by the OfficeWriter Renderer to export the report. The Designer sections off a part of the RDL for storing this information separately from the rest of the RDL file. In particular, the Designer saves a binary copy of the Excel or Word template file to the RDL and information about binding the report data to that Excel or Word template file.

Exporting a Report

When the report is exported using the Renderer, Reporting Services retrieves the data from the queries and data source locations stored in the RDL. It passes this data along to whichever renderer was selected for exporting the report. The OfficeWriter Renderer loads the copy of the Excel or Word template file and binds the data from Reporting Services to the template. The Renderer is actually using ExcelTemplate or WordTemplate to bind the data to produce the output.

In the new release of OfficeWriter, the new .NET Designer was introduced. The .NET Designer has better platform support (i.e. 64-bit Office) than the previous designer, moving from dependence on COM and VBA to being written entirely in .NET/C# using VSTO.

At the start of 2014 SoftArtisans also plans to release a major version upgrade; OfficeWriter version 9 is set to be released early next year with PowerPoint capabilities and a calculation engine for ExcelWriter, among other new features to provide more options for building reports through your own business applications.

The Big Difference

With the OfficeWriter renderer on the other hand, the charts will be native Excel charts with live data. Let’s see the difference in the following screenshots: “Product Sales Report.xlsx” generated using the OfficeWriter renderer in SSRS Click here to download a free trial visit.

To learn more about how OfficeWriter’s Excel Export functionality compares to that of SSRS visit here.

Reference: Pinal Dave (https://blog.sqlauthority.com)