Simple steps for developing SSRS Report

Steps to build Simple SSRS Report:

1) Create New Project with choosing Business Intelligence Projects\ReportServerProject.

2) By opening this Simple Report Project, click on Solution Explorer View. You are able to see following screen.

3) Now Right click on Shared Data Sources folder and choose Add New Data Source tab to add the Data Source for report.Write the appropriate connection string like following:

You can choose the database connection string from Edit Button displayed on above screen. Pressing Edit Button will redirect you on the following screen.

Choose the appropriate data base name and server name and if Test Connection is Succeed
then Press OK button. This will reflect to Shared Data Source connection string. It will be
automatically changed as per you selected in Connection Tab.

5)

Now turns to add report to our Report Server Project. Let’s create a sample report, which can display the Employee Details like First Name, Last Name, Address, City, Region, Country, etc.

For all this we need to add a report first, so for this right click on “Report” Folder in The Simple Report Project and choose “Add” Then “Add New Item” you can see the following screen:

Choose “Report” and give appropriate report name and click on ADD button.

6) After adding a new report into project, you will be able to see tree tabs.

a. Data – You have to provide the dataset for the report into this option. You can specify the SQL script into that.

b. Layout – This tab handle the layout of the report. You can specify the report layout in design mode. This works same as Report designers like Crystal report etc…

c. Preview – You can see the Preview of the report in this tab.

In Data tab User will need to create dataset for the report. The Dataset dropdown will have option by using this User will be able to create the dataset.

For this in DATA Region of the Report, click on Item of the Data Set Drop Down List Box. By clicking this you are redirected in following screen.

Here you have two options:

1. Write SQL Queries directly by selecting Command Type=”TEXT”
2. You can give Stored Procedure Name also by selecting the Command Type=” StoredProcedure”.
   

Press ok after applying proper queries or stored procedure depends as per requirements.

Following diagram shows the Data tab of the Report.

7)

Now turns to design the Actual Report as per our needs.

We want to display the Employee Details in our report. For this we are required to put
Labels, Text Boxes that shows the actual details of the Employees from the Employee table
as we applied into the Data Region.

Following image is showing the Layout view of the report in this we are using Table
object,which have the facilities of Header and Footer of the Rows. So we are displaying the
Column Title in Header and the actual record in Rows section of the Table.

To display actual data in the Table Rows, User can drag and drop those fields with Value into
the text box in report design view from the data set.

Second way to achieve this, by right click on that text box and click on “Expression”. This will
open new window like following: Here we want to display “Employee ID” so from “Fields
(dsEmployees)” we choose EmployeeID. And then apply Trim () to remove extra spaces.

Press OK button your Expression will set in Text Box. When we preview the Report it will display
the Employee ID from the database table.

You can have various options to set the values as per your need. As we need User
Name, First Name, Last Name, Email, so we can choose “Fields”. By double click on “Fields” we
can see the various columns returned by queries. As like the fields are displayed in right side
(EmployeeID, FirstName, LastName, Designation, etc.) Clicking on the particular filed, the
related expression will be reflect the at top side of the window like
“=Fields!EmployeeID.Value” .

You also can merge the values like to Display Full name you can merge TitleOfCourtesy,
FirstName and last name like this:

=Trim(Fields!TitleOfCourtesy.Value & " " & Fields!FirstName.Value & " " &
Fields!LastName.Value)

8) After proper design, user can be able to see preview the report like in following image from
Preview Tab of the Report.

We can use various inbuilt functions to make your design attractive.

For e.g.

1) Display Report Current Date:
=FormatDateTime(DateString(),1)

OutPut : Tuesday, May 13, 2008
(The day of week and date format in MMM-DD-YYYY)

2) Display Page Number out of Total Pages.

= "Page" & " " & Globals!PageNumber & " of " & Globals!TotalPages

OutPut : Page 1 of 2.

These are the simple steps to create a SSRS report but this is just creation of the report we still not integrated it with our application. For integration of the report to our application we need to deploy this report on the Report Server. After this we need to use Report Viewer Object to see the Report and have to bind this report with appropriate Report Server path and Report Path.

The next article will include following important things related while developing SSRS Report .

How t o Create Parameterized SSRS Reports?