asked on 9/11/2013

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
ListReport.png

Microsoft SharePoint SSRS Microsoft SQL Server 2008 SSIS

Last Comment

ValentinoV

10/27/2013

Justin Smith

9/11/2013

So the report lives on the SSRS server, correct?

This should be all the info you need: http://technet.microsoft.com/en-us/library/ee633650%28v=sql.105%29.aspx

asrithap

9/11/2013

ASKER

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.
ListReport.png

ValentinoV

9/12/2013

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 Jeschor

9/12/2013

Hi,
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.

HTH
Rainer

asrithap

9/18/2013

ASKER

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.

ValentinoV

10/25/2013

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

Valentino.

asrithap

10/25/2013

ASKER

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?

ASKER CERTIFIED SOLUTION

ValentinoV

10/27/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial