To create a report, you 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