SQL Server & MSBI: Create Report in SSRS 2008

In this article, I will explain how to create a simple report (that could be first report for a newbie) using SSRS 2008. Here I am assuming that you have successfully installed SQL Server 2008 along with SSRS.

There are two ways to create Reports in SSRS. You can develop the report manually, or you can use the Report Wizard to give yourself a head start. For this first report, I am going to take advantage of the wizard.

STEP 1:
To begin, start a new Business Intelligence project in Visual Studio 2008 by clicking on Start --> All Programs --> SQL Server 2008 --> SQL Server Business Intelligence Development Studio. Then, from the menu, select File --> New --> Project to open New Project wizard. Now select Report Server Project from Visual studio installed templates and specify Name, Location and Solution Name. I am specifying these ReportProjectSSRS, D:\Hari, and ReportProjectSSRS respectively as shown below:

STEP 2:

In Solution Explorer, right click on Reports folder and select Add New Report.

This will open Report Wizard. Click Next on the Welcome screen and this will bring you to the Select the Data Source screen.

STEP 3:

Enter the name of Data Source as dsLocal and select type as Microsoft SQL Server. Now click on Edit button to set the connecting string for data source, this will open Connection Properties window. Enter Server name and database name and click on Test Connection buttion to make sure the connection is established. Click OK button twice to close Test Results and Conection Properties windows.

You can check the Make this a shared data source checkbox to make this data source as shared so that it can be used for other reports as well. Now click on Next to proceed.

STEP 4:

This will open Design the Query wizard. Here you can define your Query string. Alternatively you can use Query Builder... to build your query. I will use below query to pull data from Employee table:

SELECT
[Emp_code],[Emp_Name],[Desg],

[Head],[DOB],[Basic],[Dept_Code]
FROM Employee (NOLOCK)

Click Next to proceed. It will display Select the Report Type wizard. Select Tabular option and click Next to proceed.

STEP 5:

Now you can see Design the Table wizard. Select required fields from Available fields and clicked on details button to make these fields available in details part of a report. You can do data grouping as well using Group button. Click Next to proceed.

STEP 6:

Now you can see Choose the Table Style wizard. Select default style Slate and click Next. Finally you can see Completing the Wizard. Enter report name FirstReport and click Finish to complete the wizard.