SQL Server Reporting Services

I don't think this is a technology I've ever mentioned before, partly because the reports I build for the applications I write tend to be embedded in the application. But that doesn't mean that I haven't dabbled with it and my increasing interest in Business Intelligence has meant that I've spent more time with it over the past few weeks!

So what have I learnt? Well for a start Reporting Services is a very capable reporting tool, even the free version that you can deploy with SQL Server Express is surprisingly capable, only missing things like the ability to schedule the running of a report or edit a report and limited to querying a database on the same local server. Since the free version still includes the full web services API (application programming interface), for a developer familiar with web services the former limitation is hardly a problem as building a small tool to automate running a report is very easy. As for the latter problem, well I think it would only be larger, more active databases that would find sharing reporting services on the server a problem. As for editing a report, well in my experience reports tend to first be written and deployed by developers and if users do need the ability to edit a report all I can suggest is that the database is upgraded to SQL Server 2005/2008 Standard or Enterpise which includes that ability.

To access a report the user navigates to a web site on the server on which Reporting Services was deployed. The home page then lists the reports, data sources and folders (if deeper organisation of the reports is required) that have been set up. Each report can be secured so that only those users that should have access to it can actually see it, this is the case even on the free version.

To run a report the user clicks the link for that report and the system then renders it in the browser window, from there the user can export to a variety of formats such as PDF and Excel (on the free version) with other formats (XML, TIFF, CSV and HTML) also available on the full version. To be honest I think that in most cases having PDF and Excel exporting capability is enough.

So if your organisation does have a reporting requirement using SQL Server Reporting Services is certainly worth examining and if the limitations of the free version are not a problem (there are ways to work around the local server limitation) then the only costs incurred will be developing the report itself. While writing a report is hardly difficult, the retrieval of the data in the first place does require someone with some knowledge of how to query the database.