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
Related posts:
- sharepoint SPListItem
- Increasing performance and adding flexibility using arrays instead of recordsets in ASP with databases
Hi Ali,
There is a 4th option: SharePoint List Association Manager (SLAM), an open-source project my company built/maintains on codeplex (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.
Actually, best of all is probably that it’s free and actively supported. Please check it out (there’s an illustrated guide and an intro video on the codeplex site) and drop us a line on the discussion forum or at slam@awsystems.com if you have any questions.
Yours,
Allan
i like the 4th option
#2 Works, but only in a 32bit environment as Access is 32 bit only.
this is a good site, bookmarked
I want to quote your post in my blog. It can?
And you et an account on Twitter?
hello, it will be my pleasure if you would like to qoute my post in your blog , please go ahead
and yes you can follow me on twitter @ali4ever (http://twitter.com/ali4ever)