OfficeWriter for SQL Server Reporting Services
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.
OfficeWriter includes this list of features:
- Integration with SQL Server Reporting Services.
- Report generation in the familiar formats that users are accustomed.
- Preserves the native format functionality of Excel and Word.
- Eliminate the need of Microsoft Office installation on a web or SQL Server.
- Compatible with both Java and Windows platforms.
- Allows for reuse of predefined queries, shared data sources, and report and query parameters.
- Ability to browse the reporting server directly from Excel or Word.
- Create advanced formulas with Reporting Services parameters, formulas and global variables, and add them to a template.
- As in the Microsoft products, you can use F1 key in dialogs to get any help on the tool.
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:
(a) OfficeWriter for Excel preservers all features present in designer template Excel workbook.
(b) OfficeWriter for Word that preserves all Word features.
(c) Standard Excel format can be generated from .RDL files.
(d) Standard HTML can be rendered from Reporting Services.
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.