Step By Step SSRS in 11 Hours: Hour 6

Step By Step SSRS in 11 Hours: Hour 6

Introduction

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

Contents

Drill-Down SSRS Report

Step 1

To add the dataset, right-click on "Shared Datasets" and click on "Add New Dataset".

image.png

Step 2

  • Provide an appropriate dataset name.
  • Select the data source.
  • Select the "Stored Procedure" radio button and provide a Stored Procedure name.

image.png

Step 3

To add the report, right-click on "Reports", select "Add" and click on "New Item".

image.png

Step 4

Select "Report" and provide the report a name.

image.png

Step 5

To add the data source in report data, right-click on "Data Sources" and click on "Add Data Source".

image.png

Step 6

Provide the data source a name and select shared data source reference.

image.png

Step 7

To add a dataset to the report data, right-click on "Datasets" and click on "Add Dataset".

image.png

Step 8

Provide the dataset a name and select shared dataset.

image.png

Step 9

Right-click on the design surface then select "Insert" and click on "Table".

image.png

Step 10

Set "Country Name" as the column header and map the "CountryName" field for that column.

image.png

Step 11

In the same way, set "State Row ID" as the column header and map the "StateRowID" field for that column.

image.png

Step 12

Set "State Name" as the column header and map the "StateName" field for that column.

image.png

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

image.png

Step 14

Select the field that we want to make the parent, CountryName.

image.png

Step 15

Now our report design is ready, the first column looks as in a parent and the rest their children.

image.png

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.

image.png

Step 17

In the "Row Groups" window, right-click on "(Details)" under "CountryName" and click on "Group Properties".

image.png

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.

image.png

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.

image.png

Step 20

Our design is ready now.

image.png

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.

image.png

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.

image.png

Next >> Step By Step SSRS in 11 Hours: Hour 7