SSRS 2008 Report from Sharepoint 2010 list as data source

I have a requirement to develop SSRS Report from Sharepoint 2010 list.
Here I need to pull the data from 2 lists . Since there is a storage issue with the lists,we have moved the items from list 1 to list 2 on specific days.
List 1 will have records(items) 1-10
List 2 will have records(items) 11-20
List1 and List 2 have same structure.
I need to show the records from list 1 and list2 that is records 1-20 in the SSRS Report.
How can we achieve this task?
Please find attached screenshot of the requirement
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Justin SmithSr. System EngineerCommented:
So the report lives on the SSRS server, correct?

This should be all the info you need:
asrithapAuthor Commented:
I am using report builder 3.0 to build reports. Report is published to sharepoint 2010 report library. The report should show merged records from list 1 and list 2. Please see the attached screenshot.
List1 will have records from 1-10. List 2 will have records from 11-20. Both the lists have same structure. Basically List1 items are moved to List2 on daily basis on certaon condition.While creating SSRS Report the List1 records have to be merged with List2 and displayed in report.
ValentinoVBI ConsultantCommented:
I don't think it's possible to really merge them.  A Dataset can only have one SP List as source and two datasets cannot be merged.  The only "solution" that I can think of is to put two tables right under each other to make it look like it's one table...
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Rainer JeschorCommented:
this is not possible out-of-the-box as there is no web service available in SharePoint to return data from two lists.
Either use the approach from ValentinoV or create your own (small) web service which can leverage Linq to SharePoint or SPSiteDataQuery to gather the list items and return them combined.

asrithapAuthor Commented:
Thank you Rainer for your response. I have a question. Could we store this list data into database and pull the report data from Sql database. Could we use Business connectivity Services  to achieve this task? That way we dont have to join the lists and use 2 tables to show the data from list 1 and list 2. I am just guessing if this is feasible.
ValentinoVBI ConsultantCommented:
Hi asrithap,

I'm not familiar with BCS so I can't really say something about that.  But there may be yet another alternative.  Do you know SSIS (Integration Services)?  It's one of the other BI services that ship with SQL Server and its purpose it to transfer data.  There's also a connector available on Codeplex which allows you to use a SharePoint list as source so through this you'd be able to transfer the data into a SQL Server table.

More info: Extracting and Loading SharePoint Data in SQL Server Integration Services

asrithapAuthor Commented:
Hi Valentino, Thank you for your response.But i have a question. If the list have look ups then how does this mapping take place in SQL? Do we have to create a table that has similar structure as that of sharepoint list and create a relationships between those tables?
ValentinoVBI ConsultantCommented:
If the list have look ups

Ow, I'm not exactly sure what you mean with this.  In SSIS the data retrieved from the list would look the same as if it were retrieved from a regular table.  The source component takes care of that.  But SSIS does have a Lookup transformation too, if that's what you meant?  So if you would need to look up data in another list, let's call it List B, based on a column in List A, then you could use the Lookup transformation for this.

You'd first set up two separate data flows, each retrieving the data from List A and List B.  Once the data is stored in SQL Server db tables you could set up a third data flow to transform the data anyway you like, so looking up columns could be an option.  Or you could just use a JOIN between the two tables in a regular SQL query, that would achieve the same result...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.