Microsoft SQL Server Reporting Service (SSRS)

Overview

SQL Server Reporting Service (SSRS) is a server based reporting platform introduced by Microsoft. It’s reliable, comprehensive reporting platform distributed with Microsoft SQL Server since the introduction of ‘SQL Server 2000’ in year 2004.

Same as its competitors such as ‘Crystal Reports’, ‘IBM Cognos’, etc. SSRS also provides attractive features in the areas of data representation, report management and report viewing. SSRS is fully compatible with SQL Server, SharePoint  and uses Visual Studio environment. Yet it can be used with many other commercial and open source DBMSs as mentioned in the features section below.

There are 3 main stages in SSRS report life cycle. They are,

1. Report Creation – Report is defined using the ‘Report Definition Language’ (RDL) which is a XML based markup language. ‘Business Intelligent Studio’ which uses Visual Studio environment can be used to generate reports.

2. Report Deployment – Once the reports are developed they are deployed in a central location in the Reporting Server.

3. Report Viewing – Once successfully deployed, reports can be viewed through any web connection, expose them through a web service, or publish them directly to a SharePoint site. These published reports can be exported to many formats like PDF, Excel, TIFF, XML or print or even subscribe to by uses. Subscriptions are delivered as Emails or Publish to FTP servers.

More information regarding SSRS architecture can be found at SSRS home page in MSDN, http://msdn.microsoft.com/en-us/library/ms159106.aspx.

SSRS can be used to produce reports in a business enterprise or can be customized and integrated to custom applications which has a SQL Server based back-end. For whatever the requirement it’s always advantageous to analyze pros and cons of using SSRS to meet your reporting requirement.

Features of SSRS

• It’s fully compatible with SQL Server Database, SharePoint servers and Visual Studio development environment.
• Supports relational, multidimensional, XML or custom data sources such as Microsoft SQL Server, OLE DB, Microsoft SQL Analysis Service, Oracle, ODBC, XML, Report Server Model, SAP NetWeaver BI, Hyperion Essbase, TERADATA.
• Data can be represented in tabular, matrix, charts, images, labels and many other forms.
• DB logics can be loosely coupled from the business logics that are used to compose the report by calling the Database stored procedures from the RDL.
• Generated report can be viewed using web and exported to many formats including PDF, Excel, Word, TIFF, HTML, MHTML, CVS, XML, etc.
• Users can subscribe to reports by configure the SSRS to deliver them in a scheduled time or in intervals as Emails or as FTP file shares.
• SSRS expose many of its functionalities such as getting report parameters, getting the report output, subscribing to a report, deleting a report, building a report and many more by a Web Service to the outside that can be used to integrate the SSRS features to your custom applications.
• SSRS provides drill down and parameterized reports.
• Business Intelligent Studio provides drag and drop features for easy report generations.
• Anyone with a fair knowledge can develop a custom rendering extension to process the RDL and generate reports.
• Exported report XMLs can be easily transformed in to any desirable format with the use of a simple XSLT file.
• SSRS is continuously supported and enhanced by Microsoft since year 2004. Hence has a fairly good documentation support with MSDN.

Weaknesses of SSRS

• It doesn’t expose all its functions such as drill down facility through its web service. List of all exposes methods can be found at http://msdn.microsoft.com/en-us/library/ms155071.aspx
• If you are generating and showing reports through SSRS other than directly retrieving raw data from the database it may include additional delays.
• From SSRS 2005 to 2008 there is slight difference of XML output, and the subscription xml generated by the reporting server that looses direct backward compatibility. Since they are not huge changes, those can be incorporated while development. This will matter only if you use SSRS as the backend of your custom application.