Creating a master detail report in SSRS without using Sub report.Posted: February 26, 2012 in SqlServer Reporting Services, SSRS
Tags: CRM SQL Reporting Services, SSRS
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
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.PersonIdHope it helps