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 (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Configure Management Data Collection in Quick Steps – T-SQL Tuesday #005

This article was written as a response to T-SQL Tuesday #005 – Reporting.

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.

Please note:
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.

From Book On-Line:

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?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Three Posts on Reporting – T-SQL Tuesday #005

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.

SQL SERVER – Generate Report for Index Physical Statistics – SSMS

SQL SERVER – Out of the Box – Activity and Performance Reports from SSSMS

SQL SERVER – Configure Management Data Collection in Quick Steps – T-SQL Tuesday #005

Junior developers and DBA can use these reports right away and can also start learning and exploring most database performance issues with the help of Sr. DBAs.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SQL Server RDL Specification

Report Definition Language (RDL) is an XML-based schema for defining reports. The goal of RDL is to promote the interoperability of commercial reporting products by defining a common schema that allows interchange of report definitions. To encourage interoperability, RDL includes the notion of compliance levels that products may choose to support.

Download the RDL Specifications for SQL Server by clicking the links below.

Abstract courtesy : Microsoft

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Download SQL Server 2005 Report Packs – SQL Server Sample Reports – Report Templates

Note:   Download SQL Server 2005 Report Packs by Microsoft

SQL Server 2005 Reporting Services is a comprehensive, server-based reporting solution designed to help you author, manage, and deliver both paper-based, ad hoc, and interactive Web-based reports.

Each report pack consists of a set of predefined reports, a sample database, a readme file, and an End User License Agreement (EULA). You can use these sample reports as templates to quickly author and distribute new interactive reports.

Report Pack contains following sample reports

  • SQL Server 2005 Integration Services Log Reports
  • SQL Server 2005 Report Pack for Microsoft Dynamics Axapta 3.0
  • SQL Server 2005 Report Pack for Microsoft Dynamics Navision 4.0
  • SQL Server 2005 Report Pack for Microsoft Dynamics Great Plains 8.0
  • SQL Server 2005 Report Pack for Microsoft Dynamics Great Plains 9.0
  • SQL Server 2005 Report Pack for Microsoft Internet Information Services (IIS)
  • SQL Server 2005 Report Pack for Financial Reporting

Download SQL Server 2005 Report Packs

Abstract courtesy : Microsoft

Reference : Pinal Dave (http://blog.SQLAuthority.com)