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


Author: Nishant Rana

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

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

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

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

    Like

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

    Like

  4. 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?

    Like

Share your thoughts

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s