Sql reporting services first report

SQL Reporting services also known as SSRS is a great tool to create report from database. In this article we will learn how to create a basic 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. For advanced report see my other article SSRS subreport with drill down step by step in sql server 2008

Step to create our first report:

• 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

Was not it so easy to create the report by using Sql Reporting Services? In our next article we will create some advance report like sub-report with drill down feature

Other blogs you may like

SSRS subreport with drill down step by step in sql server 2008

sql reporting subreport with toggle To create a toggle sub report in sql reporting service is too easy but if you will not aware about it then it is really difficult for you. So in this article we will see how to create a report with having sub report, and sub report data will load only when we... By Ali Adravi   On 09 Sep 2013  Viewed: 11,781

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 3,420

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 683

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 12,838

Bulk insert into SQL Server from CSV file by skipping first row

Bulk insert is used to Import a data file into a database table or view in a user-specified format. It's not daily using kind of code, so it's very common question on most of the site like. How to import CSV file into a table? How to insert records from a CSV file to a table, delimited by comma?... By Ali Adravi   On 08 Feb 2013  Viewed: 17,394