SQL Server Reporting Services Tutorial: Designing a Basic Report using SSRS 2008 and VS 2008 BI Studio

In this article, we will see how to create a basic SSRS developer report using Visual Studio 2008 BI Studio. We will create a simple drill down report which will contain a total and subtotal. We will also analyze the options for reports in Visual Studio 2008.

First of all make sure that your ‘Report Server’ is running. To check this, go to Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager as shown below –

Once you open the ‘Configuration Manager’ from SQL Server Services, check if the ‘SQL Server Reporting Services’ Service is running. If not, right click on the service and click on the start menu as shown below –

Now for this demonstration we are going to use ‘AdventureWorks’ database which we will use to fetch the data for designing our reports.

So let’s first write a query and test it using ‘SQL Server Management Studio’ by connecting to our database engine. Start ‘SSMS’ as shown below –

Now connect to the server as shown below –

Your server name may be different than mine. So please change the details and connect to your own server installed on the local machine or on your network.

Now write a query for our report and test the same in our SSMS by connecting to the AdventureWorks database as shown below –

USE AdventureWorks
SELECT
Sales.SalesTerritory.Name AS Region,
Production.ProductCategory.Name AS Category,
Production.ProductSubCategory.Name AS SubCategory,
SUM(Production.Product.ListPrice * Sales.SalesOrderDetail.OrderQty) AS Sales,
SUM(Production.Product.StandardCost * Sales.SalesOrderDetail.OrderQty) AS Cost
FROM Sales.SalesOrderHeader
INNER JOIN Sales.SalesOrderDetail ON
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
INNER JOIN Production.Product ON
Sales.SalesOrderDetail.ProductID=Production.Product.ProductID
INNER JOIN Production.ProductSubCategory ON
Production.Product.ProductSubcategoryID=
Production.ProductSubCategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory ON
Production.ProductSubCategory.ProductCategoryID=
Production.ProductCategory.ProductCategoryID
INNER JOIN Sales.SalesPerson ON
Sales.SalesPerson.SalesPersonID = Sales.SalesOrderHeader.SalesPersonID
INNER JOIN Sales.SalesTerritory ON
Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID
INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
WHERE (Sales.SalesTerritory.[Group]='North America')
AND (Sales.Customer.CustomerType='S')
GROUP BY
Sales.SalesTerritory.Name,
Production.ProductCategory.Name,Production.ProductSubCategory.Name

Now paste the above query and hit ‘F5’. You will see the result similar to the following –

This shows that our query is working fine. Now let’s create a report using Microsoft Visual Studio 2008 for displaying the result in a much better way to the stakeholders/customers.

Open Visual Studio 2008 and create a new Project as shown below –

Now click the ‘OK’ button to create a report. It will show a Report Wizard with a Welcome page as shown below –

Now click on the ‘Next’ button. The next step in this wizard is creating a connection for the underlying data source to fetch the data. So let’s configure the connection string as shown below –

Click on the ‘Edit’ button and configure the connection to connect to the database ‘AdventureWorks’ as shown below –

Test the connection and click the ‘OK’ button. You can make your data source common to all the reports by checking a check box ‘Make this a Shared Data Source’. Now let’s move to the ‘Next’ step of the wizard.

In this step, we are going to write and test a T-SQL query. But if you remember, we have already written a query and tested it in SSMS. So let’s copy and paste the above query in our query builder by clicking a ‘Query Builder’ button as shown below –

Click on the ‘OK’ button. Now let’s go to the next step by clicking the ‘Next’ button on the Wizard. This step will ask you to select the ‘Report Type’. It will be either a ‘Tabular’ report or ‘Matrix’ report. For our demonstration, we will choose a ‘Tabular’ report as shown below –

Now click the ‘Next’ button to decide how to display the data on the report and how to group the data? Here’s the screenshot for the same –

If you see the above figure, we are paging the data ‘Region’ wise, Grouping the data ‘Category’ and ‘SubCategory’ wise and showing the ‘Details’ of the sales.

Now click the ‘Next’ button to go to next step. In this step, we will decide the ‘Layout’ of the table which will get displayed in our report as shown below –

Click ‘Next’ button to go to next step. In this step, we will select a style of a table which will be displayed in our report. Make a choice of a style as per your convenience and click on the next button. (I have made a choice of ‘Ocean’ style for my report).

In this step, we will decide the deployment location for our report(s) - on a centralized location as shown below –

Now click on Next button and give a title to our report. I have given a title as ‘Adventure Works Sales Report’ and click on finish button to finish the wizard.

Once your report is displayed, let’s increate the height and width of the columns of our report as shown below –

Now click on the ‘Preview’ report tab, and check how your report gets displayed in the report viewer -

That’s all. Your basic report is ready. Now let’s see the different options available in Visual studio 2008 for report designing.

The most important part of any report is the source of the data. Adding new data fields, calculated fields, modifying existing data structure, create new data source, create new data sets, create parameters and some other operations can be performed by using a ‘Report Data Window’.

To view this window, go to ‘View’ menu and click on ‘Report Data’ –

Now let’s have a closer look at our Solution Explorer –

Using the Solution explorer, we can add data sources as well as multiple reports.

Now let’s have a closer look ‘Toolbox’ for designing a report –

Using these tools we can design the reports or modify the existing reports using report designer.

In this article we have seen how to use Microsoft Visual Studio 2008 Business Intelligence Studio for designing a basic SSRS report. This report is a drill down report which is fetching the data from the SQL Server 2008 database ‘AdventureWorks’.