In the last two installments of my technical side blog I showed how to build the Chart Web Part using a regular SharePoint list and a list that was filtered with a connected list. This time I will look at how to build roughly the same Chart using data that comes from an external content type, in this case a SQL Server table that holds my Open Lab attendance data.
In prior blog entries I’ve talked about how to create an external content type so while I will repeat some of the steps here for completeness, I leave it up to you to return to my blog entry Create an External Content Type to get the basics of creating a simple external content type.
The easiest way to begin is to open the site where you want to insert the Chart Web Part. Then from the Site Actions menu shown to the right, select the Edit in SharePoint Designer option. SharePoint Designer is a free download from Microsoft. However, be sure that you download the correct version for SharePoint 2010 and not the SharePoint 2007 version. (The Chart Web Part is a SharePoint 2010 feature.) Also note that SharePoint designer is a client tool and should be installed on your client machine. It is possible that you may not have access to use SharePoint designer against SharePoint in your office because the SharePoint administrator has disabled the ability for SharePoint to work. Is your SharePoint administrator being mean? Not really if you consider that he/she is responsible for the integrity of your SharePoint portal and SharePoint Designer, while a very powerful tool, can be dangerous in the wrong hands.
Since you opened SharePoint Designer from your site, all the objects in your site should automatically be available. If you instead open SharePoint directly from the Start menu, you will have to open the site where you want to work manually.
In SharePoint Designer, first click on External Content Types in the left navigation. This shows all the external content types defined not only for the current site, but for the enter site collection. From the File ribbon, select External Content Type from the New group as shown in the image to the left.
In the subsequent dialog, supply a name that SharePoint can use internally for the content type. By default, SharePoint Designer adds that same name to the Display Name field. The Display Name is the user friendly name that can help users identify the external content type when you have built several. The Namespace defaults to your site name and I have not found a good reason to change this. Also for our purposes, we can let SharePoint Designer create a Generic List since we will not be integrating this data with Outlook at this time. However, you must define the External System. This is the connection to the external data. Therefore, click the link: Click here to discover external data sources…
In the SQL Server Connection dialog, enter the name of the database Server. Note that there is no dropdown to select the server. You must know the name of the server. Similarly, you must supply the name of the database by directly entering it. There is no dropdown list to help you. You can also supply a user friendly name for the connection. For example, in the following figure, the database name is SQLSaturday, but because I want to access the Open Lab table only so I entered the user friendly name: Open Lab.
You can choose different ways of authenticating the user. If you are working on a single server, you may be able to connect to the SQL database with the user’s identity. You can even impersonate a Windows identity. However, to allow me to work across several servers, I have created a secure store application id as show in the following figure. The alternative may be to install Kerberos to resolve double hop issues in your server farm. A double hop occurs when your SharePoint server is on a different box from your SQL Server.
After creating the connection, you can select a table, view, or stored procedure to be your data source. If you open Tables as shown in the figure to the right, you will see all of the tables in the database. You can select one and only one table as the data source. If you need data from more than one table, use a view or a stored procedure.
When you find the table you want to use, right click on the table name to display a dialog showing all the operations that you can perform against that table. While you can create individual operations which in some cases might be preferred from a security point of view, I will select Create All Operations to simplify this example.
In the dialog that follows, the first screen merely tells you that you have selected to create all operations against the table. Proceeding to the second screen lets you define some of the properties of each of field. In order to have an updatable table, you must assign a field as the identifier. The SQL statements that SharePoint Designer builds then use this identifier when updating or deleting records to identify the correct record. You may also want to select one or more fields to be used in a pick list. However, for our purposes here, that is not necessary since the Chart Web Part will display all the records.
There is another screen that lets you define a filter on the data. I will talk a little later about the need to create one or more filters, but for now let’s allow all of the data to be used. Without a filter, the External Content Type will limit the rows accessed to the first 200 rows. My example only has 48 records in the table so I will ignore this option. Finally, save the external content type definition by right clicking the dialog tab and selecting Save.
While I will not show the steps here, once you have saved your External Content Type, you should go to Central Administration to define the permissions for the external content type.
Next close SharePoint Designer and return to your site, create a page where you want to display a chart and add the Chart web part. Once added you will select the Data & Appearance link which displays the follow choice.
Normally you would begin by defining your Chart’s visual properties using the Customize Your Chart option. However since we covered that before, I’m going to jump directly to the Connect Chart to Data option. This link displays the following dialog where you would select: Connect to Business Data Catalog.
In the second step, you must select which External Content Type you want to use. You can either:
- enter the name of the External Content Type in the dialog box directly
- enter a portion of the name with enough significant characters to identify the content type
- use the second icon button to the right of the text box to open a dialog that allows you to select the external content type from those defined within the current site collection.
On screen 3, you will see a preview of the data available for the chart. There is an option to filter the data at the top of the list. However, this filter will not work unless you define the filter back in the SharePoint Designer on the filter screen. For example, suppose I created a filter in my External Content Type named: Open Lab Year which filters on the field Year which is an Int32 data type. Then I could use the filter here in the Chart web part referencing the Parameter Name: Open Lab Year, select the type Int32, and supply a default value such as 2011 to display only the data from my external content type for the year 2011 as shown in the following figure.
When you click next, the final page of the chart properties will ask you to define each data series you want to display. Assuming that I want to display both the enrollment and attendance for each lab during the 2011 year, I could very quick configure the web part to display the following chart.
There are some other options I may pursue for next time which use the Lab Year list that I create last time to filter the SharePoint list. I tried to include the Lab Year in this example and while I could define a connection to the Chart Web Part and it appears to be able to find the filter parameter field as the field that I’m connecting into, my current example has a problem I believe with data types. I will go back this week to see if I can redefine some of my data to insure that the year data type can match the data in my Lab Year list to see if I can control the external content type filter from a connected list. I’ll let you know next time the results of that test.