SQL SERVER – SQL Report Builder in dbForge Studio for SQL Server

Modern opportunities of electronic document management systems can significantly simplify the process of decision-making at an enterprise.

Along with the opportunities they provide, the traditional printed documentation still plays an important role by allowing to conveniently operate the required information by means of reports.

Creation of such reports, depending on the degree of complexity, can take a long time, therefore, to accelerate their creation, visual editors are traditionally used.

Some of these editors have only basic functionality. Others have extended reporting capabilities. The latter group of products includes the SQL report builder built-in to dbForge Studio for SQL Server.

The editor’s capabilities allow you to build a report according your SQL query, literally, within a few minutes. For this, it’s enough to call the Report Designer command from the main menu.

Select a report type in the opened dialog.

Select a connection and a data source.

Herewith, tables and views can be selected as a source.

In case if data, for example, from several tables should be returned by a query, a user query can be written.

After selecting a data source, specify the columns, by which the report will be built.

You should also specify how this data will be presented in the report.

At the final stage, it remains just to specify the name for the report and click the Finish button.

After the wizard is finished, the report editor opens, in which it is possible to fine-tune the appearance of the report.

To preview the report, there is the Preview button provided.

After the report is generated, it can be both printed and exported to one of the supported formats.

As you can see, creating and editing the report in SQL report builder does not take a lot of time, allowing to streamline the process and reduce development time.

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

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)

SQL SERVER – Migrate a SQL Server Reports from one server to another server

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.

  1. Command-line tool (RSMigrationTool.exe)
  2. 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.

Here is the link to Download the SQL Server Reporting Migration Tool.

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

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)