Many of us who have installed Microsoft SQL Server haven't even noticed that there are some other products on the CD beside the DBMS itself. We install SQL Server and are blissfully unaware that we've glossed over a rather powerful reporting engine – SQL Server 2000 Reporting Services (SSRS).

SSRS is a server-based engine that links to data sources (not necessarily SQL Server ones – it'll work with any data provider for which the server has the appropriate native, OLE or ODBC driver) and runs reports on either a scheduled or an ad-hoc basis. You store report definitions on the server, and it takes the necessary steps to generate the data as required. Although the default way of working is to display results in the Web browser, the user is given a menu of options for alternative output, from which you can select a number of formats from the trivial (a text-based CSV file) to the complex (a PDF or a TIFF image of the report).

The most basic way to create a report is via the Report Wizard, which is one of the options presented when you create a new project in Visual Studio (SSRS integrates closely with Visual Studio, so it's worth making the investment in the latter so you get the benefits of its IDE). The first task is to create a data connection, which you do either by pasting an ADO connection string into the box provided, or by filling in a form and letting the system test the connection and generate the connection string for you. Next, you give the system an SQL query that it can use to pull the data from the tables (again, the option is there to use a query designer if you so wish). Once you've chosen the type of report you want (options in the wizard are a basic list or a matrix) you're asked to select the fields you want to use in the report, then you pick a look-and-feel from the list of defined styles and you're done.

For simple reports, this quick string of steps is all you have to do to begin getting sensible data out of the system – and even if you want to let the users enter parameters (e.g. the date range or product ID they're interested in) this is trivial to set up.

Obviously the complexity of the system increases in proportion to one's desire to put more and more information into each report, but so long as you're methodical, and you plan your reports sensibly, even quite intricate reports are far from rocket science. The more complicated the report, the more likely you are to leave the wizard alone and instead define the report parameters using the variety of screens that let you provide more intricate detail to the system. These boxes tend to be split into zillions of tab pages, each with a pile of semi-comprehensible content but as long as you take things steadily you soon figure out what everything does. Unsurprisingly, you can design the layout of your report using Visual Studio's Toolbox: just as a Windows application designer would drag textboxes, labels, buttons and the like onto the page, so the report designer can do the same with data fields, sub-reports and charts (not to mention simple shapes such as images, lines and rectangles that aid prettification).

One thing to bear in mind with Reporting Services, though, is that the speed of your reports depends entirely on how well you designed the database(s) and how sensibly you've thought through the queries that drive the data acquisition. Let's face it, if you've got a four-million row customer table and the report's trying to group things on an unindexed field, the output is unlikely to appear very speedily. It's therefore important to make the most of the other components SQL Server gives you (not least the Query Analyzer that comes with the DBMS, and which is essential for analysing and optimising your queries) before trying to deploy your reports. Consider also the impact of the reporting system on your DBMS – every time I've rolled out this type of package in a business, the management are so overawed by the power at their fingertips that the loading on the database climbs steeply as they dig enthusiastically into data they've not previously been able to see.

In short, SSRS is a powerful, yet often forgotten component of SQL Server 2000 which can bring tangible benefits to the average organisation.


If you're considering deploying SSRS, be sure to consider the knock-on implications in terms of the impact on the database systems upon which your users will be reporting.