SharePoint List data with SQL Server Reporting Services
SharePoint is becoming more populler every day, companies from around the world are starting using it as main intranet site of their headoffice.
since sharepoint provide (as promissed by microsoft) the highest compatibility with Microsoft Office , exchange server, active directory and combine them all into a intranet portal.
many companies started using this software either publishing site ( where anouncements & news could be easily shared with employees) , or team sites ( to manage work of small teams and assigning tasks to them) , or document center, or other many things.
Having huge amount of requirments of bespoke (custom) softwares that fullfill working enviroments needs developer having access to shareponit started using the lists & content types capabilities to create small & medium custom applications based on sharepoint lists
(well if that is good or bad is another issue but i think these two links clearify them link1 , link2 ).
Any how having these applications raise the need for reporting tool to genirated reports depending on the data entered in sharepoint lists/conent types, and for my suprise Microsoft's best reporting tool SSRS (SQL Server Reporting Services) don't have a nice way to provide this functionality. This thing made me search for ways of doing this and here are my findings:
eventhough SSRS do not connect in a straigh forward way to SQL server we can have workarounds :
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.
2- Creating a link table in MS Access 2007 that links to sharepoint lists (amazingly this is very easy ), 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 (do you suggest this way please write comments)
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
well these are the work arounds i could find for using SSRS as a reporting tool for SharePoint , but if you are willing to invest a bit to make this thing possible there are some plug-ins that provide this functionality such as Enesys RS Data Extension but again not sure how good they are in performance and other stuff.
hope this post will help you if you are dealing with SharePoint , feel free to discuss any thing and leave comments.
update 16/5/2009
4- the fourth way is to map the list directly to a database (Well i didn't wrote that because two things first it is redundancy, second it is hard to keep updating both the database and sharepoint lists when-ever any change happens to any record, but Mr. Allan seems to have a great solution for that in the comments which is "SLAM " (http://slam.codeplex.com)
SLAM allows you to easily maintain SharePoint data by hooking up against lists (or content types!) and having the data automatically synchronized with SQL server in real time. Best of all, it converts SharePoint look-ups to associations (with any multiplicity, one to one, one to many, many to many) that you can easily built queries against.
Thanks a for letting us know about this tool
update 21/5/2009 :
Well this time i tried the 2nd way on production (it works very well while it is on my machine in visual studio) but if we need to add it to the reporting services server it didn't work on my enviroment not sure if it works on other enviroments
Full Feed