Avatar of asrithap
asrithapFlag for United States of America asked on

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 SharePointSSRSMicrosoft SQL Server 2008SSIS

Avatar of undefined
Last Comment
ValentinoV

8/22/2022 - Mon
Justin Smith

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
ASKER
asrithap

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

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...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rainer Jeschor

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
ASKER
asrithap

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
asrithap

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question