SQL Server Reporting Services

SQL Server Reporting Services
SQL Server provides a fabulous feature in form of Reporting Services to represent the business data into various visualization data format such as tabular data reports, matrix reports, chart reports, graph reports and KPI reports also. It provides a way to interact the report server with your database and business users are capable to view the reports on their handset devices also. It comes with a lot of great features to increase the growth chances of your business. 

SSRS Installation

SQL Server Reporting Services is an optional package which you can select to install while installing SQL Server.
So before going to describe the functionality, it's very important to understand the core of the SSRS means standalone Architectural behavior.  

SQL Server Reporting Services (SSRS) Architecture

SQL Server Reporting Services is a comprehensive and extensible reporting platform which includes an integrated set of processing components, programmatic interfaces, and tools (development tools, administration tools, and report viewers).

Processing components are the basis for the multi-layered architecture of SSRS and interact with each other to retrieve data, process layout, render, and deliver a report to a target destination.

Report Processor

The Report Processor ties components of a report server together and takes care of caching within SSRS. Caching refers to an ability of the SSRS to keep a copy of a processed report and return that copy when a user opens the report. Caching can shorten the time required to retrieve a report, especially if the report is large or is accessed frequently. All report caches are stored in the SSRS catalog (specifically ReportServerTempDB database) and survive both SQL Server and Report Server restarts.

Report Processor performs the following operations:

• Execution - Retrieves a report definition and combines it with data retrieved by the data-processing extension. This operation generates an intermediate format.
• Rendering - Renders the intermediate format to a requested output format using rendering extensions.
• Processing of models - This is similar to Execution operation for reports that are generated by Report Builder and contain a semantic model (or simply a model, which serves as a data source for a report) and a semantic query. Semantic query refers to a query performed against a model that, in turn, just like a SQL query, generates a report’s data set.

Schedule and Delivery Process

The Scheduling process is responsible to force the report trigger from a schedule where as Delivery process is responsible to hit the report delivery trigger to target destinations. 

SQL Server Reporting Services supports both on-demand (pull) and event-based (push) delivery of reports. Users can view reports in a Web-based format or in e-mail.

SSRS - Column Based Drill Down Report
A Column Drill Down report is similar to row drill report but main difference is that it’s based on the columns and will provide the additional details of the column summary. In the section, we are showing that How can we create our column based Drill Down report within few simple steps.

SSRS - Drill Down Report
Drill-down describes an increasing granularity in the management of data with comfortable functions for navigating through the data where user is capable to jump to the next level of detail or the next report object on the same level, hide individual levels and switch between the detail and drilldown data information. 

SSRS – LookUp, MultiLookUp and LookupSet Functions 
These beautiful features bring a lot of satisfaction to report writer and developers to marge values from two datasets. These functions are is playing a very handy role to combine data from two datasets in a single data region which was not possible in earlier versions of Reporting Services.

SSRS – Implement Interactive Sorting
A sort expression controls the order in which data appears in a data region. You can add interactive sort buttons to enable a user to toggle between ascending and descending order for rows in a table or for rows and columns in a matrix. The most common use of interactive sort is to add a sort button to every column header. The user can then choose which column to sort by. 

SSRS - Charts based drill-down Report
A picture is worth a thousand words – especially when business is trying to find relationships and understand in their data, which could include thousands or even millions of variables and representation of data visualization through the chart reports become very urgent to take the right action for the business. A chart based drill down report is similar to row drill down report but main difference is that it’s based on the category groups and will provide the additional details of that particular category. 


SSRS - Main Report with Sub-Reports
I would like to introduce a Sub-report as a simple report item which will display in inside the body of a main report.  It will use as embedded Item within a report and any report can be used as a Sub-report. A sub report mostly takes the parameter from main report. We can place a Sub-report in a data region, the Sub-report will repeat with each instance of the group or row in the data region. We can add more than one sub report into the main report.

SSRS - Deploying Reports on the Report Server
Report deployment comes after developing the report. To visualize the report to the end users/business users, we need to deploy the report on the report server. So, there is lots of techniques to deploy the report on the report server. In the section, we will go through them step by steps.