Introduction to SQL Server Reporting Services

QuanTriMang.com - The software package SQL Server 2005, 2008 and 2008 R2 is bundled with SQL Server Reporting Services (SSRS) - a solution for creating specialized reports for businesses. With SSRS, you can completely create, publish, and manage a huge number of reports from a variety of data sources.

The main components of SSRS include:

Report Server: reports on the processing of the main databases and components

Report Designer: The main 'environment' for creating reports, running on Visual Studio of SQL Server and Business Intelligence Development Studio (BIDS)

Report Manager: a common web-based tool for managing reports, security functions, data sources, comments, and more.

The main purpose of the following article is how to create a new SSRS project, share original data, and key reports based on the AdventureWorks database.

Create new SSRS project in BIDS:

Project concept - the project includes all objects - objects, components. For example, SSIS reports or packages - inside BIDS. And each project is based on certain categories.
At the Start menu, select Programs> Microsoft SQL Server 2008 (2005 or any version)> SQL Server Business Intelligence Development Studio . Select File> New> Project and Report Server Project from the Business Intelligence Projects list , type a name for the project and click OK:

Introduction to SQL Server Reporting Services Picture 1

This is an 'empty' project, meaning nothing is available. If the Solution Explorer button is not available, select the Solution Explorer tab on the right or View> Solution Explorer, the next window will display the name of the project and all related components and objects.

Create shared original data set:

SSRS data sources can be 'embedded' in reports, or shared among multiple reports of a project. Depending on the type of report we will share this original data in different ways. For example, if the data source changes, or the entire database is moved to another server, the user only needs to change the source of the shared data, including dozens, hundreds of Separate reports are included within them.

Introduction to SQL Server Reporting Services Picture 2

To create a shared data source, within the Solution Explorer window, right-click and select Shared Data Sources> Add New Data Source . Name the database, noting that there should be no space here. Next, select the appropriate database category from the list, for example, here is SQL Server . Click Edit to fill in the details of the connection protocol. In the Connection Properties window, you enter the server name, identity information, and database. Then click Test Connection to check and OK when done.

Steps to create a report:

In the Solution Explorer , right-click Reports> Add New Report . In the Select the Data Source screen , select the database source just created in the previous step. Next, the Design the Query screen displays, allowing users to type query statements, or using the Query Builder function to build with a graphical interface. In this test, you rely on the following query code snippet:

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

On the Select the Report Type screen , select Tabular . Note that the Tabular- style format will look like a regular spreadsheet (or database table), with columns at the top of the page, and the number of lines depending on the amount of data in the dataset. Then click Next .

Next to Design the Table , add Color and Category to the Group section, add the remaining fields to the Details section, click Next .

Keep choosing Stepped at Choose the Table Layout and Next steps. Next, select the type for the report table and continue to Next . Finally, name (eg Products By Category ) and Finish :

Introduction to SQL Server Reporting Services Picture 3

In the Design tab, you can edit the report in several ways. For example:

Change the size of the report and the components with drag and drop.

Add other objects like photos, from the Toolbox toolbar.

Format characters and numbers (right-click and select Text Box Properties).

Add, move, delete, or change data attributes.

The above basic information is just the first step to introduce SSRS functions. For more details on SQL Server Reporting Services, please refer to the following link: SQL Server Books Online, SQL Server Books Online Tutorials, Microsoft Press - Stacia Misner and CodePlex. Good luck!