Reporting Services Connection to SharePoint

Question

I need to create reports on SQL Server Reporting Services 2005 (SSRS) on data coming from SharePoint Lists,

i was searching for ways to do that and i found,

1. connecting to SharePoint web service out put XML, the problem here is it sometimes work and sometimes don't, and also i couldn't do inner join between different lists may be it is there but it will be trouble to do it i think (if there is easy way tell me please)
2. Creating a link table in MS Access 2007 that links to sharepoint lists, and then connect to the MS ACCESS link tables from SSRS 2005 : this will work but i am kind of scared of having ACCESS in the middle i always want to avoid using access
3. connecting to the views provided in the sharepoints backend database (there is a view for all lists and another view for all list items) : here column names will be troublsome to create the query the clounm names are like (float1,float2,...,date1,date2,date3,....) , also i am not sure if it is good thing to make SSRS talk directly to sharepoints backend database views

can you tell me which of these ways is best (or if there is a better way plz tell me)

i kind of like the third way but not sure if i should use it

Answer 1

I have summarized this problem on our wiki, but it is not complete.

http://wiki.threewill.com/display/enterprise/Reporting+on+List+Data+in+SharePoint.

Note that we have since tried out the CorasWorks DIT and it looks like a promising approach.

Answer 2

Hi

We are exporting all the data to a SQL database everynight ( we have also started to move items away from Sharepoint because of performance). Then we use standard Reporting Services to create the reports....

• (+) reporting doesn't hit the performance of day to day activities in Sharepoint
• (-) the data is "one day" old

Answer 3

Check out SharePoint List Assocation Manager, SLAM.

Allows you to do exactly what you're looking to do in real time and it's free and very well supported.

http://slam.codeplex.com