Sunday, May 31, 2015

SSRS - My First Parameterized Report

We have learnt to create our first SSRS Report.

If you are new on the blog than you can view the topic "SSRS- My First Report" by using below link-

My First SSRS Parameterized Report

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 “Department Wise Employee Details” and report should be like as –

Department wise Employees Details
Emp Id
Amit Gupta
Anil Sharma
Bill Smith
Chris Gray
Sales & Marketing
David King
Eliza Roy
Flash God

For the report, we need to add a report filter to display the data only for the selected employee department.
We need to follow the following steps to create our first SSRS Parameterized Report:

1)    On the start window, All Programs list chose SQL Server Data tools from Microsoft SQL Server 2012.
2) SQL Server Data Tools 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, set Filter condition in the Department = ‘IT’. Execute the query 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
WHERE  (Department =’IT’)

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)  After doing the needful formatting to the report as shown below:

Parameter Settings into the SSRS Report
30) Now, we need to add parameter into the report. In the Report Data, Right click on the Parameters and click on the Add Parameter as shown below:

31) On the Report Parameter Properties window, We need to do the following settings –
  • Set the parameter name as inpDept.
  • Set the prompt name as Department
  • Set Data Type as Text (no need to check any checkbox for now)
  • Set parameter visibility as Visible
After this, click OK button as shown below:

32)  Now, we have added a parameter into the report as shown below-

33) Click Preview on the report as shown below-

Now, we need to add this parameter to our report to filter the data on the report dataset. For this we need to do the following things to set the parameter with the report Dataset.


34) In the Report Data tool, click on the Datasets which will show the DataSet1 as shown below:

35) Right click on the DataSet1 and click on the Dataset Properties as shown below-

36) In the Dataset Properties,  we can see that Query property has Name as DataSet1, Data Source, Query type and Query also as shown below:

37) Now, click on the Query Designer and set the @inpDept in the filter condition as shown below –

38) Run the script and set the Query Parameters values as shown below –

39) After click OK button, It will be shown as below –

40) After clicking OK button, we can go back the previous window as shown below –

41) Choose parameters in Dataset Properties window and set the parameter value as shown below –

42) Now click OK button. Click preview of the report and  fill Department as “IT” as shown below –

43)  Change the value of the Department parameter as Admin and click on the View Report button as shown below –

This completes the tutorial section of the parameterized report.
In the next post, We will learn How to add Dynamic Parameter List into the SSRS Report.
Please put your comments if you have any issues-

1 comment: