SQL Server Reporting Services
SQL Server Reporting Services
(SSRS) is a
server-based report generation software system from Microsoft.
Administered via a web interface,
it can be used to prepare and deliver a variety of interactive and printed
reports.
SSRS competes with Crystal Reports and other business intelligence tools.
Versions
Reporting Services was first
released in 2004 as an add-on to SQL Server 2000.
The second version was
released as a part of SQL Server 2005 in November
2005.
A third version was released
as part of SQL Server 2008 R2 in April
2010.
The latest version was
released as part of SQL Server 2012 in March
2012.
Introduction
Microsoft has come up with
its own reporting service, in conjunction with SQL server database to introduce
the Microsoft SQL Server Reporting services [SSRS]. It provides projects
of type Business Intelligence that aids in better business decisions too.
SSRS provides several
extensions towards the data rendering, delivery and security of reports thereby
allowing it to have a higher programmable ability. This innovative approach
enables reports to be created with lesser development effort [compared to other
reporting services], along with customized security options.
SSRS is a comprehensive
reporting platform whereby reports are stored on a centralized web server (or
set of servers). Because reports are centralized, users run reports from one
place. Having centralized reports also means that report deployment is quite
simplified.
Architecture
The full Reporting
Services architecture includes development tools, administration tools, and
report viewers. It can be summarized as 3 Tier Architecture
Tier 1 -
Presentation Tier
Client
applications and Built
in/Custom tools constitutes the Presentation tier. The Presentation
tier works on the data provided by middle tier and handles report
generation and report visualization. It includes various components such
as Report builder, Report designer, Model designer, Reporting services
configuration and other Third party tools. Report generated can be visualized
and administered by a Web browser.
Tier 2 -
Middle Tier
This acts as a brain for
Reporting services and known as Report server. Report server consists of
various windows service components and web service components which
interacts with Data tier components (Report server database and data sources).
This constitutes the Middle tier.
1. Windows
service components:-
Programmatic
Interfaces is responsible to handle on-demand reports, interactive report
processing.
2. Web service
components:-
Scheduling and Delivery
processor helps to deliver reports to the target destinations which are
being triggered from a schedule.
Delivery Extension is
used to deliver reports to the specified locations either by an e-mail delivery
extension or by a file share delivery extension. E-mail delivery extension uses
SMTP (Simple Mail Transfer Protocol) to send an e-mail message to the users
whereas file share delivery extension saves the reports to a shared location on
the network. Delivery extension works in conjunction with Report Subscriptions.
3. Components
common to Window and Web service:-
Extension Name
|
Task
Performed
|
Security
Extension
|
Authenticate
and Authorize Users and Groups to a Report server.
By
default it is Windows authentication.
Can be
customized as per requirement but only one security extension can be used.
|
Report
Processing Extension
|
Provides
custom processing to the special feature added to the report.
By
default, Report server can process tables, charts, matrices, lists, text
boxes, images, line, rectangle, sub report.
|
Rendering
Extension
|
Reports
containing data and layout information can be rendered from Report
processor to various device specific formats like HTML/Excel/CSV/Image/PDF/XML/Microsoft word. |
Data
Processing Extension
|
Interacts
with data sources to get flattened row set by performing query on a data
source.
Included
extensions are there for SQL Server, Analysis server, Oracle, SAP Net Weaver
BI, Teradata, Hyperion Essbase, OLE DB, ODBC, ADO.NET.
|
Tier 3 –
Data Tier
1. Report Server Database.
2. Data Sources like XML,
Oracle, etc.
NOTE: - A Report
server requires at least one Security extension, Data processing
extension, and Rendering extension. Delivery and custom Report processing
extensions are optional, but necessary if you want to support report
distribution or custom controls.
1. Report Server is the core engine and interacts with Report server
Database. It provides services for implementation and delivery of reports.
2. Report Manager is administered via Web browser and
manages the Report Server. It provides front end access to the report server
web service components
3. Report Designer is a developer tool for creating reports.
4. Report Builder is a simplified tool for business users
to build reports on fly. It acts as a base for report creation with Report
model.
5. Report Server database stores report definitions.
6. RDL stands for Report Definition Language. It is an XML file and
stores Query information, Data source information, etc.
