Hour 6: Drill Down Report

Introduction

 
In this article, we will create drill-down reports in SSRS.
 

Drill-Down SSRS Report

 
Step 1
 
To add the dataset, right-click on "Shared Datasets" and click on "Add New Dataset".
 
Shared Datasets 
 
Step 2
  • Provide an appropriate dataset name.
  • Select the data source.
  • Select the "Stored Procedure" radio button and provide a Stored Procedure name.

    query
Step 3
 
To add the report, right-click on "Reports", select "Add" and click on "New Item".
 
New Item 
 
Step 4
 
Select "Report" and provide the report a name.
 
Report 
 
Step 5
 
To add the data source in report data, right-click on "Data Sources" and click on "Add Data Source".
 
Data Sources 
 
Step 6
 
Provide the data source a name and select shared data source reference.
 
reference 
 
Step 7
 
To add a dataset to the report data, right-click on "Datasets" and click on "Add Dataset".
 
Datasets image 
 
Step 8
 
Provide the dataset a name and select shared dataset.
 
Provide dataset 
 
Step 9
 
Right-click on the design surface then select "Insert" and click on "Table".
 
Table 
 
Step 10
 
Set "Country Name" as the column header and map the "CountryName" field for that column.
 
Country Name 
 
Step 11
 
In the same way, set "State Row ID" as the column header and map the "StateRowID" field for that column.
 
State Row ID 
 
Step 12
 
Set "State Name" as the column header and map the "StateName" field for that column.
 
State Name 
 
Step 13
  • Now we need to create a parent, since we want to display state names by country name. Here the country will be the parent.

  • Right-click on "CountryName", select "Add Group" and click on "Parent Group".

    CountryName
Step 14
 
Select the field that we want to make the parent, CountryName.
 
parent  
 
Step 15
 
Now our report design is ready, the first column looks as in a parent and the rest their children.
 
report design 
 
Step 16
  • Click on the "Preview" tab and we can see the country-wise list of states.

  • We need to change two things in the current report.

    • States should not be pre-loaded.

    • Country name should not be repeated with each row.

    Preview
Step 17
 
In the "Row Groups" window, right-click on "(Details)" under "CountryName" and click on "Group Properties".
 
Row Groups 
 
Step 18
  • Select "Visibility" in Group Properties.

  • Select the "Hide" radio button, in other words hide this column when the report is initially run.

  • Check the "Display can be toggled by this report item:" check box and select the field.

    Visibility
Step 19
 
Select the country name column, right-click on it and click on "Delete Columns" so that the report has only one column with Country Name.
 
Delete Columns 
 
Step 20
 
Our design is ready now.
 
Our design 
 
Step 21
 
Click on the "Preview" tab and you have the first look of the report. It shows only the country list with a plus "+" sign.
 
country list 
 
Step 22
 
To see the list of states, click on that plus symbol and we can see the list of states of that specific country.
 
particular country 
 
Author
Akshay Patel
Akshay Patel
58 28.6k 7.7m