SQL Data Analysis and Reporting

Overview

dbForge Studio for SQL Server offers the possibility of creating SQL data reports implemented in the well-known and user-friendly interface. With dbForge Studio for SQL Server, the routine of SQL data analysis and report creating is simplified to several clicks — that means you won't have to spend a lot of time and efforts for SQL report producing any more.

To start generating SQL reports, go to the Start page and select Data Analysis.

dbForge Studio for SQL Server enables to create various kinds of reports, namely:

• Data Report
• Chart Report
• Master-Detail Report
• Pivot Table
• Label Report
• Static Report

Let's take a closer look at each type of data reports, available in dbForge Studio for SQL Server.

Data Report

Data report is a formatted representation of SQL data that can be displayed on a screen, printed or saved to a file. SQL reports allow getting the needed data from a database and representing it in a human-readable form, and also providing a lot of capabilities for SQL data generalization and analysis.

When printing tables and query results data is presented in nearly the same form as it is stored. So, it is often necessary to present it in the form of a report that has traditional appearance and is easy to read.

dbForge Studio for SQL Server provides easy-to-use and fast solution for this goal which includes the fully-functional report builder, SQL Data Report Wizard for creating different types of reports, and a lot of report examples.

You can start creating SQL Server data reports by going to the Data Analysis tab of the Start Page, or from the Database Explorer window, the Query Builder tool and from the Data Editor tool.

With this feature you can generate a table, a chart or a list report by means of the controls collection.

You can create an SQL report, based on a table, presentation or a query, and set up the output settings.

For example, let's create the department sales report with the help of the Report wizard. We need to select the Data Report feature, located under the Data Analysis tab of the Start Page. Then we need to select Standard Report, set up a database and the query text, select the mapping levels of the output data, select query style, formatting, set up the report title.

Here is our result.

You can output an SQL Server report as an HTML file, save it in several popular formats or e-mail it at once. The generated report can be printed out. The search through the ready report is available as well.

Chart Designer

Charts are visual illustrations of your SQL data. In one glance, a viewer can come to an immediate understanding of the numbers.

Chart Designer, implemented in dbForge Studio for SQL Server, includes a rich chart collection, that will help you to select the best presentation for any purpose.

You can output the percentage-based data and choose the quantity of the displayed items (the rest of the items will be organized and be shown as Others).

You can start working with the Chart Designer form the Database Explorer window, the Query Builder tool and the Data Editor tool. The image below illustrates, how to start creating a chart from Query Builder.

The HTML View, having a lot of advantages, like the option to e-mail a report, or save it as an image, is available for all kinds of data reports.

Master-Detail Report

Master-Detail report is a data analysis tool, used to display data from a hierarchical data source. Master-Detail report represents a detail report within its master and can be used if a report's data source implements a master-detail relation between an object bound to a parent report and an object bound to a child report.

By setting up the parent table you can see the data from the child tables in the same window.

For example, with the data from Emp and Dept tables we can get information about employees from each department. To do that, you need to create a report, containing both tables.

In Data Source, select Edit Relations and set up the key, master and detail tables.

In Designer, double-click Detail Tasks, click Insert Detail Report and select "FK_DEPT_EMP" from the drop-down list.

Then drag the FK_DEPT_EMP table from Data Source and drop it on the created Detail Report Band.

There can be any number of detail tables, and you can set up any nesting.

Pivot Table

Pivot Table is a data analysis tool that converts large amounts of data into a compact and informative summaries ? pivot tables. You can rearrange your data by a simple drag of a mouse until you get the layout for the best for understanding of the data relations and dependencies.

With the Pivot Table feature, implemented in dbForge Studio for SQL Server, you can create an SQL summary report, analyse statistic information on the basis of a certain criterion, see the graphic interpretation of an SQL Server report, to output the summary information etc.

For example, let's create the Sales by Department and Employees report for the Emp and Dept tables, and create a chart, showing Sales by Employees.

You can create an SQL report, based on a table, query or view ? just setup it as Data Source.

• In Toolbox, select Pivot Grid.
• In Pivot Grid Task, select Run Designer...
• The Property Editor window opens.
• Click Retrieve Fields.
• On Layout tab, setup Row, Column and Data Fields, which can be saved or loaded.
• On the Appearances tab, you can set up font, color, back color and border color, as well as to change line visibility and thickness for each element of Pivot Grid.

The output information can be filtered and sorted from the general properties on the Fields tab.

The Pivot Table feature simplifies the aggregation process and the statistical information count. The obtained report can be printed out, exported to various document formats and even e-mailed in the required format by one click.

Label Report

The Label Report option allows creating SQL reports containing labels of the specified type. For example, we can output information about Employees as a card.

You can add the output settings for any report. To do this, in the Data Source window, right-click Parameters and select Add Parameter.

To enable a filter, click the triangle in the top-left corner of Report Designer to invoke Report Tasks. Add the filter to Filter String.

Here is our result.

As you can see, you can define separate settings for even and odd records. Label reports can be printed out, exported to various document formats and even e-mailed in the required format.

Static Report

A Static Report contains some static information and does not need a data source.

To create a static report, on the File menu, point to New, then select Blank Data Report.

To proceed with report creation, open the Toolbox window by pressing the Toolbox icon on the Data Report toolbar. Then double-click the selected control and type the text into it.

With help of the Rich Text control you can load RTF-files to Data Report ? simply drop a Rich Text control onto the report's Detail band, and modify the control's content (either by using the in-place editor or by loading data from an external RTF file).

The obtained SQL static report can be printed out, exported to various document formats and even be e-mailed in the required format by one click.

Summary

dbForge Studio for SQL Server provides powerful data analysis and reporting tools. The set of the most popular report types and rich controls will make your data speak for you. All SQL data reports can be saved in all popular formats and be e-mailed in one click.