What is SSRS?

Today, data generation is a simple task that can be done by organizations but making the most of the data that is getting generated is still elusive. It is quintessential to possess the right information to sustain ultra-competitive business environments. In such a game-changing environment, there is a definite need for technologies, tools that can give us the power to generate that sense. To answer this question comes to a tool from Microsoft along with the SQL Server package which provides features and the like to achieve just that, and the solution is an offering from Microsoft (SSRS).

In this article, we will go through these features and functionalities one by one and to the possible detail.

What is SSRS - Table of Contents

• What is SSRS?

• SSRS for End users

• Overview of SSRS Features

• SSRS Additional Features

• Report Usage Types

• SSRS in the Report Development Life Cycle

• Editions of Reporting Services

• How is SSRS licensed?

What is SSRS?

SSRS is Microsoft’s version of business reporting. SSRS stands for SQL Server Reporting Services and it is a server-based report generation software that has been developed by Microsoft. SSRS provides a unified, server-based, extensible, and scalable platform through which all the business reporting needs can be satisfied. It extends its scope from the present paper-based reporting to interactive and web-oriented reporting content. This reporting content can further be shared with various users through emails, file shares, etc. for maximum reach.

SSRS has the capability to generate reports of interest in various file formats such as HTML (Hypertext Markup Language), Microsoft Excel, or CSV (Comma Separated Values) format, etc. In addition to all these mediums, SharePoint can be used as a front-end to such reports and then be pushed to corporate portals for regular access.

SSRS is just another tool that comes along with the Microsoft Business Intelligence (BI) services of the platform. SSRS along with other components of the Business Intelligence platform provides the most sophisticated enterprise data analysis. The Microsoft Business Intelligence suite consists of the following:

• Microsoft SQL Server: This is a traditional database engine that stores the SSRS catalog data along with the business data.
• SSAS (SQL Server Analysis Services): This can be considered a very powerful tool that finds its usage in online analytical processing (OLAP) and data mining. OLAP helps perform data aggregation in order to look through the dimensions of data whereas Data mining helps in discovering patterns available in the data.
• SSIS (SQL Server Integration Services): This is a component that helps in extracting data, transforming it as per need, and also loading ETL data.

The SSRS tool provides an interface into Microsoft’s Visual Studio to enable developers and SQL Database administrators to connect to the SQL database and prepare SQL reports in different ways. There is also a Report Builder tool that is available with the package that comes in handy for less technical users to format SQL reports in standard formats. There are tools and other business intelligence tools like Crystal Reports that SSRS competes within this line of technology.

If you want to build your career with a SSRS (SQL Server Reporting Services) certified professional, then visit Mindmajix - A Global online training platform: “SSRS Online Certification Training” Course. This course will help you to achieve excellence in this domain.

SSRS for End Users

From the offerings that the Microsoft Business Intelligence suite provides, SSRS is definitely a unique offering as it caters to a wide variety of users. In a broader sense, Microsoft has classified this diverse set of users into: 

• Information Consumers
• Information Explorers and
• Analysts.

From this classification, any individual can definitely vouch for the maximum usage is in the first category of users – the Information Consumers. Data that is generated or already available will always be consumed, hence the maximum user base will be with this user group. Information Consumers use the static, predefined, and formatted data that is available. Information explorers form the next bigger user group of users who would be interested to interact with the reports to some degree as in applying some custom filters or drilling down the available data to certain levels. This would definitely require some technical expertise but doesn’t restrict to only technical skills.

Finally comes the Analysts, the smallest user group of users who can develop reports and also perform some sophisticated calculations such as linear regressions or trend analysis, etc. Analysts require more technical expertise to cater to all of these reporting needs and also to satisfy the most critical and complex reporting requirements. It can be thus said that the reports that are generated by the Analysts will become the input to both Information Explorers and Information Consumers.

[ Learn Complete SSRS Tutorial ]

To cater to the various needs of the users, SSRS does provide the following tools for specific usages. These tools find their usages based on the user’s perspective of the reports:

• Report Viewer: As the name suggests, this is the module that you would be interested in for viewing your reports over the Web. Information Consumers should be very keen on using this as the Report Manager is set up by SSRS for this very need. There is a provision for the developers to embed a Report Viewer control in ASP.NET and Windows Forms applications too. The latter method provides a hook to embed these into the web pages or .NET applications.
• Report Builder: This is a tool that is provided with a user-friendly UI to cater to all the ad hoc reporting needs. This is set up against a SQL Server or an Analysis service database for the reporting requirements. As the name suggests, this would be the tool that the Information Explorers be keen on working with. Unlike most of the other ad hoc reporting tools, here there is no expectation of SQL knowledge. The reports can be generated by users without the Structured Query Language (SQL) knowledge or without any understanding of complex joins etc..
• Report Designer: This tool provides all the required hooks to generate complex reports. This is the forte of Analysts and this is where they kick into action. Though most of the reporting requirements are handled by Report Builder itself, this is created to take on really complex reports.

When Might Reports be Useful?

Consider a medical research facility where patients are recruited to various clinical trials. The staff in the clinic create a database record for each patient when he or she agrees to be part of the trial, and then the clinic receives a payment from the drug company based on the rate at which it finds willing participants.

A typical scenario in the past might have involved the clinic emailing the drug company with the total number of participants in the trial on a weekly basis, maybe with details of any patient who dropped out of the trial for medical or personal reasons, the number of drugs used, and any adverse events encountered. The time taken to collate and send this data in the correct format would take up valuable time in the clinic.

If the clinics were recording data in a database, SSRS could be used to produce on-demand reports in a pre-defined format that could then be either exported as a PDF, Excel, etc and emailed to the drug company, or the drug company could be given access to a reporting server so that they could run the report at any time, and get up-to-date data without anyone from the clinic lifting a finger.

Overview of SSRS Features

There a numerous set of features that are provided by the SSRS offering of Microsoft’s Business Intelligence (BI) suite. These features address complex business reporting requirements and needs. Here is the brief overview of the features that are provided by the SSRS offering of Microsoft’s Business Intelligence (BI) suite:

SSRS is definitely a fully featured report engine and reports can be created or generated against any possible data source which has a managed code provider such as an OLE DB or an ODBC data source. This translates to saying that the data retrieval part will be able to retrieve data from SQL Server, Oracle, Analysis Services, Access, Essbase, and the like. Data can be presented in multiple ways and hence with every release, Microsoft ensured that the feedback taken is put to greater use. There are these new Chart and Gauge controls and a Tablix control which provides an amalgam of Table plus Matrix controls.

Apart from these, there were new presentation formats as Word and Excel included and also provided direct integration with SharePoint.

Let us now take a look at the features that are provided by SSRS offering:

• Able to retrieve data from managed providers with OLE DB and ODBC connections
• Ability to display data in tabular, free form, and charts
• Ability to export data in many formats as like HTML, PDF, XML, CSV, Word, and Excel
• Ability to aggregate and summarize data
• Ability to add in report navigation
• Ability to create an ad hoc report and save it to a server
• Ability to create custom controls using a report processing extension
• Ability to embed images, graphics, and external content
• Ability to integrate with SharePoint
• Provision of Simple Object Access Protocol (SOAP) Application Programming Interface (API), pluggable architecture
• Provision to subscription-based reports, on-demand reports
• Ability to store and manage reports generated by the Users with SSRS’s report builder tool
• Provision to URL based report access
• Ability to display KPI data using Gauge and Chart controls.

Over and above all of these features, there is this icing on the cake. The extensibility that has been provided by SSRS, to be able to embed reports or generate reports customized based on your needs – it is a wonderful feature that came beyond the developer’s anticipation.

SSRS Additional Features

1) IIS REMOVED IN 2008

As there is a conflict to other applications they removed the dependency of IIS in 2008 by replacing with the components

• SAL OS
• SQL CLR
• SQL NETWORK INTERFACE
• COMMUNICATIONS THROUGH http.SIS protocol.

2) RICH MEMORY MANAGEMENT ADDED

a) Server Infrastructure for process memory monitoring

• Dynamic, self 7 – managing with Memory pressure
• Reduces throughput in memory pressure situations.

b) Reporting Processing uses a file system caches t adapter to memory pressure

• Receives memory events from the server

c) The administrator is able to set targets (Min, Max)

• The minimum threshold defines the amount of memory the server thinks ‘belongs’ to it
• The memory is only used if requests need it.
• The maximum threshold defines that not to exceed the value.

d) Adapts to other processes consuming memory.

3) RS 2008 REPORT PENDING CHANGES

a) Report processing

• On-demand processing
• Hierarchical cursor-based object model.

b) Rendering

• New rendering architecture
• Renderer rewrites.

4) SCALABILITY

a) Reports in SQL: 2005 are memory bound

• Memory usages is proportional to data size.
• Large datasets can cause out-of-memory exceptions
• Memory usage in problem renderers (pdf. Excel, csv).

b) Very large reports can starve (or) fail many smaller reports.

5) DUNDAS ACQUISITION

• The SQL SERVER reporting services team has acquired dunda’s software data visualization products.
• Chart, guage,  map, barcode, and calendar for reporting
• Chart, guage for share point
• Chart program/enterprise, guage, map, OLA P chart and tab controls for visual studio (windows and web).

6) TABLIX – NEW DATA REGION

• Tablix provides a combination of the best features of tables and matrix data regions
• Build versatile reports
• Allows a flexible layout with multiple Row and column.

 

HTTP Listener

It monitors the incoming request directed to HTTP.sis on a specific code on the local computer the host name and port are specified on a URL resection while you configure the server.

When the HTTP listener process the request it forwards the application layer to verify the user ident.

Authentication Layer:

It verifies the user id, password (Or) the identity of user (or) application that makes the request. The following authentication that supported are

• Windows Integrated security
• NTLM Authentication
• Forms Authentication
• Basic Authentication
• Anonymous Access.

Report Server

It is the heart of reporting services which is implemented as a windows server.
It consists of

• Windows service
• Report manager
• Web service
• Background processing.

a) Windows Service: (provides report scheduling & delivery services):-
Both the services are used in designing, saving, executing, managing, and publishing the reports Reporting services hosts the report manager, the reports report server, web service & background features in their own service.

b) Report Manager: It provides client fronted access to the report server Items and their management

c) Web service: It provides access to the report server via report builder.

d) Background processing: There are much processing have in this background processing.

• Reporting Processing
• Data processing
• Model processing
• Data Rendering
• Data authentication extensions
• Scheduling
• Subscription
• Database maintenance.

Report processing:

The report server has 2 cool processors
a) Report processor
b) Scheduling and delivering processor.

Report server back end:

• Report server stores folders and files just like a file system.
• The Report you create exists as a file in the files with extension “.RDC” (Report definition language).
• When the report is published it will be stored in the report server database
• The deployment uses a2SQL server relational databases for internal storage.

[ Related Article: Matrix Reports in SSRS ]

Report server temp DB à It stores temporary data session information and caching information

Data Processing

It is designed to retrieve a specific type of data source and provide extended functionality during report design and processing.

Data Rendering extensions

There are three rendering formats available:

a) Data render:  Data only display

Eg:– CST & XML

b) Soft page break render:- Maintain format & layout.

Eg:– Msword, excel, MHTML, Report viewer controls, etc.

c) Hard page break render:-  It supports gif & pdf formats.

Scheduling Delivery Extensions

• Report server Email
• Report server file share.
• Custom extensions
• Subscriptions.

Simple SSRS Architecture

 

Reports are required in general but situations:

a) For Internal Reports

b) For external Reports.

Internal Reports: Generally, company Internal Operations such as payslip, salary slip, Relieving letter, Internal Audition, etc.

External Reports: These reports are generally submitted to 3rd party authorities such as the IT department, STPI, etc.

Conclusion: To create this type of reports we go for different reporting applications like cog Nos, BO, SSRS, CRYSTAL REPORTS, MICROSTAR tegy etc.

Report Usage Types

• Standard Reporting: Here there will be a centralized database. Multiple users connect to the database and they generate their own reports
• Adhoc Reporting: This report also can be called dynamic reports and the content and layout changed every time.
• Embedded Reporting: Here the reports are embedded with the 3rd party applications like Java, net, etc.

[ Related Article: Types of Reports in SSRS ]

SSRS in the Report Development Life Cycle

To understand the ways how SSRS can be used or deployed, you should have the perfect understanding on its lifecycle works. It also helps if there is a better understanding of what features come to your rescue in what stage of the report development life cycle. To keep it a bit simple, any typical reporting application goes through the following three stages – Authoring, Managing and Delivery. There are tools that help through these stages for any reporting application. With this understanding, let us now take a look into each of these stages to gain the best knowledge on the same.

Authoring:

• The authoring stage denotes the stage within the report development life cycle where the report author defines the report layout and sources of data.
• Reports can be designed using either the Report Designer tool or Report Builder 1.0 depending on the release of SQL Server that you would be using.
• There is also the new version of Report Builder 2.0 that fits very nicely into the areas where analysts would be interested in.

Managing:

• The managing stage denotes the stage within the report development life cycle where the author publishes a specified report to a centralized location where a report administrator scrutinizes for security and delivery.
• Once the report is published, an administrator can use Report Manager or SharePoint or SQL Server Management studio to manage these published reports.
• SSRS passes both the load tests as in scaling users from a single user to around thousands of users and the uptime, and reliability maintenance.

Delivery:

• Delivery is the stage within the report development life cycle where the actual report gets distributed to the intended users and is also available in many different formats ( SSRS retrieval mechanism kicks in to enable users to change the output format of the requested report ).
• SSRS provides a wide range of delivery methods ranging from emails, interactive online (usually on SharePoint or custom applications), printers, or on the file system.
• Reports are structured as specific items under a folder which further enables easier browsing and for quicker execution.

Learn Frequently Asked SSRS Interview Questions & Answers that help you grab high paying jobs

Editions of Reporting Services

SSRS (SQL Server Reporting Services) comes in 4 different editions that mimic the 4 editions of SQL Server or Visual Studio, namely Express Edition, Workgroup Edition, Standard edition, and last but not the least is the Enterprise edition. These editions are as expected range from free editions to fully scalable Enterprise editions. Let us see in some more detail about these editions, shall we?

• Express Edition: Express edition provides a lightweight of SSRS for the developers to use it on a need basis. There will be limited features to what is present on a full version of SSRS along with SQL Server.
• Workgroup Edition: Workgroup edition is ideal for a smaller group of individuals or branch offices where the load is limited and the features used are also limited. Should there be a need to scale up a setup with the Workgroup edition, there is always a scope for that instance to be upgraded from Workgroup edition to either a Standard edition or an Enterprise edition.
• Standard Edition: Standard edition of this tool is well versed or suited for small to medium organizations or a single server environment. The only two features that the Standard edition of SSRS are specialized data-driven subscriptions, infinite drill down using the Report Builder.
• Enterprise Edition: Enterprise edition of this tool is well suited for bigger organizations with more complex databases and also more complex reporting requirements. Enterprise edition covers all the major features of SSRS and also supports scaling across a web farm.

How is SSRS Licensed?

The simplistic answer that one could provide here is that any machine that runs Microsoft SQL Server is licensed not just for the database engine but also gets listed for the entire Microsoft Business Intelligence (BI) platform. This means that it is licensed for Microsoft SQL Server, SSRS, SSAS, SSNS, and also SSIS at once with just one license. This gives one an opportunity to work with SSRS without actually worrying about anything else.

Currently, there are 3 different ways to license an SQL Server installation. To gain more specific details on this can be achieved and procured, it is suggested to contact the Microsoft representatives or resellers.

• Per processor: In the per processor method of licensing, a License is paid for each processor on the machine that runs a SQL Server instance. This method of licensing is an optimal way for web-facing or the business-to-business machines running SQL Server. This will be very much helpful for huge user populations.
• Server license plus device client access licenses (CALs): License cost is paid only for the machine that runs SQL Server and at the same time for each and every other device that connects to this SQL Server instance. An ideal case where this model of licensing can be applied is Kiosks where there are multiple users per device.
• Server license plus user CALs: License cost in this model is paid for the machine that runs SQL Server instance and also on a user basis accessing the machine. This is very useful in the cases of enterprises in which each and every user can access the SQL Server machine for many devices at once.

Conclusion

In this article we have gone through the concepts of SSRS (SQL Server Reporting Service) and also understood the circumstances or scenarios where it finds its usage. We have also understood the features that SSRS provides to its end users. We also have discussed how we can leverage different SSRS features in the report development life cycle. We have gone through a few more specifics of it as like the editions and the licensing details in a more detailed manner. 

With this article, we have tried to provide as many details as possible. We hope that this article should provide all the details that you would require if you are interested in the whole feature of SSRS. Please provide us the feedback on this article in the form of comments or suggestions. Though we have put in lots of effort in providing the most accurate details possible, we would still request you to please contact the Microsoft documentation as well, if you are willing to make any purchase decisions.

List of Related Microsoft Certification Courses:

SSIS Training	Power BI Training
SSAS Training	SQL Server Training
SCCM Training	SQL Server DBA Training
SharePoint Training	BizTalk Server Training
Team Foundation Server Training	BizTalk Server Administrator Training