Creating a master detail report in SSRS without using Sub report.

Suppose we have simple master (Person) and detail (Favorite
Actor and Favorite
Movie) tables in our database .

To write a report that doesn’t make use of sub reports we need to have a single dataset as shown below

Create a new report project and drag the List control for Master table and two Tablix inside List for the detail records.

Select the list and set Group Properties for the Row Groups as shown below.

Select the row for the first tablix that shows Favorite Actor information, right click it and specify group properties as FAID (i.e. primary key for that table)

Do the same for the other tablix (Movie table).

Run the report, it will show the following output

To hide the tablix if there is no data, set its Hidden Property using the following expression

For tablix 1 à
=IIF(IsNothing(Fields!FAID.Value),True,False)

For tablix 2 à
=IIF(IsNothing(Fields!FMID.Value),True,False)

The DataSet query

select P.Id, P.FirstName, P.LastName,fa.Id as FAID,fa.firstname,fa.lastname, fm.Id as FMID,fm.Movie from Person P
left outer join FavoriteActor as FA on P.Id = FA.PersonId
left outer join FavoriteMovie as FM on P.Id = FM.PersonId

Hope it helps