Sql | 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

تلميحات SQL 1 البحث السريع بدون استخدام like

Posted by Ali AL Lawati on Jan 11, 2009 in برمجة و تطوير, عربي | 0 comments

السلام عليكم و رحمة الله و بركاته ، صارت فترة طويلة لم يكتب شيء في هذا الجزء من المدونة و هذا غالبا بسبب كسلي

فكرت اضع بعض تلميحات ال اس كيو ال SQL كون لا بد لأي مبرمج في هذه الفترة ان يستخدمها لكي يتصل بقاعدة بياناته

هنالك الكثير من الحركات الجميلة في هذه اللغة التي نغفل عنها لقلة وقت الإطلاع و هذه أحد تلك الحركات

البحث السريع دون استخدام like

في حالة اذا كان لدينا في احد جداول قاعدة البيانات عمود يحتوي على نص طويل مثلا نص قصة أو مثلا تدوينة أو رسالة او موضوع منتدى طبعا هذا النص سيتكون من العديد من الكلمات – الآلاف منها — .

في حين الحاجة للبحث سنبحث عن كلمة معينة في جميع نصوص مواضيع المنتديات مثلا . فأسهل طريقة لعمل ذلك هي

“Select title from articles where body like ‘%database%’”

ولكن ماذا لو كان لدينا الكثير من المواضيع (بالتالي رفوف كثيرة بالملاين في جدول الـ articles ) حينها سيصبح البحث بطيئا جدا
فاذا كان الهدف من البحث لديك هو الوصول للنتيجة بأسرع طريقة فهذه إحدى الطرق التي اجدها جيدة في الـ MYSQL
أولا عليك جعل جدول الـ articles من نوع الـ MyIsam
ثانيا : علينا اضافة خاصية ارضفة للعمود body و ذلك بالأمر التالي

ALTER TABLE articles ADD FULLTEXT(body);

الآن حين البحث اذا استعملنا الاستعلام التالي الذي يبحث عن كلمة database في عمود الـ body من جدول الـ

“select title , MATCH (body) AGAINST (‘database’) as rank from articles ORDER BY rank desc;”

سنحصل على نتيجة مثل :

|title | rank |
—————–
|sqltip | 1.3 |
| eclipse| 0 |
|.NET | 0 |

هنا الرانك عبارة عن مدى تطابق النتيجة اثناء البحث وكلما كان اكبر كلما دل على تطابق اكثر طبعا الصفر تعني عدم الحصول على الكلمة في النص و يمككنا عدم اظهار النتائج التي تحتوي على صفر بالاستعلام التالي

“select title , MATCH (body) AGAINST (‘database’) as rank from articles where MATCH (body) AGAINST (‘database’) > 0 ORDER BY rank desc;”

اتمنى ان تكون هذه التلميحة مفيدة لكم

لرؤية التدوينة في نسختها الانجليزية تفضل هنا

SQLTip1: search database faster without like

Posted by Ali AL Lawati on Jul 29, 2007 in Development, English | 4 comments

SQL tip 2, SQL tip 3
well all of us need to deal with databases and sql query and most of the time we need to provide searching abilities to users.

well the easiest way is using like i.e to search articles containing the word “database” this query is goo enough “Select title from articles where body like ‘%database%’”.

but what if we have the articles table so huge and contains lots of rows and each row has a huge article !!!!, well simply the database work will be slowed down and will effect the work of other functions of the applications.

if this is the situation indexing the body column of the table and searching the index would be much faster (( well yeah i know this is a basic knowledge for database experts but most of the developers dont use it that why i am mentioning it))

so lets see how this is done in MYSQL:

to create the index you have to do this

ALTER TABLE articles ADD FULLTEXT(body);

then while searching the query will be something like

“select title , MATCH (body) AGAINST (‘database’) as rank from articles ORDER BY rank desc;”

this will return some thing like
|title | rank |
—————–
|sqltip | 1.3 |
| eclipse| 0 |
|.NET | 0 |
____________

where rank is 0 where it didnt found the searching keyword
and it is bigger where-ever it finds the searching keyword more (more relivent)

so to get only the good results the query will be

“select title from articles where MATCH (body) AGAINST (‘database’) > 0 ;”