What is SQL Server Reporting Services?

Your manager steps off the elevator and heads straight to your desk.  He never enters the DBA cubicles, so you glance suspiciously at him.  You duck your head and type furiously, hoping to blend in with the computer.

"Soo..." he drawls casually once he reaches your desk.  "Big Boss wants me to write a report for the CEO on how much it rains on the plains in Spain."

Too late; you’re trapped.  The last time your manager received an assignment from the Big Boss, you ended up working overtime for a month.

As he plops down a thick stack of printed papers on your desk, your fears come true.  "Here is that Books Online website for SQL Server Reporting Services.  I'd like that report by Friday."  Your eyes shoot imaginary death rays at his quickly retreating back.  What are you to do now?

Well, dear DBA, you should turn to SQLServerCentral’s SSRS in a Flash series, of course!  The upcoming SSRS series will discuss all aspects of SQL Server Reporting Services for someone learning to build and distribute reports.  Let’s start by discussing what Reporting Services is and how we use it.

What is SQL Server Reporting Services?

SQL Server Reporting Services (SSRS), Microsoft’s enterprise reporting platform, is included in the SQL Server business intelligence suite, along with SQL Server Integration Services and SQL Server Analysis Services.  Although SSRS fits well with the other Microsoft business intelligence products, it is also flexible enough to show data from other platforms and be integrated into other products.  In either situation, the reporting side of SQL Server provides the capability to create, distribute, and manage reports to your company.  An example of an SSRS report can be seen in Figure 1.

Figure 1. Sample SSRS report

All levels of an organization can utilize reporting through SSRS.  Analysts, managers, and CEOs use reports to see company information and different analyses, which help them perform their jobs more quickly and accurately.  Different parties can share the analysis of one person, rather than data remaining in one silo department.  SSRS reports provide a variety of options to enhance the report consumers’ experience.

What can you do with SSRS reports?

SSRS reports allow a user to pull, manipulate, and view their data in different ways.  One way that a user can see data is by querying data from multiple sources, including SQL Server and SQL Server Analysis Services.  Also, a developer can create a custom data source or use an OLE DB or ODBC provider to retrieve data from other systems.  The querying language depends on the particular provider, but includes variations of SQL and MDX.

Once the data is available to the report, a developer can use report items, such as tables and textboxes to display the data in different forms.  The data could be sorted, grouped, or manipulated based on expressions and properties in the report item.  Developers can also show the data in visual forms, including charts and gauges.  To create dynamic reports for user manipulation, developers can add parameters.

To create reports, SSRS provides two report designers: Business Intelligence Development Studio (BIDS) and Report Builder.  Developers typically use BIDS, while business analysts typically use Report Builder.  You can see the BIDS report designer environment in Figure 2.

Figure 2. Business Intelligence Development Studio development environment

After report design and publication, users will want to use the report that the developer created.  To satisfy this need, SSRS reports can be rendered in HTML, Excel, PDF, or other formats.  Once the report is rendered, users can flip through pages, export the report to a different format, or change parameter values to re-render the report.

To permit the developer and user to work with SSRS, the full platform is composed of multiple components to help deliver, manage, and administrate the reports.

What components does SSRS use?

The components that SSRS uses depend on which integration mode is configured.  SSRS uses one of two different modes: SharePoint integrated mode or Native mode.  SharePoint integrated mode allows SSRS to store its reports, report items, and metadata in SharePoint.  Along with SSRS and SharePoint, a separate add-in will need to be installed to associate the two products.  Native mode only requires a SQL Server instance to store its needed information.

Let’s discuss each of the three components needed for SSRS configured to run in Native mode:

• Report Database
• Report Server
• Report Manager

The Report Database stores the metadata of the reports, report items, and security information for SSRS.  The database does need to be a SQL Server database, and it will have a different format depending on if your report server is set up to integrate with SharePoint or not.  The database can be stored on the same server as the SSRS instance or on another server with other SQL Server databases.

The Report Server hosts SSRS reports.  A windows service and a web service handle report functionality, including rendering reports, listing report items, scheduling reports, and more.

The Report Manager is a web application interface to the report server.  Administrators can use this tool to manage access and the location of reports.  Developers can deploy reports and set up subscriptions to deliver reports to users.

Now that we’ve seen the SSRS components, let’s look at how different individuals use SSRS.

Who uses SSRS?

The beauty of SSRS is that all employees of a company can receive value from reporting.  From the database administrator to the CEO to the business analyst, each employee can benefit from an SSRS report.  Let’s take a look at a few scenarios to highlight the advantages of SSRS.

Scenario 1: Dashboards

Dashboards show how a company fares based on the facets that the company deems important.  SSRS dashboard can show both trending data and key performance indicators (KPIs).  A company executive can view a dashboard to pinpoint the exact area of the company that is having trouble.  By clicking on the incriminating KPI, the executive passes the information to a manager.  By using SSRS, the information was readily available to the executive and manager, and the company is now in the position to fix the problem.

Scenario 2: Ad-hoc analysis

Business analysts spend a majority of their time working with data.  They pull data from a variety of places, mash the data together, and run calculations, all with the intent of providing answers to pressing business questions.  These questions could change on an hourly basis, so it is important to be able to answer them as quickly as possible.  In SSRS, an analyst can pull and show the data easily and quickly and also export to another format for distribution.

Scenario 3: Administration

Last, but certainly not least, we can’t leave out the DBAs.  As the administrators of SSRS, DBAs will need to be able to restrict who sees which report, as well as see who is accessing which report.  They can create reports to view server statistics, database health, and execution times.  Although DBAs can use SSRS to run their own reports and see who is running SSRS reports, they also use the tools to administer the SSRS instance through the Report Manager, which can be seen in Figure 3.

Figure 3. Report Manager

How do you write an SSRS report for Big Boss?

So far, we’ve seen what SSRS can provide to a developer, DBA, or an end user.  We know the benefits of SSRS and the different scenarios where SSRS is applicable.  We’ve seen the different components of SSRS and how to manage the report server.  We have a good understanding of the purpose of SSRS.

This is all well and good, but you have a report to write, pronto!  Big Boss won’t wait for his report, and your manager is holding you responsible.  To learn how to write your report, stay tuned for the next episode of SSRS in a Flash, The Basics.