Tag: CRM SQL Reporting Services

 

Suppose these are our two tables, Person and Person Details, having one to many relationships to each other.

Person:-

Person details:-

Now here we would like the report to show data in the following format (in repeated fashion)

1st record of Person Table

All details of that record

2^nd record of Person Table

All details of that record

Now first create a report having following simple query for the data set

SELECT Id, [First Name], [Last Name], Email FROM Person

Now create one more report which we would be using as a Subreport with the following query

SELECT Hobby FROM [Person Details] WHERE (Id = @Id)

Now go to our main report,

Add a List Control to the report.

Drag the fields from the DataSet of the main Person table to the report.

Next drag the Subreport control inside the list control of the report.

Specify properties for the Subreport.

Refer to my previous post for that (little lazy to put those stuffs again)

https://nishantrana.wordpress.com/2011/08/20/using-subreport-control-to-show-1-to-n-related-information-in-ssrs-report/

Our final report would look like this.

Hope it is helpful.

Suppose these are our two tables, Person and Person Details, having one to many relationships to each other.

Person:-

Person details:-

Now first create a report having following simple query for the data set

SELECT Id, [First Name], [Last Name], Email FROM Person

Now create one more report which we would be using as a Subreport with the following query

SELECT Hobby FROM [Person Details] WHERE (Id = @Id)

Now go to our main report, select id column, right click it and select add column to the right.

Drag Subreport control from the toolbox there in the new column added

Next right click it and select Subreport properties

Specify the detail report as Subreport.

For visibility specify the following options (report would be auto expanded and we will have + sign in front of our id column through which we can toggle the visibility of the subreport)

For Parameters specify value for the id to be passed from the main report.

That’s it. We are done J

Hope it is helpful.