A drill-through reports is the combination of the two reports. One report is known as the base or main report having link option to call another report and second report is known as the sub report in this scenario which must have the parametrized functionality because drill through reports commonly contain details about an item that is contained in an original summary (main) report.
2) After choosing the SQL Server Data tools, SQL Server Data Tools will open as –
We have already created a parametrized report and you can also create that report by clicking here and use that report as the sub report in drill through SSRS report –
Note: A
sub report can act as the main report as well as a main report can act as the
sub report. These reports act as vice versa with each others.
|
Before creating
a drill through SSRS report, we need to know about the output of the 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
EmpId
|
EmpName
|
EmpSalary
|
EmpAge
|
Department
|
105
|
Eliza Roy
|
$ 15,965.00
|
25
|
Admin
|
112
|
Roy Chauhan
|
$ 15,965.00
|
25
|
Admin
|
108
|
Rahul Kuamr
|
$ 15,965.00
|
22
|
Finance
|
101
|
Anil Sharma
|
$ 15,965.00
|
22
|
Finance
|
102
|
Bill Smith
|
$ 18,540.00
|
25
|
HR
|
109
|
Gary White
|
$ 18,540.00
|
25
|
HR
|
100
|
Amit Gupta
|
$ 15,450.00
|
32
|
IT
|
107
|
Sumit Singh
|
$ 15,000.00
|
32
|
IT
|
113
|
Lucky God
|
$ 15,965.00
|
23
|
Others
|
106
|
Flash God
|
$ 15,965.00
|
23
|
Others
|
104
|
David King
|
$ 18,025.00
|
22
|
Procurement
|
111
|
King Street
|
$ 18,025.00
|
22
|
Procurement
|
103
|
Chris Gray
|
$ 18,025.00
|
28
|
Sales & Marketing
|
110
|
Rosy Wang
|
$ 18,025.00
|
28
|
Sales & Marketing
|
We
need to display this information in our SSRS report and Report header should be
like as “Department Wise Employees Summary”.
In this report, we will show the department name and number of employees in the
each department. Whenever user clicks on the department link button then it will drill
through the report and shows the employees details in that department as shown
below:
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 Drill Through Report:
1) On the start window, from the all Programs list choose SQL Server Data tools from Microsoft SQL Server 2012.
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 as given below:
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, Click OK Button to close the shared data source properties window.
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) Right click on the reports folder, choose Add and click on New Item as shown below:
14) This will open Add New Item – Reports, choose Report and named as Department Wise Employee Summary as shown below:
15) After click on Add button, SSDT will create Department Wise Employee Summary report. We need to add report header to the report as shown below :
16) Here is now; we need to open Report Data. In the Report Data, Right click on the Datasets and click on the Add dataset as shown below:
17) Add Dataset will launch Dataset properties window. We need to set the name as DeptEmployeeSummary for the Dataset and choose a dataset embedded in my report and choose Query Type Text as shown below:
SELECT EmpId,
EmpName,
EmpSalary,
EmpAge,
Department
FROM
dbo.Employees
Order by
Department
|
18) After click on the OK button, a dataset has been added in the report data as shown below:
19) Now right click on the report and add table as shown below:
20) Add dataset to the table as shown below:
21) Now right click on the report details and Add Group to choose Parent Group to the repots as shown below:
22) It will launch the Tablix group, we need to select Department as Group by and checked Add group header as shown below:
23) Now we have the following data into the report as shown below:
24) Click Preview on the report as shown below-
Now, we need to remove details row as shown below:
Now, we need to remove extra columns from the report as shown below:
We need to add total number of the employees in the each department as shown below:
After down the all the formatting to the report, click on the preview and report will look like as shown below:
25) Now we need to add drill-through functionality to the report. Right click on the Department group Text box click on the Text Box Properties as shown below:
26) In Text Box Properties, set the action as Go to the Report. Specify a report name and add the report parameter for the specified report as shown below-
27) Click OK button and do the needful formatting to the link for Department as shown below:
28) Now, we are ready to preview of the report as shown below:
Click on the department link, it will show the following report –
This completes the tutorial section of the simple drill-through report.
Nice Post, It's crystal clear
ReplyDeleteGreat effort
ReplyDeleteThis fixed position allows for more accurate drilling especially in repetitive tasks. go here
ReplyDeleteVery simple and Objective
ReplyDeleteA good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one 托福代考
ReplyDeleteI adore your websites way of raising the awareness on your readers. GRE代考
ReplyDeleteThis is highly informatics, crisp and clear. I think that everything has been described in systematic manner so that reader could get maximum information and learn many things. chiropodists drills
ReplyDeleteThanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info. podiatry vacuum drill
ReplyDeleteThis particular papers fabulous, and My spouse and i enjoy each of the perform that you have placed into this. I’m sure that you will be making a really useful place. I has been additionally pleased. Good perform! cordless impact comparison
ReplyDeleteThanks for a wonderful share. Your article has proved your hard work and experience you have got in this field. Brilliant .i love it reading. podiatry vacuum drills
ReplyDeleteThanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with extra information? It is extremely helpful for me. chiropodists drills
ReplyDelete