Enhanced Reporting in SQL Server 2008

As a developer, many times you need to create reports from data sitting in a

database. Although many developers continue to use traditional methods of

creating this using code, an easier way is

 to use SQL Server Reporting Services. The upcoming version of SQL

Server 2008 has a large number of enhancements that can make the task of

creating reports for your applications much



easier.

To create a report, yosu need to have installed the Reporting Service as part

of the SQL Server installation. A very important new enhancement is available

right here itself. Reporting Services no longer requires that Internet

Information Services (IIS) be installed beforehand. In fact, Reporting Services

itself contains a web server that allows developers and end users to interact

with the service directly.

Reporting Web Server



The built-in Web server of Reporting Services uses the kernel level HTTP
handler, HTTP.SYS that is part of Windows 2003 and later and is used by IIS as

well. This means that unlike the

SSRS Architecture

 Cassini web server which is part of Visual Studio and used for

development purposes only, the Web server that is part of Reporting Services is

a full-fledged, enterprise grade one that can be used in production as well.

Creating a new data source window where you can either create one or use a shared source.

The web server also has a number of enhancements and optimizations specific

to Reporting Services — something that IIS doesn't have. This also takes care of

all the authentication mechanisms (windows, basic, anonymous), memory management

of the service, providing a web service end point and hosting the Report Manager

web site for end users and administrators. It also allows migration of IIS

settings from previous versions of Reporting Services 2000 and 2005.

Building Reports



To build a report you need to have the SQL Server Business Intelligence

Development Studio or Visual Studio 2008 installed. In either of these you can

create a new SQL Server Report Project using the installed template.

The query designer window where you can design, write and view results of the query.

The 2008 version of the design surface has been optimized for quick usage of

entities. First of all, the designer itself shows you where to start by

providing a link in the middle of the surface asking you to define a data source and a data set. The data source is

the place from where you wish to get the data to display in the report. The data

set is the actual query you wish to run including any filters (WHERE clause),

fields (SELECT columns) etc. A new and improved

wizard allows you to do these tasks very easily.

The Report Designer tool, looks like a part of Office 2007 and has features that let end users work on report design without requiring Visual Studio or BIDS.

The interesting part is that you can now build a report without going into

Visual Studio at all. This is useful when you wish to allow end users or DBAs to

create reports, but do not want to install

Visual Studio or even SQL Server on their machines. A new application

called the Report Designer is available to install on end client machines and

allows all the functionality that is available through Visual Studio as well.

Report Designer



This new tool allows DBAs and end-users familiar with the database and writing
queries to design their own reports without having to know or learn Visual

Studio. This application can be installed as a full installation on their

machines and it provides an easy to understand interface that uses the Office

2007 Ribbon experience.



The designer allows these users to create reports, view and edit queries, change
the layout of reports, add controls to the report like charts and tables and

even preview it without publishing to the Report Server.

A number of UI enhancements like the Row and Column Group Task Panes, an “Add

Total” command on numeric fields that automatically adds a total row at the

correct level of grouping, and access to the global variables directly make it

an easy tool to use to create even complex reports.

Using the Tablix to merge dynamic columns and static columns.

Introducing the 'Tablix'



Earlier versions of Reporting Services had two controls — called the Table and
the Matrix. The Table was a generic tabular data display control where the

columns, where fixed by the rows, would grow depending on the data itself. The

Matrix on the other hand allowed for both dynamic columns and dynamic rows —

akin to a PivotTable in Excel.

SQL 2008 Reporting Services has a new control called the Tablix. This is a

combination of the Table and Matrix controls that now allows you to create

certain types of reports that were impossible to do earlier due to the nature of

the earlier controls. Take a look at the two examples shown here.

The first contains an example of a matrix and a table. The table has a static

column. However combining both of them was not possible earlier. Using the

Tablix however, it is easy to combine dynamic and static columns into the same

tabular format.

The second example contains two matrices each having dynamic columns. We can

now combine both these sets of dynamic columns into one tabular format where

each dynamic list can grow independently of each other.

Gauge charts let you create scorecards easily.

Rendering



There are three improvements in the rendering of reports in SQL 2008. First off,
exporting a report into Comma Separated Values (CSV) is much better and the

output is clean enough for human reading as well. Next, in case of a report

having a sub report, exporting it to Excel now renders the sub report as well

and hides it properly in Excel.

And finally, there is a new export format option available in SQL 2008

Reporting Services. You now have the option of exporting a report directly to MS

Word format. In the current betas it exports to the Word 97-2003 DOC format, but

it is expected that the ISO OOXML format (DOCX and XLSX) will be support by RTM

or by a patch upgrade later on.

Charts



SQL Server 2008 introduces a whole bunch of new chart types for use in the
reports. A list of the chart types that you get are:

• sColumn
• Cylinder
• Line
• Stepped Line
• Pie
• Pie Callout
• Doughnut
• Funnel
• Pyramid
• Bar
• Area
• Range
• Polar
• Radar
• Range Column/Bar
• BoxPlot

Each of these have a number of UI features (like exploded Pie version, Stacked
or 3D bar/Column, etc.) as well. Over and above these, some more new features

like having secondary axis, multiple charts with alignment and scale breaks are

also allowed.

A completely new chart type called the Gauge has also been introduced in this

version. You can use this to represent dashboard items like KPIs, or scorecard

components in your reports.



As you can see, SQL Server 2008 Reporting Services has a huge number of
enhancements as well as new features that have been introduced. If you are a

developer who needs to query data and display it in a report, this is an option

that you should consider.

Vinod Unny, Enterprise InfoTech