This article demonstrate how to create a DrillThrough report in SSRS.
A DrillThrough report is a report that a user opens by clicking a link within another report. Drillthrough reports contain details about an item that is contained in an original summary report.
Basically, DrillThrough reports allows the user to navigate from one report to another by clicking an item in the original report, commonly called as summary report.
Lets see the step by step implementation of DrillThrough report in SSRS.
First we create a summary report which contains a Total Amount by Products and When user clicks on any of product in report it navigates to user to a detail report which contains a details of that specific product.
Lets take a look at dataset, you can see a product have a different -different category and amount.
Lets create a summary report first, take a new report named RptProductSummary, and for this report we are using an embedded data source and embedded data set.
This report will display Product wise total amount only.
Lets format the report, set font size, make the font bold, text alignment, and change column header’s background color.
Lets see the report preview, and you can see it displays data in tabular format.
Note: You can also fetch the grouped data directly from dataset applying group by on product in dataset query, but here we will be grouping data in SSRS report side as we have very less records in dataset.
Now we will summarize the report data by grouping the report data by product.
Right click on Tablix and then navigate to Add Group and select Parent Group..
[Also Read : Create a Drill Down Group Report in SSRS
Create Multi group Drill Down report in SSRS]
Once you click on Parent group, a Tablix group dialog box opens.
Now in Group by drop down list, select Product and check add group header.
After that click on Ok button.
After that, you will see two product columns in report. First product column groups the data in report, so we can delete the second product column.
Now, we will show the total amount by product, for that Just right click on Amount column and select add a Total from context menu.
After that, you will see a Total Amount is added to the report.
Lets see the preview, and you can see product wise total aggregated amount with detail amount values.
As we do not need of detail amount so can remove data rows, just right click on Amount column and select delete rows from context menu.
In case if you want to delete multiple columns then select multiple columns with Shift key.
Once you click on Delete Rows, a dialog box appears, just click on OK button.
Once you delete rows, you will see a grouped/summarized report is created, which grouped the data by product.
Lets see the preview. Now you can see report data are grouped by Product.
So far, we have been created a summary report named RptProductsummary report as it displays the total amount by product only.
Now we will create one more report that would be a detail report, which will display a detail view of data that is amount by product and category.
So the idea is that, user will click on any product in summary report and will be navigated to detail report which displays details related to selected product.
Here we take a new report named RptProductDetail, it uses the same data set query but added the parameter @product, which takes product name an input and filter the report data based on that value.
You can see the data set query as shown below.
Lets create a detail report, as shown below.
Lets see the report preview, and you can see it displays the detail view of amount by product and category for an input parameter value Furniture.
So far, we have been created a Detail report as well.
Lets create a DrillThrough report, for this we will pass the user’s selected product value from RptProductSummary report as input to the parameter @parameter, which is created in RptProductDetail report.
Lets go to summary report RptProductSummary, then right click on Product column then select Text Box Properties from context menu.
Once you click on Text Box Properties, a Text Box Properties window opens.
Now go to Action tab, select Action as Go to report.
After that specify a report name, as we want to navigate the user to Detail report so select the detail report as RptProductDetail.
Then, click on Add button to specify a parameter and its value to report. Next select the parameter name and value field, which would be Product parameter.
After that click on General tab, and give a tool tip text, this will be displayed to user on hovering on product name in summary report.
Once done with this, just click on OK button.
Now open the Detail report RptProductDetail , then double click on Parameter @product inside Parameters folder.
Once you double click on Parameter, a Report Parameter Properties window opens.
Just check the parameter visibility as Hidden, after that click on OK button.
Now, its time to check the implementation of DrillThrough report.
Go to Preview page of summary report RptProductSummary.
You can see, when you hover on any product it display the text as “Click to view detail”, so that user can click on Product to see the detail associated with that product.
Lets click on Product name Furniture, and you will see it navigates you on Detail report which displays the details specific to Product Furniture only.
Also you will see a Back button is enabled, you can click on that to go back to summary report, or you can say parent report.
Lets click on Back button.
Once you click on back button, you are navigated back to summary report.
Also Read..
Create a Drill Down Group Report in SSRS
Create Multi group Drill Down report in SSRS
Multi – value Parameters in SSRS
