Linked Report (Drill through ) Reports in SSRS

A Linked report or Drill through report is a report which will open the another report to get the detail report. In order to show this I have created two reports.

1) StudentExamStatus
2)StudentExamStatusDetail

The report StudentExamStatus show the report of students whether they have passed or failed. The second report StudentExamStatusDetail shows the particular student information with the score they have achieved in each exam.

First Report Query text

create table #studentmarks (studentid int identity(1,1),studentname varchar(50),subjectname varchar(250),marks smallint,Ispassed Char(1))

insert into #studentmarks values ('A','Social',80,'P'),

                                                       ('A','Science',50,'P'),

                                                       ('A','English',90,'P'),

                                                       ('B','Social',30,'F'),

                                                       ('B','Science',50,'P'),

                                                       ('B','English',90,'P'),

                                                       ('C','Social',80,'P'),

                                                       ('C','Science',70,'P'),

                                                       ('C','English',90,'P')

select studentname,

(select top 1 (case  IsPassed when 'P' Then 'Passed'

                           WHEN 'F' then 'Failed' else null end) from #studentmarks s1 where s1.studentname = s.studentname order by Ispassed asc) AS ExamStatus

from #studentmarks s

group by studentname

drop table #studentmarks

Create a Data Source.

Use the Data Source as Shared Data Source so that all the data sets can use the same connection string.

Create a Data Set for the first report as shown below.

StudentExamStatus Report Design

The SSRS 

The Second Report StudentExamStatusDetails DataSet