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-
http://microsoftdatatools.blogspot.in/2015/05/ssrs-my-first-report.html

 
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
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

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-

Thursday, May 28, 2015

SSRS- Cascading Parameter Report



Cascading parameters provide a way of managing large amounts of report data. We can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter.
  1. In this case, the first parameter is independent and might present a list of Department.
  2. Second parameter is dependent on the value of the first parameter such as Sub-Department.
When the user views the report, the values for both the Department and Sub-Department parameters are used to filter the report data.

For cascading parameterized report, we are assuming that we have the following data tables in our database called DemoDatabase –

Reservation Master
Project Code
Project Name
Project Manager
Project Budget
PC001
Reservation
Mike Towery
125,000
PC002
Attendance
Chris Gray
155,000
PC003
Human Resources
Kimmy Wang
225,000
Primary Key= Project Code
Department Master
Dept Id
Department Name
D001
Database
D002
Testing
D003
IT
Primary Key = Dept Id

Employee Master
Emp Id
Employee Name
Dept Id
EmployeeRatePerDay
E0001
Ryan Arjun
D001
240
E0002
Tony Towery
D002
230
E0003
Lucy Gray
D003
250
E0004
Will Smith
D001
245
E0005
Chao Milk
D002
225
E0006
Chris Gyal
D003
210
E0007
Bill Gray
D001
190
E0008
Red Bill
D002
210
E0009
Tom Ramsay
D003
200
Primary Key = Emp Id

Reservation Staff
Project Code
Emp Id
PC001
E0001
PC002
E0002
PC003
E0003
PC001
E0004
PC002
E0005
PC003
E0006
PC001
E0007
PC002
E0008
PC003
E0009
Composite Key  ( Unique Key) = Project Code + Emp Id
On the basis of the above tables, I created a cascading parameterized video captured report in Microsoft SQL Server Data Tools.

You can view all the steps "How to create a cascading parameterized report in Microsoft SQL Server Data tool" in this video here: