People have always used charts to help others understand tabular data and to help illustrate trends that may not be as obvious when just looking at the numbers. In SharePoint 2010, you can add a variety of different charts directly to your pages. In this blog post, I’ll show you how to get started with the Chart Web Part and SharePoint Lists and in future posts I’ll look at charting other data sources.
To begin, navigate to the site where you want to display your chart, create a custom list and add your data. Of course there are many ways you can get that SharePoint list. You could enter the data manually, copy the columns and rows of another data source, import an Excel spreadsheet, or maybe even use an InfoPath form to display form fields as metadata in the form library. I’m going to start with a pre-defined table that tracks attendance for our SharePoint Open Labs which we hold every month. We allow people to register ahead of each lab. However, some of those who register do not show up and some of the people who show up did not register. Suppose we wanted to compare the number of people who register to the number of people who attend each month. Perhaps it would also be nice to know how attendance varies over the year.
After creating the data list, either open an existing page where you want to insert the chart to plot this data or create a new page. With the page in Edit mode, select the area/zone where you want to place the chart and open the Insert ribbon from the Editing Tools group as shown in the following figure.
The leftmost column displays the web part categories available to us. Select the Business Data category and click on Chart Web Part in the Web Parts column to the right to select it. When you click the Add button, the web part is added with a placeholder image as shown in the following figure.
Users with edit rights will see two options at the top of the chart: Data & Appearance and Advanced Properties. Most of the properties you need to edit to create your chart are available through these two links.
Clicking the Data & Appearance link first, you may see a message box asking you to save your changes before continuing. Click OK. A page appears that further divides the available options into two groups.
The first, Customize Your Chart, helps you to define the appearance of your chart. The second, Connect Chart to Data, lets you define where to find the data source for the chart. Even though you have not associated a specific data source with the chart yet, you can define the properties that will control how the chart looks. Clicking Customize Your Chart displays the following dialog.
The first step of this dialog lets you select the type of chart you want to create. The Chart Type Categories on the left show 15 different chart categories. Within each category you will find a selection of both 2D and 3D charts providing even more variety in your chart selection. Although not shown here, I will click the Bar category which displays the bars horizontally and select the simple Bar template.
In Step 2 of the dialog, you can customize the overall appearance of the chart by select from one of the predefined themes, drawing styles for the bars (in this case), transparency, size and chart file format. Each time you make a change, you can see a sample chart on the right using your selection. If you find the Auto Preview annoying, you can turn it off and using Chart Preview button to refresh the example chart on demand. The following figure shows the initial selections I made for my chart. You are not locked into these or any other selections. You can return to the properties at any time and change any of the chart properties. So go ahead and experiment.
Step 3 is actually divided into four individual dialogs because there are so many properties when defining chart elements. The first page of this step lets you define a title for the chart and the legend. In addition to defining the text to be displayed, you can define the font, the font color and the position of the text. The circle of dots lets you select the position of the text relative to the body of the chart.
On the second page of Step 3, you can define how to display the x and y-axis. The flexibility here is tremendous as shown by the following figure which only shows the properties for the x-axis. A similar set of properties define the appearance of the y-axis. You can also choose whether to display grid lines for each axis as well as define starting points, ranges, and logarithmic scales if the default property settings are not adequate.
Page 3 of Step 3 lets you determine whether to display labels for each data point and data markers. In this example, the addition of the data labels will make it easier to determine the exact value of each bar. Again you have control over the font, font color and format of the labels displayed. You can even fine-tune the positioning of the labels if you do not like their default.
The final page of Step 3 lets you define hyperlinks and tooltips to each of the data series, legend, and labels. Although I did not do it for this example, you might use the hyperlink or tooltip to display details about each data point in the series such as the name of the people who attended the Open Lab.
If you have been watching the example chart on the right of the dialog, you have seen the progression in the appearance of the chart, but we still do not have any real data associated with the chart. In fact, when you click the Finish button on this page, the dialog will close and the formatted placeholder chart example appears on the page as shown below:
Next we need to associate data with our chart. Click the Data & Appearance link one more time to display the first page of this wizard. This time, click on the link: Connect Chart to Data
As shown in the next figure, you will have four choices for your data source for the current chart. The first choice lets you connect to another web part for the data. Since SharePoint 2007, some web parts can act as data providers to other web parts and some can consume data. In this case, you must have at least one web part, such as a list, displayed on the same page as the Chart web part to act as the data provider.
The second option, and the one we will use here, connects directly to a SharePoint list which does not have to be displayed. We can then select columns from that list to use in the chart.
The third option uses the Business Data Catalog. In previous entries I showed how to create external content types and then used those content types to define external lists and columns in custom lists. As you might suspect, you can also consume the data from those external content types directly in a chart to graphically display data using the Chart web part.
The last option lets you work with data supplied through Excel Services. Since Excel Services works with tables within and Excel spreadsheet in which each column has a column name, we can reference the columns we want to display using the Chart web part. Of course, you could also create the chart within Excel and publish it along with the rest of the Excel data to SharePoint. The primary difference being who defines the chart. Does the Excel spreadsheet creator define the chart when they create the Excel spreadsheet or do you want other content creators to ‘consume’ the data from the Excel spreadsheet in their own chart.
The next step in defining our data source after choosing a SharePoint list is to select the site that contains the list as well as the list itself. Note that libraries are included in the dropdown of the available lists (see figure below). Remember that libraries are just a special form of list that includes a document, image, or page along with the other metadata about the item.
Step 3 in defining the data source shows a preview of the data. Often the entire list contains more records than you may want to plot in the chart so you also have the option to define filters to limit the data included in the chart.
The last step in defining the data source for the bar chart lets you include one or more data series. Each data series is represented by a different data field in the list. Other fields in the list might represent labels, hyperlinks, or tooltips. Data in one field can also be grouped by using values in another field. While two data series are created for the default chart, you can remove the second data series by selecting it from the dropdown under Series and then clicking the minus (-) button. You can also add additional data series by clicking the plus (+) sign, naming the series and then associating it with a field.
When you click the Finish button on the last page of the dialog, you will be rewarded with the image of your new chart as shown in the following figure.
But you don’t have to stop there. If you edit Chart web part properties directly (as you would for any other web part), you can change the chrome settings. You can even change the chart border properties (although you can also change those properties by selecting the Advanced Properties link when editing the page.
Perhaps you are asking whether everyone can see the two links above the chart allowing them to make changes to your carefully constructed chart. Fortunately the answer to that concern is that only users with modify content or edit rights can see these links and thus make changes to the chart definition. Users that can only view your pages will see your final chart as shown in the following and last figure of this post.
In a future blog post, I will illustrate some of the other data options you can use with the Chart web part. See you then.