How to create basic report in Sql Server 2008

In this article we will learn how to create a basic SSRS Report. We will learn how to create a shared data source so we can use it for all the reports in our project, How to create our data set to bind columns in report.
So why we are waiting, let's start

• Click on Start
• Click All Programs
• Expand Microsoft SQL Server 2008 folder
• Sql Server Business Intelligence Development Studio
• Click File menu
• Click New
• Click Project…
• From Project type select Business Intelligence
• From Templates: select Report Server Report
• Select folder to save and enter project Name

We have created a blank project without any data source and without any default report because we will create our data source and reports step by step. As you can see there two folders created

1. Shared Data Source: where we will create our datasource
2. Reports: where we will create our all the reports

Let’s create our shared data source first which we will use for our all the reports in this project

• Right click on Shared Data Source Folder
• Click Add New Data Source
• It will open Shared Data Source Properties dialog
• Enter Name for data source say MyDataSource
• Select Type: since I am going to use Sql Server Northwind database so select “Microsoft Sql Server”
• Click Edit button to define the connection string
• Enter server name say localhost
• Logon to the server: Select your authentication type
• Select or enter a database name : Northwind
• Click Test Connection
• Click Ok

Here is our all the settings:

We set our shared data source, time to create a procedure or write Sql statement to get data for report, since it is our first report so we will not use complicated procedure with different parameters but very simple Sql statement

 SELECT * FROM [Suppliers]

Now we will create our first report, we will use blank report without using report wizard, so let’s create it.

• Right click on Reports Folder
• Click Add
• Click New Item, it will open Add New Item dialog
• From Templates: select Report
• Give name to report, say, Suppliers.rdl
• Click Add

It will open a blank surface to design our report, here we will add report header, footer and a table to design the report.
To add report header

• Right click on the surface
• Click Insert
• Click Report Header
• Add Textbox to add the report header text

Similar to report header we can add report footer and its contents, like report generation date.
To add report content (supplier detail)

• From Toolbox drag a Table on report surface
• It will open a dialog to select the data source
• Select Use shared data source reference
• Select your shared data source from drop down
• Click Next
• It will open Query Designer
• Select Text from Command type drop down
• Paste our Sql statement and execute it.
• Click Finish

Now we will add column to this table

• Open Report Data window
• If it is not visible then open it from view menu
• Drag different columns from DataSet in table column
• If you need more columns, right click on header to add the more columns

Once you added your all the columns, click preview to see the result, it is working but One thing we are missing, alternate row color for data, so let's add it

• Select entire row
• Open Property
• Select BackgroundColor, clieck expression

Paste following into the expression pan

= IIf(RowNumber(Nothing) Mod 2 = 0, "Transparent", "WhiteSmoke")

Here is our created report