You want to show some data from SQL server in your SharePoint site. It's a very common requirement since the core purpose of portals is to be the single place to look for information and data, no matter where it comes from.
In this post I will try to list all of your options. I hope I got them all.. If you think I'm missing something, please write a comment.
Data View Web Part
Data View Web Part (DVWP) allows us to display data from different data sources in any form we want. It can work with any data source that’s available in SharePoint Designer, like SQL Server, XML files, Lists, and more.
It can format the data in virtually any way you want it, since it uses XSL transformation for formatting the data.
It is available to use only from SharePoint Designer.
This will be my preferred option in most cases.
Pros – Very Flexible, No development required, Easy to customize, Out of the box.
Cons - Advanced formatting might require knowledge in XSL, authentication options limited.
Follow up:
http://office.microsoft.com/en-us/sharepointdesigner/HA100948041033.aspx
Performance Point
Microsoft Performance Point Server (PPS) is a BI product included with MOSS Enterprise. With it you can create advanced dashboards and connect to different data sources. One of the data sources available from PPS, is SharePoint List.
Basically you can create a SharePoint List data source, and then create any kind of KPI, report, chart or whatever PPS has to offer with really minimum effort.
This will be my preferred option in most cases.
Pros – Easy to design, looks good, provides the richest result.
Cons – Requires Enterprise license for MOSS , requires PPS installed, and some knowledge with PPS.
Follow up:
http://office.microsoft.com/en-us/performancepoint/HA102408421033.aspx
Excel Services
Excel Service (ES) is a feature of MOSS 2007 Enterprise that introduces server side to excel and includes web services and Excel rendering engine. This basically means that you can open up Excel, use it's powerful tools and wizards to connect to the data and design it. once you are done with that, just put result online and MOSS will render the XLSX file to standard HTML.
Pros – Can take advantage of Excel features, No development required, Easy to customize, Out of the box.
Cons - Requires Enterprise license for MOSS, designing and displaying options can be limiting, only pivot table supported.
Follow up:
http://office.microsoft.com/en-us/excel/HA100137751033.aspx
Business Data Catalog
Business Data Catalog (BDC) allows MOSS to connect to other systems you already have, and work with them in various ways. A developer creates an XML file that describes the data structure and loads it into MOSS. Once you have a BDC application setup, you can use the built in BDC web parts to query, analyze, and display the data.
Pros - Powerful solution, provides a lot more that just displaying the data, deeply integrated into SharePoint.
Cons - Hard to develop, might be "Overkill".
More on this:
http://msdn.microsoft.com/en-us/library/ms546541.aspx
InfoPath Forms services
Actually I was thinking whether this option is suitable here.. I decided to let you be the judge. InfoPath allows us to create data driven electronic forms that may be consumed via browser when using Forms Services. InfoPath can consume SQL data with use of "Data Connections" and even base the form main data source on SQL tables. It provides rich UI with built in controls made specifically for collecting and presenting data. You can design a form that is based on SQL data source, and then display in in browser using Forms Services.
Pros - No development required, Easy to customize, Out of the box, can take advantage of other InfoPath features.
Cons - Requires InfoPath 2007 for designing, requires Enterprise license for MOSS, not the right choice for just displaying data.
Follow up:
http://office.microsoft.com/en-us/infopath/HP100866391033.aspx
Reporting Services
Reporting Services (SSRS) has been around for a long time now. Starting SQL 2005 SP2, You can have SSRS to work in SharePoint integration mode. You can use Report Viewer Web Part to view SSRS reports inside SharePoint.
Pros – Powerful platform
Cons - Requires knowledge of SSRS, Requires installing SSRS on your SQL Server, and the web parts on SharePoint server.
Follow up:
http://msdn.microsoft.com/en-us/library/bb522800.aspx
Custom Web Part that directly query the SQL server
Obviosly, You can develop your own Web Part to do the job.
Pros - Very flexible (You designed it...)
Cons - Why Develop a custom web part when you have built in alternative specified above.
Conclusion
In this post I have presented different methods for showing data from SQL server inside SharePoint Site. My preferred options are Performance Point if available, or DataViewWebPart, if not. Let me know if you liked it or if you think I have missed something.
-- My Name is Itay Shakury and I’m a SharePoint Consultant --