To experience the awesomeness of OfficeWriter, I suggest you here to downloadthe 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 OfficeWriterrenderer in SSRS Click here to download a free trial visit.
How many time you have felt that there should be need of the tool which help you to migrate SQL Server Reports from one server to another server. Well, I am glad to see this migration tool for migrating reports from SQL Server 2008 R2 and later version. This tool uses powershell for migration script. Here is the requirement of source server and target server. Source server must be native mode using Windows authentication. Target server must be SharePoint integrated mode. The web application must be using Windows classic authentication mode.
You can migrate it using any of the following methods.
Command-line tool (RSMigrationTool.exe)
GUI tool (RSMigrationUI.exe)
Either of the tool will generate a powershell script which will migrate the reports. This tool also generates the log file which provides the additional details regarding the migration.
The three most important components of any computer and server are the CPU, Memory, and Hard disk specification. This post talks about how to get more details about these three most important components using the Management Data Collection. Management Data Collection generates the reports for the three said components by default. Configuring Data Collection is a very easy task and can be done very quickly.
There are many different ways to get reports generated for CPU, Memory and IO. You can use DMVs, Extended Events as well Perfmon to trace the data.
Keeping the T-SQL Tuesday subject of reporting this post is created to give visual tutorial to quickly configure Data Collection and generate Reports.
The data collector is a core component of the Data Collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data, unlike SQL Trace.
Let us go over the visual tutorial on how quickly Data Collection can be configured. Expand the management node under the main server node and follow the direction in the pictures.
This reports can be exported to PDF as well Excel by writing clicking on reports.
Now let us see more additional screenshots of the reports. The reports are very self-explanatory but can be drilled down to get further details. Click on the image to make it larger.
Well, as we can see, it is very easy to configure and utilize this tool. Do you use this tool in your organization?
If you are following my blog, you already know that I am more of “T-SQL and Performance Tuning” type of person. I do have a good understanding of Business Intelligence suit and I also do certain training sessions on the same subject. When I was writing the blog post for T-SQL Tuesday #005 – Reporting, I realized that I have written a post that clearly explains how to generate reports using SQL Server Management Studio.
Here is a quick recap on how one can use SSMS and out-of-the-box reports which can help many developers.
Please note that they can be resource-intensive as well, so please use SSMS carefully.