SQL Server Reporting Services: An Introduction

SQL Server 2005, 2008 and 2008 R2 all come packaged with SQL Server Reporting Services (SSRS) – an enterprise-level reporting solution. With SSRS, you can create, schedule, publish, and manage a multitude of reports using a variety of data sources.

SSRS components include:

• Report Server – the report processing engine and databases
• Report Designer – the main report design environment, which runs in SQL Server’s Visual Studio shell Business Intelligence Development Studio (BIDS)
• Report Manager – a web-based interface used to manage reports, security, subscriptions, data sources, and more
• Other components for administration, and other functionality

The following sections demonstrate how to create a new SSRS project, a shared data source, and a report based on the AdventureWorks database.

Create a SSRS Project in BIDS

A project is the container for all objects – for example, reports or SSIS packages – in BIDS. Each project is of a certain type; you can create a SSIS or other project, but today we want a SSRS project.

1. From the Start menu, select All Programs > Microsoft SQL Server 2008 (or 2005, as appropriate) > SQL Server Business Intelligence Development Studio.
2. Select File > New > Project.
3. Select “Report Server Project” from the list of Business Intelligence Projects, enter a project name, and click OK.

You now have an empty SSRS project open in BIDS.  If the Solution Explorer is not visible, click on the Solution Explorer tab on the right, or select View > Solution Explorer. This window displays the project name, and all the objects associated with the current project.

Create a Shared Data Source

SSRS data sources can be either embedded in reports, or shared among reports within a project. A shared data source is a separate object from your reports. If or when your data source changes – for example, if the source database moves to a new server – you must only change one shared data source, instead of dozens (or hundreds) of individual embedded report data sources.

To create a shared data source:

1. In the Solution Explorer, right-click the Shared Data Sources folder and select “Add New Data Source“.
2. Give the data source a meaningful name (e.g., ServerName_Database). Note that the data source name cannot contain spaces.
3. Select the data source type from the drop-down list. For this example, choose SQL Server.
4. Click Edit to enter the connection details.
5. In the Connection Properties window, enter the server name, logon credentials, and database. Click Test Connection to check the connection, and click OK on all windows.

Create a Report

You can create your report in SSRS either with the report wizard, or from scratch. To create a report with the wizard:

1. In the Solution Explorer, right-click the Reports folder and select “Add New Report“.
2. On the Select the Data Source screen, select the shared data source you created.
3. The Design the Query screen allows you to type or paste your query; or you can click “Query Builder” to build your query in the graphical design.  Paste the following query in the Design the Query window:

SELECT P.Name ,
ProductNumber ,
Color ,
ListPrice ,
SC.Name [Category]
FROM Production.Product P
LEFT OUTER JOIN Production.ProductSubCategory SC
ON P.ProductSubCategoryID = SC.ProductSubCategoryID
WHERE P.ProductSubCategoryID IS NOT NULL
AND P.Color IS NOT NULL
ORDER BY Category, ListPrice ASC

4. On the Select the Report Type screen, select Tabular. Note that the Tabular format is like a simple spreadsheet (or database table), with columns across the top, and the number of rows depending on the rows returned by the dataset. (The Matrix format is like a pivot table.) Click Next.
5. On the Design the Table screen, add Color and Category to the Group section; add the remaining fields to the Details section. Click Next.
6. Keep the “Stepped” selection on the Choose the Table Layout screen, and click Next.
7. Select a style for your table, and click Next.
8. Give your report a meaningful name (e.g., Products By Category) and click Finish.

Your report will appear in the design tab. To view the finished report with data, click the Preview tab.

In the Design tab, you can edit your report in a number of ways. For example:

• Change the size of the report and report elements with drag and drop.
• Add report elements – such as images – from the Toolbox.
• Format character and number displays (right click and select Text Box Properties).
• Add, move, change, or delete data elements.

This brief introduction only skims the surface of SSRS functionality.  Read more about SSRS in upcoming installments of this SQL Server Reporting Services series, and in the references listed below.

References:

• SQL Server Books Online: Reporting Services Components and Tools
• SQL Server Books Online Tutorials: Designing and Implementing Reports Using Report Designer (Reporting Services)
• Microsoft Press: SQL Server 2008 Reporting Services Step By Step, by Stacia Misner
• CodePlex: Download the AdventureWorks database