By: Christian Holslin
This is a continuation of the Sleazy Reporting series. This post focuses on the power of External Content Types as a method for data entry to build the data behind a SQL Server Report.
The Objective
As with my previous post, Sleazy Reporting: SharePoint 2010 Lists, we will continue to focus on the objective of building reports using SharePoint. This time, we’re going to expand the playing field to include External Content Types and External Lists, two new features available in SharePoint 2010.
Reporting Fundamentals
Every report needs data. And data needs to be entered, somewhere, by someone, generally into a secure location. What better place than SharePoint to handle this task! Throw a list up on a site, add some columns, setup a convenient public view, give some people access, and let them go to town. Easy, right? Sure… but what happens when you want to create a report? As I mentioned in my previous post, with native lists in SharePoint 2007, short of hand-coding SOAP queries, dealing with PerformancePoint Services 2007, building a complicated Application Definition for the SharePoint 2007 Business Data Catalog, or writing a custom event handler to shadow-copy data into SQL, you really had no straight-forward solution supported natively by the platform itself. Enter SharePoint 2010.
The Setup
In order to build the reporting environment against a SQL Server 2008 database that I will be demonstrating in this post, you will need a few things:
· SharePoint 2010
o Any edition will work. Yes, you can do this with the free edition of SharePoint 2010.
o You will need the Secure Store Service setup and running if you are not using integrated Windows authentication (NTLM or Kerberos). In my environment, I am using Kerberos authentication. I will explain how to set this up in a future post.
· SharePoint Designer 2010
o Also free.
· SQL Server 2008
o You can use any edition for External Content Types.
To follow along with the rest of the post, you will also need a SQL Server database with at least one table with one column. My example here is nothing fancy (yet; that's another post), just a database with a table and two fields: a primary key and a string (like how ID and Title work in a native SharePoint list).
External Content Types
External Content Types are a new feature in SharePoint 2010. They can be easily created using the free SharePoint Designer 2010 utility. They allow you to create a connection to an external database, like SQL Server, through a snappy user interface (no more Application Definition files). Here’s how to create one:
Figure 1: Click New
Start by opening a site and clicking External Content Types on the left Site Objects bar, and then click the External Content Type button under New in the Ribbon. Next, fill out the basic information:
Figure 2: Basic Information
To tie the External Content Type into your SQL Server database, click the hyperlink “Click here to discover external …” This will open a new view where you can add the connection to SQL. Click the big Add Connection button:
Figure 3: Adding a SQL Connection
Enter the connection information for your database:
Figure 4: Database Connection Details
I am choosing Connect with User's Identity because I want the data connection to be made as the person viewing the list. This is a common requirement I get from clients. Frequently this necessitates the use of Kerberos authentication.
Locate the table in your database which contains the data you want to manage in SharePoint. This table will become accessible from SharePoint as a list via the External Content Type you are creating. Pick the table:
Figure 5: Customer Table
And right-click on the table name; pick the Create All Operations choice:
Figure 6: Create All Operations
This choice tells SharePoint to automatically generate a set of standard operations based on CRUD (create, retrieve, update, and delete). Just click next through the dialog box that opens and you get all of the following operations automatically created for you:
Figure 7: All Standard Operations
Keep in mind, my Customer table has the following schema:
· Id - primary key, integer, identity
· Name - variable length Unicode string (50 characters max)
The beauty of External Content Types you create with SharePoint Designer 2010 is that the tool automatically understands and interprets the table structure for you. It can tell the Id column in the primary key, that it's an integer, and that it's automatically generated by SQL Server. This makes it very easy to rapidly expose SQL Server data to SharePoint with minimal effort.
And of course, you can customize the Operations after you create them. I will save that for another post. This is what you need to do to establish relationships between External Content Types.
Remember to click Save (small floppy disk button in the top-left of the program) so SharePoint Designer 2010 saves your new External Content Type!
External Lists
The External List is a new list type which uses an External Content Type. After creating the External Content Type, simply click a button in SharePoint Designer 2010 to create a new External List. This will expose your data to the SharePoint user interface:
Figure 8: New External List
With the External Content Type still open, take a look at the Ribbon. Click on the button labeled "Create Lists and Form" to make a new External List to expose your External Content Type to the SharePoint user interface. This is the last step:
Figure 9: Creating an External List
Fill out the List details, then click OK. You also have the option to create an InfoPath Form by checking the box. This is useful if you need to add business rules to the data entry process. We're going to leave this blank. But try it out on your own. It may come in handy for your situation. Once we click OK, the SharePoint site where we created this External List will now have it available:
Figure 10: Customers List in SharePoint
When we browse to the list, we can see, add to, and modify everything in the Customer table in our SQL Server database right from SharePoint:
Figure 11: Working External List
That's it! Using the CRUD operations that SharePoint Designer 2010 created for us, we can see, add, edit, and delete Customers from our SQL database directly in SharePoint. No custom code, no custom XML, no custom SQL, no need for SQL Server Management Studio, no nothing. Just a pure and simple SharePoint List in Internet Explorer.
Create Your Report
Of course, now that you hooked everything up, simply use Reporting Service to create a report using the data coming from SharePoint. This activity is beyond the scope of this post, but suffice it to say SharePoint 2010 can natively talk to SQL Server with such ease that it is now feasible to build a sustainable and complex reporting platform against a data warehouse which is actually managed through SharePoint. This technology will, no doubt, strengthen the Microsoft business intelligence stack while providing more flexibility to managers and server administrators looking to consolidate their services in SharePoint.
The Payoff
So why do we care?
Power
Give users the full SharePoint experience without compromising the performance and flexibility of SQL Server. Build an Analysis Services Cube against data maintained in SharePoint. You are reporting directly against SQL tables: you can implement database-side transforms, stored procedures, special queries, etc. to assist with reporting without any impact on the data entry process or the SharePoint Farm itself.
Mobility
External List data is not physically in SharePoint. It’s in a SQL Server database. If you change, move, delete, or recreate an External List, the data is still there. The data is no longer married to the SharePoint taxonomy.
Volume
Using External Lists tied to SQL Server databases decouples your data entry platform from the limits of native SharePoint Lists. Some limits remain (items in a single view), but now you can store as many records with as much data as SQL Server can handle (which is a lot, a huge, enormous amount).
No Code
Forget writing CAML, SOAP XML, VB.NET or C#. SharePoint 2010 handles the connection between the List and the Database seamlessly and without the need for programming. Open SharePoint Designer 2010 and go. Not that code is bad, out-of-the-box is simply better.
By: Christian Holslin