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?
ValentinoVConnect With a Mentor BI 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...
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.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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...
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.