OfficeWriter for
SQL Server Reporting Services

by Satya Shyam K Jayanty

Read Review on SQL-Server-Performance.com

Making instant business decisions requires instant access to current relevant information, and IT department systems must deliver the right information to the right person at the right time. As you are aware, traditional systems that include system packages, third-party applications, and home-grown customized applications are addressed to particular needs and often tend to be expensive to develop, not user-friendly and time consuming. Microsoft has addressed this area in SQL Server 2000 by providing SQL Server Reporting Services as a comprehensive solution in the area of reporting, which includes authoring, managing and delivering reports that can be produced in paper, or in an interactive format by using web-based reports. For more information on SQL Server Reporting Services, you can download it from http://www.microsoft.com/sql/reporting/productinfo/trial.mspx.

SQL Server Reporting Services is server-based reporting platform that you can use to create and manage tabular, matrix, and graphical free-form reports from multiple data sources. One of the features of Reporting Services is that presentation processing occurs once the data is retrieved, enabling multiple users to review the same report simultaneously in formats designed for different devices.

SQL Server Reporting Services utilizes .NET technology to integrate a variety of heterogeneous data types to deliver information in variety of formats. Using this tool, developers can use Report Designer, with the help of Visual Studio .Net that works as a graphical tool by generating reports using the Report Definition Language (RDL) format which can be customized with any RDL-aware tool.

The Reporting Services layer runs as a middle-tier server, as a part of your existing server architecture. In order to install SQL Server 2000 Reporting Services, you need to have SQL Server 2000 installed for database activities, and Internet Information Services as a web server. The report server engine takes in report definitions, locates the corresponding data, and produces the reports. Using Reporting Services, you can interact with the engine through the web-based Report Manager, which also lets you manage tasks like refreshing schedules, along with notifications. In addition to the paper-format reports, end-users can view the report output from a web browser, and can export it to PDF, XML or Excel (viewed as static images) with a click of a button.

It is more than a year since Microsoft released SQL Server 2000 Reporting Services. After extensive usage of Reporting Services, users have called for the features like end-user ad-hoc reporting. The release of SQL Server 2005 Reporting Services includes this highly anticipated functionality.You can consider Reporting Services 2005 as a new product because it’s finally fully integration with SQL Server 2005 – the platform it was originally paired with. Some of other features in this latest release are multi-value support for parameter selection, sorting of data in report, a new XML data provider that permits data access from a URL or Web services, and the addition of 64-bit support.

Overview of OfficeWriter for Reporting Services

It is a standard phenomenon that most companies are accustomed to Microsoft Office tools, such as Excel and Word. As corporate business intranets grow, so does the need for the seamless management of data from Microsoft Office products. Developers are always on the lookout for products that enable them to create new and interesting .NET applications and clients are keenly looking forward for easy ways to access the end result in the form of reports using an integrated product.

The web browser has also become one of the most widely used mediums in commercial applications for the distribution of business reports users. There is no doubt this feature allows for scalability and, therefore, the same is required of web reporting solutions.

How about manipulating enterprise reports in the form of Word and Excel documents, with an enhancement of web-based reports where your employees and customers can access the data reports easily with a few clicks?

One such solution is provided by SoftArtisans’ [http://www.softartisans.com] OfficeWriter for SQL Server Reporting Services, a server-side application integrated with .NET. It has been developed and designed to generate presentation-quality reports in native Excel and Word file formats that users can browse from a web browser.

Report generation has become a part most .NET applications, as the presentation of reports has been changed with the evolution of many third-party products, such as OfficeWriter. OfficeWriter report generation, in native formats, enables users to sort, manipulate, and alter spreadsheets and documents on their machine in the Microsoft Excel and Word environment that they are already comfortable with.

SoftArtisans OfficeWriter, which already sells as a reporting product that generates and delivers the reports in Microsoft Office formats, extends this functionality to support SQL Server Reporting Services. The Reporting Services-integrated product [http://officewriter.softartisans.com] is a web-based tool that opens, modifies, and delivers native Word and Excel documents as reports, over the web without having the need of Microsoft Office in a SQL Server environment.

OfficeWriter vs. Microsoft Office

OfficeWriter consists of the WordWriter and ExcelWriter components, which are also integrated with Reporting Services. The documents generated by OfficeWriter are in their respective native formats and they preserve all the features of the original product. The performance is not hindered when the usage of the reports is quite high with concurrent users.

The main advantage of OfficeWriter is that it can be used as a server-side application without having a need to install Microsoft Office on a server. The scalability of OfficeWriter can handle creating spreadsheets and documents for just a few users, or many users, while Microsoft Excel and Word only works well for a small number of users. This feature can be noticed when multiple users want to view same spreadsheet or document simultaneously. The ability to avoid installation of Microsoft Office on a server can spare server resources, which are best used when multiple users are accessing the applications at the same time.

Finally, on the aspect of licensing, if you are going to handle Microsoft Office on the server, all of the clients that access spreadsheets or documents from the server will require a Microsoft Office client license. Microsoft has already advised that it is impossible to run Office Web Components on a server that is accessible to users on an enterprise-wide network. Whereas OfficeWriter, hosted on the server-side, does not require a Microsoft Office client license, and it is valid to use free programs like Excel Viewer for spreadsheets or Quick View for documents.

Usage and Functionality

OfficeWriter enhances productivity when integrated with SQL Server Reporting Services by allowing users to design and deliver their reports in native Word and Excel. OfficeWriter for Reporting Services can be broken down into two pieces:

By using OfficeWriter, the reporting format enables fully-functional design, publishing, and delivery of reports from Microsoft Excel and Word using SQL Server Reporting Services or .NET. This product preserves and maintains all of Excel’s and Word’s formatting and styles, including advanced features such as VBA and macros.

OfficeWriter gives you the ability to use all of Microsoft Excel’s formulas and functions in spreadsheet reports. This simplifies web reports with the power of spreadsheet-style formulas. The amount of code required to write an impressive-looking report with OfficeWriter is minimal. Similar to an Excel application, you can create multiple worksheets and access their cells in the formulas, which will allow you to modify the visual aspect of reports.

Formatting can be added to a cell by setting font, horizontal alignment, locked, number, vertical alignment, and text wrap properties. This can also help to create styles that contain color, font and format options for use in multiple cell or ranges.

Pivot tables in Excel are an interactive way of representing a table that quickly combines and compares large amounts of data. You can rotate rows and columns to see different summaries of source data and display the details for area of interest. Using pivot tables, ExcelWriter allows the analyzing of related totals, especially when there is a long list of figures to sum and when you need to compare several facts about each figure. The interactive summary of table allows the user to change the view of the data where the spreadsheet is equipped with automatic calculations.

Installation Requirements

The following are the details about how to install of OfficeWriter on server and client sides.

Server-side installation requirements:

Client-side installation requirements:

When you first start the install program, you get this introduction screen.

Next, you are reminded that you must have the necessary J# components already installed.

Next, you need to specify the directory to install OfficeWriter, and by default \Program Files directory is chosen. If you need to change the directory, then click on the Browse button to specify the required location.

Next, choose the features you want to install, including the designer for Excel and/or Word.

The OfficeWriter installation includes Reporting Services Integration that allows the generation of reports in the native Microsoft Office formats. Server-side integration with SQL Server Reporting Services requires ExcelWriter.NET and WordWriter.NET. As a prerequisite, you must install the complete SQL Server Reporting Services package, otherwise you will get the message ‘Reporting Services Integration will NOT be installed’ as per the screenshot below:

If you want to install ASP.NET samples to use under OfficeWriter, you must select .NET solution option in order to install the samples.

Otherwise you will see the screen below:

Automatic and Manual Installation

Installing OfficeWriter on a server is bit different compared to on the client. When you choose to run the automatic installer for OfficeWriter, copies of the OfficeWriter Assistant CAB files are placed in several locations on the server.

The DLLs are not registered on the server during the automatic installation because the objects do not need to be instantiated on the server. The DLLs are required for registration on any client machine that browses the OfficeWriter Assistant as samples on the server.

Manual installation of the OfficeWriter Assistant works as a client-side control, so there is no need to register the DLLs on the server. The DLLs are distributed to client machines in a CAB file format which is downloaded to the client by means of an <OBJECT> tag embedded in an HTML page.

Using the automatic installation, the screens guide the user to install OfficeWriter designer on the client, but you first need to close all Microsoft Office applications and double-click on Installer.exe file to follow the on-screen instructions.

MS Query is a part of Microsoft Office, but may not be installed by default. To install MS Query, you need the Office Installation CD ready and follow:

Then you will be prompted to install MS Query and follow the on-screen illustration to complete the installation.

When used for the first time, Internet Explorer will prompt the user to install the ActiveX component in the browser with a security dialog. If accepted, then only the OfficeWriter Assistant will be installed.

During the installation process you will be asked for the license key, which is required to confirm the order. The IIS service must be reset before the new license will take affect. If you do not input a valid license key then the free version of OfficeWriter will be installed.

Next, you will see the screen (below) that configures the product installation with all the pre-supplied values.

OfficeWriter In Action

As you may be aware in Reporting Services, the Word format is not recognized or supported. But with the addition of OfficeWriter, Reporting Services delivers the reports directly in the Word or Excel formats. OfficeWriter allows separate roles of query developer and can create shared data sources and build queries.

OfficeWriter delivers reports directly from the Excel or Word toolbar to browse the Reporting Services server and display rendered reports directly in Microsoft Office.

The process of creation of Word or Excel with OfficeWriter is straightforward. For example, to create a Word report from an existing RDL file, you need to:

• Create a new Word document using OfficeWriter Designer
• Using the OfficeWriter Word Toolbar, set up a new query by using the OfficeWriter Designer toolbar under the Word menu bar at the top of your document.
• The Add Query button allows you to add new database queries to your report template. If you opened a report definition (RDL file) that was not created by OfficeWriter Designer (for example, an RDL file created in Visual Studio .NET), the Add Query button will not be active.
• Create bookmarks in the Word document by inserting fields from the query. Open the Insert menu and select Bookmark... to open the Bookmark dialog. Enter a bookmark name and click Add.
• Deploy the report to ReportServer. During this process, OfficeWriter will generate the report .RDL file, which is saved on the same folder where the original Word document is located.

For example, to create an Excel report from an existing .RDL file, you need:

• To create an Excel spreadsheet file using OfficeWriter Designer.
• Using the OfficeWriter Excel Toolbar, click the Open Report button.
• Navigate to the folder where the Excel report is created, then click Select-Query on the toolbar to select query from the drop-down list.
• Open Microsoft Excel and create a new file.
• Click the Open Report button on the OfficeWriter toolbar.
• If your report definition file was saved on a local or network drive, navigate to the folder containing the file, select it, and click Open. If your report definition file was deployed to Reporting Services, click the Retrieve on the bottom right corner of the dialog box, and continue.

• Place the Cursor in the required cell and click insert field to select the required field(s)
• From OfficeWriter Designer Toolbar, save the template as an .RDL file.
• To publish the report, select the drop-down to display the server or URL.
• Choose the server on which Reporting Services are installed and click Refresh.
• Select a folder for which publishing rights are enabled and click OK.
• Lastly, if the report is published with no errors, it will prompt ‘Publish Successful’ message dialog.

Tip: To verify that the report is actually running on Reporting Services, open a browser and go to the Reporting Services Report Manager. This displays the report, but only you can view the data set. Select a format drop-down and choose the Excel format. When you click on export and open the tool, it will open and display the report the way you created it.

SQL Server Reporting Services generates reports in the form of .RDL files; OfficeWriter [Open Report] button allows opening a saved report definition files (RDL). You can open RDL files on any drive on your computer, as well as from any accessible Reporting Services server. Excel output features such as pivot tables, VBA, Macros or advanced formulas and charts that are preserved by OfficeWriter.

Features

OfficeWriter includes this list of features:

The OfficeWriter designer is a toolbar add-in feature for Excel and Word that allows users to design a report template in Excel or Word instead of Visual Studio .NET. The template is integrated into the Reporting Services’ xml-based RDL format for publishing on Reporting Server.

This tool can open existing .RDL files in Excel or Word. These query fields will appear in a drop-down list on the OfficeWriter toolbar. The fields can be used to insert data placeholders in the report template and will not be able to create additional queries. When the file is saved, the template information will be added to the .RDL file.

There is another way of creating a new template with an existing .RDL file. The OfficeWriter Designer allows the user to set a data source and to create database queries in Excel or Word format. This feature allows the business user to create fresh queries and reports and be responsible for the content in their own format. This eliminates the requirement of learning of complex application development environment like Visual Studio .NET, but still enables the full advantage of Reporting Services’ report management, security, and release options.

OfficeWriter’s Reporting Services integration is available in both OfficeWriter Enterprise Edition and Standard Edition. This product is also available in Developer and Free editions with their own built-in limitations. In the Enterprise Edition, additional features include advanced formulas with Reporting Services aggregate functions, report parameters, and global variables.

An OfficeWriter report provides a list of export format choices depending on user’s choice. The various formats are differentiated as:

The OfficeWriter report format supports ASP, ASP.NET and J2EE technologies. This works as a complete server-side Java library for reading and writing Microsoft Office documents and spreadsheets.

The OfficeWriter Reporting Services Integration will not work on the Beta version of SQL 2005 (Yukon). This will be fixed for the release of SQL 2005 in November. OfficeWriter does not support the beta versions of the .NET 2.0 Framework (Whidbey). This will be fixed for the release of .NET 2.0.

OfficeWriter supports reports populated with a data from a variety of sources, including JDBC result sets from popular databases (MSSQL, Oracle, MySQL, DB2 etc.) or any data that is compatible with a Java object. The link libraries of OfficeWriter are simple to add to a variety of J2EE compliant application servers, including Apache, IBM Web Sphere, and BEA Web Logic.

Automation

SQL Server Reporting Services allows users to deliver report content in the format of their own choice, but you need to use the subscription feature that is included with Reporting Services.

SQL Server Reporting Services Report Manager includes a My Subscriptions page that organizes all of your subscriptions into one place. You can use My Subscriptions to view, modify, and delete existing subscriptions. However, you cannot use it to create subscriptions.

Within My Subscriptions, you can sort subscriptions by folder, report, description, trigger, last run, or status. All values are sorted alphabetically except for Last Run, which is in chronological order.

My Subscriptions shows only the subscriptions that you create. It does not list subscriptions that are owned by other users, even if you are added as a subscriber to those subscriptions. Data-driven subscriptions that a report administrator defines for you are also not represented here.

After creating reports with the OfficeWriter Designer there is no need to switch back from Microsoft Office to a browser to generate reports. In Reporting Services, the new parameters-support enables the user to create a spreadsheet with parameterized queries. After compilation of reports, the user can browse and execute reports from Reporting Services servers right into Excel or Word format, which adds full support from Microsoft Office to existing .RDL files.

The parameterization with OfficeWriter and Reporting Services allows the user to filter the data returned by a database query and display a subset of data in the report.

The compliance of OfficeWriter RDL files with Microsoft RDL schema enhances the user feature to edit the generated report in Visual Studio, format tables and text boxes with report definitions from existing reports. However you must preserve the tables defined in the generated RDL, all custom tags and data source names with field names.

Summary

The main advantage of using OfficeWriter instead of Microsoft Office as a server-side web reporting application is that it opens and manages Excel spreadsheets or Word documents on the server faster and more efficiently. You can see a significant performance gain when the document or spreadsheet is viewed by multiple users on a server wide network. There is no requirement of client license for usage of Microsoft Office products on server side, where you need only one license for OfficeWriter from the vendor to work out reports in extranets or intranets.

SoftArtisans offers extended priority support plans in three different formats: Silver, Gold and Platinum. To get more information on these support options please contact SoftArtisans at sales@softartisans.com. SoftArtisans offers free major product upgrades for one year and as well as online-support resources such as forums list and knowledge base articles.

To download evaluation software of OfficeWriter visit http://support.softartisans.com/eval.aspx. When you submit your email address then you will receive download instructions.