SSRS Introduction

SSRS or SQL server Reporting Services which is also called Microsoft SQL reporting, is a component of the Microsoft BI stack.

SSRS is a set of ready made tools, that helps you to create, deploy and manage reports.

You can extend reporting functionality using C# and VB as programming languages.

Microsoft SSRS or Business Intelligence SSRS, lets you create very rich reports (Tabular/Graphical/Interactive/free-form) from various datasources with rich data visualization (Charts, Maps, sparklines).

All these reports can be viewed via a web browsers.

SSRS allows are reports to be exported in various formats (Excel, PDF, word etc)

SSRS allows reports to be delivered via emails or dropped to a share location in an automated fashion.

SSRS provides a host of security features, in order to control, who sees which reports etc.

Types of reports in SQL Server Reporting Services:

Parameterized reports

Linked reports

Snapshot reports

Cached reports

Ad hoc reports

Clickthrough reports

Drilldown reports

Drillthrough reports

Subreports

Parameterized Reports:

A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs. Parameterized reports are frequently used for drill-through reports, linked reports, and subreports, connecting and filtering reports with related data.

Linked Reports:

A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file. Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings

Snapshot Reports:

A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server. Report snapshots are not saved in a particular rendering format. Instead, report snapshots are rendered in a final viewing format (such as HTML) only when a user or an application requests it.

Report snapshots serve three purposes:

• Report history. By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.
• Consistency. Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.
• Performance. By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

Clickthrough Reports:

A clickthrough report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. Clickthrough reports are auto generated.

Drilldown Reports:

Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report.For reports with large amounts of data, consider drillthrough reports instead.

Drillthrough reports:

Drillthrough reports are standard reports that are accessed through a hyperlink on a report item in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters.

Subreports:

A subreport is a report that displays another report inside the body of a main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports.

Architecture:

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.

Advantages of SQL Server Reporting Services:

  1.       Faster processes of final reports on both relational and multidimensional data.

  2.       Easily deploying reports using the Microsoft reporting service.

  3.       Faster delivery of information to business organizations.

  4.       Providing better Decision making to business users.

  5.       Direct and efficient way of reporting access to information residing in both Oracle  and     

            Microsoft SQL Server databases.

  6.       Provides Ability to business users to interact with information without including IT 

            professionals.

  7.       There is No need for any special skills.

  8.       It provides a World Wide Web (WWW) based connection for deploying reports.

Icon Descriptions