SSRS- My First Report

My First SSRS Report –
The beginning of anything is always excited because it gives us a chance to prove ourselves in term of new technologies also. I was very excited for my first report because I want to learn all the things about the SSRS Reports. Before create our first SSRS report, we need to know about the output of the first SSRS report.

We are assuming that we have an Employee Master Data table in our database which contains the following information –

1. Emp Id
2. Name
3. Age
4. Salary
5. Department

We need to display this information in our SSRS report and Report header should be like as “Employee Information” and report should be like as –

Employees Information
Emp Id	Name	Salary	Age	Department
100	Amit Gupta	15450	32	IT
101	Anil Sharma	15965	22	Finance
102	Bill Smith	18540	25	HR
103	Chris Gray	18025	28	Sales & Marketing
104	David King	18025	22	Procurement
105	Eliza Roy	15965	25	Admin
106	Flash God	15965	23	Others

BIDS (Business Intelligence Development Studio) is included with SQL Server product. When you are running the SQL Server installer, you need to click the Business Intelligence Development Studio checkbox in order to install BIDS. After successfully completing the installation, you are ready to launch BIDS from the Microsoft SQL Server 2008 R2.

We need to follow the following steps to create SSRS Report:

1)      On the start window, All Programs list chose SQL Server Business Intelligence Development Studio from Microsoft SQL Server 2008 R2.  

2)     SQL Server Business Intelligence Development  Studio will open as -
 

3)     Now, Click File, Add, New Project (ctrl+shift+N) from the top-level menu –

4)     Chose a Report Server project to the solution. Fill in the Add New Project dialog as shown below:  

At this point, we have created a Report Server project called “Reports” as shown above.

5)     Click OK button on the New Project window and will Launch the report wizard-

6)     In the Solution Explorer, Right Click on the Shared Data Sources and click on Add New Data Source.

7)     Then Shared Data Source Properties window will open. Change the name as DataSource and click on the Edit Button.

8)     Click the Edit button to display the Connection Properties dialog; fill in your Server name and select the respective database which contains the data for the report as shown below:

9)     Click on the Test Connect button to verify the connection is working or not for the report as shown below –

10)  After connection verification, click ok Button.

11)   Window will return to Shared Data Source Properties window as shown below-

12)  Click OK button to finish the Shared Data Sources wizard, now we have the DataSource to get the information from the database as shown below:

13)  Now right click on the reports folder and click on Add New Report

14)  This will open Report Wizard as shown below:

15)  Click on the Next> button and select Shared data source as shown below:

16)  Click on the Next> button and click on the Query  Builder….. as shown below:

17)  This will open the Query Designer window as shown below:

18)  Right click on the blank window and click on Add Table from the Query Designer property window as shown below:

19)  Select the Employees from Tables tab in Add Table window. Click Add button as shown below:

20)  After selecting the necessary columns, click on the Run as shown below:

21)  After execution of the query, It will display the following result to run the below query-

SELECT        EmpId, EmpName, EmpSalary, EmpAge, Department

FROM            Employees

22) Now, Click OK button to get back on the Report Wizard as shown below:

23) Click Next button to select the Report Type as shown below:

24)  Choose Tabular option and click the next button, which will display the below window:

25) Select Available fields in the details button as shown below:

26) Click on the Next Button to choose the Table Style as Forest as shown below:

27) Click on the Next> button.

You should provide a descriptive name for your report in the Report Name textbox. You can click the Preview report check box if you would like to see what your report will look like. Click the Finish button to complete wizard. You will now see your report in the Solution Explorer as shown below:

In the above window, you are able to view the Report Summary for the report such as Data Source, Report Type, Layout Type , Style, Details and Query for the report also.

28)  Now, Click on the Finish button to complete the Report Wizard and the report will also be displayed in the Report Designer as shown below:

29)  Click on the Preview tab to render your report. A portion of the report is shown below:

30)  Now, we need to add report header in the report. In the Design mode, select Employees and remove it from the report as shown below:

31)  In Design view, right click on the report and choose report header from the insert list as shown below:

32)  In the Design mode, Header area will be display as shown below:

33)  Add Text Box in the header area as shown below:

34)  Fill the Text Box Value as Employees Information and do the needful formatting to the report as shown below:

35) Click on the Preview tab to display the report:

This completes the tutorial section on the Report Wizard. After the above steps, you are able to create your own simple SSRS Report.