Creating SSRS report in SharePoint 2010 with multiple SPLists

Question

I have SP Lists

Students: StudentID, Name, Address,Email Course: CourseID, Name, Duration, Fees StudentCourse: StudentID, CourseID

How can I query these lists and create a SSRS report displaying StudentID, SudentName, CourseID, CourseName, Duration in a table. Need to combine these lists to create a dataset.

Answer 1

To do this without building deployable code and only using the SSRS Report Builder, use the LookupSet and Join commands. Load both the Student and Course lists as datasets, setting any filter queries you might need and to join them in a tablix, you can use some expression along the lines of:

=Join(LookupSet(Fields!StudentID.Value, Fields!StudentID.Value, Fields!Name.Value, "Course"))

Create the Tablix based on the primary list, then in the join expression, use the second list to join on. The 1st parameter in the Expression is the Primary Key, the 2nd is the Foreign Key, the 3rd is the value to extract from the join and the 4th is the Foreign dataset name

The LookupSet information on Technet

Answer 2

Simply fetch data from different lists in datatables and than join them.

Follow the link it will solve your problem

http://www.a2zmenu.com/Tutorials/SharePoint/List-Join-operation-in-SharePoint-2007---2010.aspx