http://rtmgroupq8.com/en/creating-ssrs-reports-from-two-sharepoint-lists-or-more/

Create SSRS Report for SharePoint

1- Open SQL business intelligence development studio from Microsoft SQL Server 2008

2- Select file —> new project.
3- Select Report server project and change name of project.

4- On “Solution Explorer” right click on project name and then point to Add —> New Item.
5- Select Report from Template and change the name of Report then click Add.


6- After creating the report, you must create data Source and Data Set from “Report Data box” (if you can’t see “Report Data box” click Crtl+Alt+D).
7- In “Report Data box”, right click on Dataset and select Add Dataset.
8- Change the name of the Dataset then select “use a Dataset Embedded in my report”.
9- For “Data source” option click on the new button.
10- Change the name of the “Data Source” then select “Embedded Connection”, regarding the type option select “Microsoft SharePoint List Type” then type your “Connection String” as the following:
http://servername/sitename
11- Select “use single Transaction when processing the queries” option
12- Now for Credentials option, select “Use Windows Authentication” then click ok button.
13- Now to design your query you need to click on “Query Designer” button.
14- You should now be able to see all the lists available in your SharePoint site, select the list you want to read data from it then click “OK” button and again click “OK” button on the other page.
Up to this minute we created the report and the first dataset.
15- Repeat steps 6-14 to create another “dataset” but this time use the same “data source” created before.
Note: the second list should have a column that has a same data in the first list
16- Drag and drop a “Table” from the Toolbox.
17- From the first dataset select the needed column(s) and drop it in the table.
18- Right click in empty column in the table and select expression.
19- In category list select “miscellaneous” then select lookup from item list.
20- Use the lookup function in “Set Expression for Value” as below:
Lookup (Fields.Col1.values, Fields.Col2.values, Fields.Department.values,”DataSet2”)
Fields.Col1.values: a column from first dataset.
Fields.Col2.values: a column from second dataset that have the same data in Col1
Fields.Department.values: Column that needed to be shown from the second list

21- Click “Ok” button then again click “ok” button.
 
22- Click preview to validate data and you are done.