SQL Server Reporting Services(SSRS)
Today, data generation is the simplistic 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 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?
SSRS is the Microsoft’s version to 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 to 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 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 the 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 in loading ETL data.
The SSRS tool provides an interface into Microsoft’s Visual Studio to enable developers and SQL Database administrators to connect to 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 with in this line of technology.
SSRS for End Users:
From the offerings that Microsoft Business Intelligence suite provides, SSRS is definitely a unique offering as it caters to wide variety of users. On a broader sense, Microsoft has classified these diverse set of users into:
- Information Consumers
- Information Explorers and
From this classification, any individual can definitely vouch form the maximum usage being 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 forms 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 to drill 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 requires more on the 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.
To cater to the various needs of the users, SSRS does provide the following tools for specific usages. These tools find its 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 Report Manager is setup 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 in 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 in to action. Though most of the reporting requirements be 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 on 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 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 Features:
There a numerous set of features that are provided by the SSRS offering of the Microsoft’s Business Intelligence (BI) suite. These features address the complex business reporting requirements and needs. Here is the brief overview of the features that are provided by the SSRS offering of the 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 like 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.
Subscribe to our youtube channel to get new updates..!
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, sel 7 – managing with Memory pressure
- Reduces through put in memory pressure situations
b) Reporting Processing uses a file system caches t adapter to memory pressure
- Receives memory events from server
c) Administrator is able to set targets (min, Max)
- Minimum threshold defines the amount of memory the server thinks ‘belongs’ to it
- The memory is only used if a requests need it.
- Maximum threshold defines that not to exceed value.
d) Adapts to other processes consuming memory.
3) RS 2008 REPORT ENDING CHANGES
a) Report processing
- On – demand processing
- Hierarchical cursor – based object model.
- New rendering architecture
- Renderer rewrites
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 ACQUISTION
- 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
a) Tablix provides a combination of the best features of tables and matrix data regions
b) Build versatile reports
c) Allows a flexible layout with multiple Row and column
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
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
It is the heart of reporting services which is implemented as windows server.
It consists of
- Windows service
- Report manager
- Web service
- Back ground 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 the report server Items and their management
c) Web service: It provides access to report server via report builder.
d) Back ground processing: There are many processing have in this background processing
- Reporting Processing
- Data processing
- Model processing
- Data Rendering
- Data authentication extensions
- Data base maintenance
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 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 data bases for internal storage
Report server temp db à It stores temporary data session information and caching information
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
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 pay slip, salary slip, Relieving letter, Internal Audition etc.
External Reports: This reports generally submitted to 3rd party authorities such as 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 database and they generate the own reports
- Adhoc Reporting: This report also can be called as 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.
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 on what features comes 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 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 nice into the areas where analysts would be interested in.
- 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 scrutinize 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, reliability maintenance.
- 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), printer or on file system.
- Reports are structured as specific items under a folder which further enables easier browsing and for quicker execution.
Editions of Reporting Services:
SSRS (SQL Server Reporting Services) comes in 4 different editions which 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 light weight of SSRS for the developers to use it on need basis. There will be limited features to what are 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 organization 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 were 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.
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 few more specifics of it as like the editions and the licensing details in 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:
|SCCM||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|