Reporting Services for Beginners Part One

So why SSRS?

First of all the good news:- SSRS gives nontechnical end users and programmers a server-based reporting platform that will generate any kind of report from any kind of data source for both desktop and Web applications. It allows you to customise your information to suit the preferences of a wide variety of users of information.Now for the really good news:- Do you own a copy of Microsoft SQL Server 2000 or 2005? If so, Reporting Services already comes bundled with SQL Server? Are you aware how this service can be used as a platform for report authoring, management and delivery? Well if you do then stop reading, if you don�t continue.

SSRS enables organisations to quickly transform important data into shared information for those crucial decisions, all at a lower total cost of ownership. Licensed as part of standard SQL Server, SSRS is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional paper-oriented reports and interactive Web-based reports.One of the biggest problems that Reporting Services solves is providing a simple means for organisations to deploy interactive and attractive reports to the Web and Web Services. (Company intranets being a useful deployment source)

See Figure A for a high-level look at the Reporting Services architecture.

Figure A Reporting Services Architecture (Diagram provided from official Microsoft documentation)

SSRS, due to its robust but user friendly architecture, is an obvious choice for Enterprise or in-house reporting, for product management, sales, and human resource and finance departments. Its flexibility makes it ideal for usage in applications as well. SSRS offer various delivery methods, from ftp to email and it provides various rendering formats therefore removing the complications of business-to-business reporting. Similarly because of its flexibility and industry standard security model, it makes extranet and secure internet reporting easily achievable.

So what exactly is SSRS ?

The reporting system comprises of two main components.

1. Report Server & Report Server Database
Report Server is an integrated web service which controls report generation and management. Report Server database is a SQL server database which is used as a data dictionary of reports (catalogue, groups) and caching. SQL server agent is used for scheduling the reports. The web services and database can be installed & configured on its very own server or can happily sit on an existing database server.(Note � does require .NET Framework & IIS installed too)

2. Report Manager
An ASP.NET based Web interface for managing the reports, setting security and user permissions.

And what can it do ?

Reporting services were designed with concept of disparate data sources in mind. This is where SSRS is so attractive to many organisations that may possess many different databases and many different data sources. A single report can retrieve data from multiple locations and render it to make it look like it's from a single source.

It provides built-in windows authentication security, however one can write a custom security wrapper to fit particular enterprise needs if required.With SQL Server reporting services, multiple delivery methods and formats work like a charm.
Gone are the days of having to construct a spreadsheet report and then converting it into a PDF file to email out every Monday morning at 9am sharp, and even then it needs to be published on your company intranet. (And that�s without having to integrate it into your business application !!)

You design a generic report and SSRS takes care of everything else. It allows you to export it into many different formats (including HTML, PDF, CSV, XML and even old Excel).
But why all the excitement I hear you cry? There are many reporting tools with differing capabilities out there on the market � what�s so special about this one?

How does it compare to other reporting tools ?

Here are some comparisons with Business Objects (now taken over Crystal Reports too!) and Cognos� new tool ReportNet. I must state here I only have personal experience of SSRS and Business Objects. I have not actually used ReportNet myself, but having read reports (excuse the pun!) and articles on the tool I feel I still qualify enough to at least offer my comparisons and judgement (albeit good or bad).

The one major consideration for companies is usually �how much is this reporting tool going to cost us?�
Both Business Objects and Cognos have been vying for the No 1 spot in the Reporting Tool charts for many years now and are each priced comparably and are similar in the functionality and scalability of the platforms.

There is no doubt there has been a lot of development on both of these tools but with the increase in development has come an increase in cost.
Many companies these days have standardised on MS SQL Server as their infrastructure. The obvious advantage SSRS has in terms of pricing is that it comes as an extension of SQL Server.
Why bother looking into purchasing other reporting tools if you can simply leverage your investment whilst providing quality reporting at no additional cost?

Ok so it�s more cost-effective but is it easy to use or learn to use?

All good reporting tools should come with user-friendly wizards to help get you started. Even MS Access had a half decent wizard (much improved in SSRS! but with the same general Microsoft look and feel!). Crystal Reports also had an easy-to-operate wizard.

Business Objects however, does not have a wizard, it has opted for a drag & drop paradigm for creating its reports.
They use �universes� (which contain all the data elements of a database), the user can simply drag elements from a universe to construct a new report.

Personally, I feel a good old fashioned wizard can be helpful in quickly creating reports as well as learning how to design them in the first place (which for the non-technically minded amongst us always helps).

There is a lot of parity among all the different reporting tools available on the market when it comes to things like deployment, administration, parameterisation, formatting and security (ok maybe not MS Access in terms of security!).

But SSRS has more drill-down capabilities, more subscription options and an ease and familiarity about its use. SSRS can be bolted onto many companies� training plans with Microsoft, can aid enhanced certification of its DBAs and as SQL Server itself develops in the future so will SSRS.

Reporting Services is scalable, secure and robust.
It supports the complete reporting lifecycle by including tools for report creation, execution, distribution, and management.
New users can have Reporting Services installed and new reports published within a matter of hours instead of days or weeks. And timeliness is an important factor too when deciding upon which reporting tool to go with.

My workplace had been using SQL Server for approx 5 years alongside reporting tools like Cognos and Crystal Reports, without knowing the capability and potential of SSRS.
It most cases because default installations were not carried out, dare I say it, some didn�t even know SSRS existed!

Reporting Services is a .NET Framework application that supports not only SQL Server 2005 but also SQL Server 2000, OLE DB, SQL Analysis Servers, ODBC data sources, and Oracle � yes even Oracle.
And if you implement a mixed SQL Server / Oracle RDBMS infrastructure like we do as well � then the advantages become even more evident.
You can even use SSRS to report on the wellbeing of your Oracle databases!

Another plus point of SSRS is its ability to cache report-based data that changes infrequently, along with support for a snapshot-based pre-execution of reports.
This is ideal for those weekly / monthly reports that are viewed on demand by your bosses!

Reporting Services provides a variety of delivery options.
These include a rendering engine in addition to an on-demand delivery mechanism. The rendering engine enables reports to be delivered in a variety of formats and targets different device types.

Reporting Services provides both an on-demand and a subscription-based report delivery model.
The on-demand model of report delivery allows users to dynamically request reports from the Report Server.

Additionally, administrators and users are able to schedule a specific report and - within the context of a subscription - are able to receive reports according to the refresh interval defined in the reports.
On-demand report access is based on a browser model and scheduled report delivery is driven through a subscription model.

With SSRS, different users can receive reports tailored to their specific needs.
E.g., user A may want her report in PDF format, but user B may wish to receive the same report as an Excel spreadsheet attachment to an e-mail message.
Because let's face it no 2 users are the same in terms of their demands or requirements.

The only dilemma you have here is whether you allow certain users, with the proper permissions to create or amend their own reports or have your DBAs or other administrators create them on their behalf. I know which I would prefer!

So, Reporting Services offers a vast number of features and many advantages over its competitors. Get Reporting Services installed and configured and take it for a test drive. I think you will be surprised at the possibilities it offers, and the ease with which you can create, manage, and publish your reports.

I have only touched upon the capabilities of Reporting Services here, in Reporting Solutions for Beginners Part Two, I will discuss in more technical detail how SSRS works and how you can use the data management capabilities of SQL Server to deliver powerful, secure and scalable reports necessary to drive better business decisions.

Contact us to discuss your options and get SSRS benefiting your company today .

If you like this article you can sign up for our monthly newsletter . There's an opt-out link at thebottom of each newsletter so it's easy to unsubscribe at any time.

Click Here