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
</p> <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> <p>
Hope it helps
Nishant Rana's Weblog 
Thanks a lot for the help.
LikeLike
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?
LikeLike
It very good!
LikeLike
Thanks.. It is what i m looking for..
LikeLike
Thank you for connecting the dots.. query to dataset to report tables. Hard to find.
LikeLike
Very useful technique!
LikeLike
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.
LikeLike
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.
LikeLike
Great article…It helped me a lot
LikeLike
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!
LikeLiked by 1 person
Thanks for your sharing your thoughts
LikeLike
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.
LikeLike