Sharepoint | OmanDev

SharePoint List data with SQL Server Reporting Services

Posted by Ali AL Lawati on May 15, 2009 in Development, English | 8 comments

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

sharepoint SPListItem

Posted by Ali AL Lawati on Nov 6, 2008 in Development, English | 6 comments

Shareponit SPListItem

Hello there, well to be honest I am not a fan of Microsoft technologies well any how I cannot live without my XP these days (one of the best things the big M have done), but SharePoint is completely different story. It has a lot of potentials and abilities and once I saw the first time I said to myself this will finish all of my working place’s problems. But I had a feeling something will be wrong in it. To be honest I have faced a lot of challenges many times I was surprised with what SharePoint provides and many many more times I was disappointed with its limitations. But thanks to friends and fellow bloggers who helped a lot out of the problems.

This time I was facing a very strange problem that I am even not sure if it is SharePoint problems or it is just our environment, I was not able to find any similar problem on the net so thought I could document it here.

SharePoint API provides a great API to access list items , search them, add, delete, and update them that save you the effort of reading list information from SharePoint’s database directly, and it is provided in a very nice way. But the updating I have had some strange issue with that I have solved now, but I couldn’t understand why I got it in the first place.

Here I will give you the basics of accessing items from SharePoint Lists and show the issue

To access a list is very simple make a variable and assign it the url where you see the list in the sharepoint

string strDashListRoot = “http://[Servername]/[subsite/subsite]/Lists/[list name]/AllItems.aspx“;// just copy past the link form your browser

Then from the link you can get the site and website and the list easily

using (SPSite site = new SPSite(strDashListRoot))
{

using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists["listname"]; // do not forget to close the brakets after your code is done } }

Now here you can access the list items easily

String information=Lists.items[2][“fieldname”].toString(); // simply put the index of the item you need and then the field name that you want to show

Now the main thing here is when we want to change field of the list item we could do easily this

Lists.items[2][“fieldname”]= “myvalues”;

Lists.items[2].Update();

but that didn’t worked in our environment while doing the folling code worked !!!

SPListItem item = Lists.items[2];

item[“fieldname”]= “myvalues”;

item.Update();

Well may be that is something related to our environment but hope through this simple blog you know how to access and edit SPList items.