Hope all of you from the USA had a great Thanksgiving.
Last week we used a Chart web part with a list data source to show the basic method of creating charts within SharePoint 2010. This week, we will take a similar list displayed using a list web part as the data source. Furthermore, just to make it a bit more interesting, we will connect the primary data list to a lookup table to filter the displayed list to show that the filtering can be passed down to the chart as well. So let’s get started.
If you remember the sample data from last week, I logged the registrations and actual attendance at our monthly open lab sessions for the last year. Now, just imagine have multiple years of data instead of just one year. That is the scenario that I’m going to use here. To begin, I create a separate SharePoint list called: LabYear which contains only a single column, the year numbers for the last four years.
Next I took the list I used last week and added a column for the year so I could store multiple years of data. Rather than just using a single line of text for the year (I’m not calculating anything using the year so it does not need to be a numeric value), I created a lookup column that pointed to the other list. The only column in the lookup table is the LabYear column and fortunately it is the only thing I need.
At the bottom of the column settings is a new setting specifically for lookup fields that define the relationship between the two tables. To those of you familiar with referential integrity between tables in a database such as SQL Server, this new setting lets you create a referential integrity link between the current table and the lookup table. Click the checkbox to Enforce relationship behavior. By default, SharePoint creates a Restrict delete relationship. This means that it will not allow anyone to delete a record (item) from the lookup table if the value in the linked column is used in one or more records (items) in the detail table. The Cascade delete option means that if someone deletes a record (item) in the lookup table, SharePoint will automatically delete all records (items) in the detail table with a matching value. This allows us to work with related SharePoint lists more like we work with related tables in a database.
When you click OK to close the column settings page, you may see the following dialog which tells you that in order to enforce a relationship between tables, you must index the current column. This feature is needed for performance reasons to make it easier to find matching values in the current table. Click OK to index the column.
You should now be able to create a new page and place both lists on the page using the automatically generated web part SharePoint creates for any list. The following figure shows the two lists. But other than determining what values we can use for the Year column in the Open Lab Attendance table, there is nothing connecting the LabYear web part to the Open Lab Attendance web part. To filter the Open Lab Attendance web part to show only the items from a selected year, we need to create a connection between the two web parts.
Before we can create a connection between the web parts, you need to edit the page (if you are not still in edit mode), by using the dropdown menu for the web part found on the right side of the web part title bar. Let’s begin from the lookup list, LabYear.
Starting from the lookup table, edit the web part and then use the dropdown menu again and select Connections which now appears. The fly-out menu has several options on how to connect the current list to other web parts. Since we want to send the selected year from LabYear to the Open Lab Attendance web part, select Send Row of Data to, and then select from the next fly-out Open Lab Attendance as shown in the following figure.
SharePoint now opens a dialog to help you define the connection between these two web parts. In the first panel, you need to select the connection type from the Open Lab Attendance web part’s point of view. The connection type should be: Get Filter Values From because you want to get the year from LabYear.
On the second page of the configuration dialog, you need to define the fields for both the Provider and Consumer. The Provider in this case is the field in LabYear and the Consumer is the field in Open Lab Attendance. In both cases, the dropdown list displays only the fields in the corresponding list. The following figure shows the completion of the page.
After clicking Finish, you will notice that the LabYear web part now has a new column named Select with a diagonal double arrow. By default the first item in the table is selected (notice the filled-in arrow heads). However, since I did not yet enter data for 2008, the Open Lab Attendance web part does not show any data.
However, if I click on one of the other values where I do have data, you will see the attendance data for that year in the Open Lab Attendance web part. This is one way in which you can easily let the user filter the data they want to see on the final page. The following figure shows the result of selecting 2010.
You can now begin creating the chart definition using the techniques discussed in last week’s blog: https://sharepointmike.wordpress.com/2011/11/19/adding-charts-to-your-sharepoint-pages/. I will not go through all of the steps again on how to define a chart. If you need a refresher, please refer back to last week’s blog. Rather I will start at the point where I need to choose a data source. This time, I will select Connect to another Web Part as shown in the following figure.
When you click Next, you must select which web part you want to use as your data source for the chart. In the dropdown menu, you see references to both the LabYear and Open Lab Attendance web parts. The following figure shows that I need to select the Open Lab Attendance web part which has the detailed attendance data I want to chart.
Step 3 lets you choose the source data format. For our current example, the only option is Table so select it and move on the next page of the data source wizard.
Next you need to define which columns you want to chart. In the following figure, you can see the definition of the first data series to be created on the chart. In this case, I selected Enrollment as the Y-axis field and Month as the X-axis field.
But wait a minute, Month does not appear in the dropdown list. The trick here is that when you create a custom list, SharePoint populates that list with an initial default column named Title. You cannot delete this column, but you can rename it to something that makes sense in the context of your custom list. That is exactly what happened here because I renamed the original Title column as Month. However, in the dropdown list to select fields, you still see the original column name, Title, which SharePoint continues to use internally. Therefore, the following figure shows me selecting the field Title even though it will display Month in the resulting chart.
I also set the name of the series (rather than default) so that the series name can appear in the legend. You can also customize other properties of the series, but this should be enough to get started.
Using the Series dropdown, You can then define a second series to be included on the chart. In this case, I will choose Attendance for the Y-axis. Again you can customize other properties of this series and/or any other component of the chart.
When you finish customizing your chart, you may be surprised by the chart that appears on the web page. It may look similar to the one below. It may not be obvious, but this chart plots all of the data in the Open Lab Attendance list, not just the data for the selected year. What happened?
The problem is simply a matter of timing. The filter criteria needs to be refreshed to limit the data displayed in the chart. You can do this simply by clicking on one of the other years and then if desired, clicking back on the year that you wanted. This will reset the data that the Open Lab Attendance web part passes to the chart web part. The following figure shows an example after selecting the year 2010.
That’s it for this example. Next time, we will look at using External Data sources with the Chart web part.