SQL Server Reporting Services (SSRS)

SQL Server Reporting Services is a power full reporting tools. This article describes some basic information about SSRS. Summary of the articles:

• What is SQL Server Reporting Services (SSRS)?
• History of SSRS
• Types of SSRS
• Advantages of Reporting Services (SSRS)
• Export Options
• How to get the Information of Report Files in Report Server?

What is SQL Server Reporting Services (SSRS)?
SQL Server Reporting Services or SSRS is a server based report generation software system. It is developed by Microsoft Corporation. It is administered via a web interface. It is used to generate and deliver interactive and printed reports. We can use it instead of Crystal Reports and other reporting tools. The entire report and data source definition is stored as a simple XML file. The reporting engine uses this file to render reports.
The reports can be building based on relational or multidimensional data from SQL Server, Analysis Services, Oracle, or any Microsoft .NET data provider such as ODBC or OLE DB. We can create tabular, matrix, and free-form reports or ad hoc reports.

SSRS includes the following core components:

• A complete set of tools that can be used to create, view and manage report
• A Report Server component that hosts and processes reports in a variety of formats like HTML, PDF, TIFF, Excel, CSV, and more
• An API that allows developers to integrate in custom applications or to create custom tools to build or manage reports

History of SSRS
Microsoft released SSRS in 2004 for SQL Server 2000. The second version was released in November 2005 for SQL Server 2005. The latest version was released in April 2010 for SQL Server 2008 R2.

Types of SSRS
The types of SSRS are given below:

• Parameterized reports
• Linked reports
• Snapshot reports
• Cached reports
• Ad hoc reports
• Clickthrough reports
• Drilldown reports
• Drillthrough reports
• Subreports

Advantages of Reporting Services (SSRS)
SSRS provides lot advantages. Some advantages of SSRS are given bellow:

• It is faster and cheaper
• Efficient reporting access to information residing in both Oracle and MS SQL Server databases
• No need for expensive specialist skills
• The default report designer is integrated with Visual Studio .NET so that we can create application and its reports in the same environment
• The security is managed in a role-based manner and can be applied to folders as well as reports
• Once parameters are defined, the UI for these parameters is automatically generated
• Subscription based reports are automatically sent by mail to the users

Export Options
SSRS allow many ways of rendering the reports:

• HTML (MHTML)
• Excel
• Acrobat
• Tiff (image)
• XML
• CSV

If we like, we can write our own rendering mechanism.

How to get the Information of Report Files in Report Server?
The following query returns all the information of report files in report server.

SELECT
    Path,
    Name,
    CASE Type WHEN 2 THEN 'Report'WHEN 6 THEN 'Report Model' END AS Types,
    CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS SourceCode
FROM ReportServer.dbo.Catalog
WHERE (Type IN (2, 6))
ORDER BY Path, Type, Name

That’s all about SSRS.