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 à

For tablix 2 à

The DataSet query

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

Hope it helps


Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

12 thoughts on “Creating a master detail report in SSRS without using Sub report.”

  1. Thanks! This is what i looking for. I have another case like this, there is an amount column in Favorite Actor and Favorite Movie. I need a total of the amount display at the bottom of tablix 1 & tablix 2. I put a field to show the total amount in tablix 1 but the amount is double as the records from the JOIN query return duplicate data from Favorite Actor. Is there any other way to do that?


  2. Great technique. Simple and creative way to achieve a master-detail report without using a subreport. I was looking for exactly this solution! Thanks for the excellent article.


  3. This is a very nice post. I have tried it, however, the detail tablix (es) are showing only one record and the group total is for all the extracted total from the query, where have I gone wrong please.


  4. Gad…so simple, but so easy to miss a step. Need to group on detail, not create a parent group in List or it will throw the no details members error. A million thanks!

    Liked by 1 person

  5. great article I am trying to implement it but my detail table only displays first rows… for both tables though I am using customer, cash and cash applied tables to print customer wise cash transactions report.


Please share your thoughts

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.