Introduction
In this posting, I will walk you through the process of connecting a Choice Filter web part to a Chart web part so that the range of data shown in the chart depends on a user selection. The chart type will be a pie chart, and the filter type will be the Choice Filter web part. Interestingly, the Choice Filter web part won't be connected to a list that in turn is connected to a chart web part. Instead, the filter will be connected directly to the chart web part itself. This can be done because the chart web part has the capability to perform its own filtering on the datasets it consumes. Furthermore, it can also consume filter values that it will then apply to the datasets it has consumed. All of the steps for accomplishing this will be discussed here. Note: these steps require that you have at least Design permission level to the page containing the web parts.
Background
The scenario that will be explored here involves enabling a user to select different sets of data associated with routine monthly SharePoint patching and then see the selected data set displayed in a pie chart. A set of data involves the aggregated times associated with eight patching tasks:
- Preparation
- Operating System Update Installation (OSU)
- SharePoint Update Installation (SPU)
- Run Get-SPProduct all SharePoint Servers (GSP)
- Run PSCONFIG all SharePoint Servers (PSC)
- OWA Update Installation and OWA Reconfiguration (OWA)
- Miscellaneous Tasks (Misc)
- Review and Regression Testing (Rev)
These are the general tasks performed every month for SharePoint farm patching. The aggregate total times captured from such tasks are typically recorded to a list having a normalized transactional schema, where values are in minutes, as shown in Table 1 below. These are the times associated with patching two SharePoint 2013 farms. Each row represents a single dataset.
| Table 1 |
|---|
| Title |
Date |
Prep |
OSU |
SPU |
GSP |
PSC |
OWA |
Misc |
Rev |
Total |
Include |
| Patching: production |
7/16/2016 |
0 |
192 |
105 |
139 |
195 |
6 |
0 |
60 |
11.52 |
Yes |
| Patching: Development |
8/10/2016 |
30 |
117 |
124 |
188 |
38 |
9 |
0 |
64 |
9.35 |
Yes |
| Patching: Production |
8/20/2016 |
37 |
144 |
133 |
31 |
35 |
16 |
0 |
36 |
6.93 |
Yes |
| Patching: Development |
9/14/2016 |
10 |
131 |
127 |
120 |
95 |
7 |
0 |
55 |
8.97 |
Yes |
The OSU aggregate is the total time involved with patching all farm Windows servers; SPU is the aggregated time associated with updating all farm SharePoint servers; Rev is the aggregated time associated with reviewing and regression testing; and so on. The times associated with these tasks are recorded for process monitoring and improvement purposes. This sort of data listing follows the standard tabular format for related datasets (having a common X-value):
| X-header | YA-Header | YB-Header | YC-Header |
|---|
| X1 | YA1 | YB1 | YC1 |
| X2 | YA2 | YB2 | YC2 |
| X3 | YA3 | YB3 | YC3 |
The data in Table 1 can be straightforwardly displayed in a chart object. The Date column provides the X-value and the Prep, OSU, SPU, PSC, OWA, Misc and Rev columns provide the Y-values. Entering this data into a SharePoint list and connecting a chart object to this data results in a typical line chart.
This approach works fine for charting table data as lines. However, if you want display data in a pie chart, you have to reformat the data. Pie charts do not display multiple datasets, but only one dataset at a time. This means that a pie chart can only represent the Y-values of a dataset, and not the X value; in other words, a pie chart displays the values for a single row of
Table 1. The process for transposing data from a line chart to a pie chart runs like so:
- Choose a particular dataset (set of X,Y values)
- Remove the X-values, leaving the Y-values (YA1, YB1, YC1...)
- Identify the column headers and pair them with the Y-values
In other words, create a new table having just two columns and listing the values like so:
| Item |
Value |
| YA-Header | YA1 |
| YB-Header | YB1 |
| YC-Header | YC1 |
The first column does not represent X-values, but contains the column headers from Table 1. These then become the pie chart labels for the pie slices. The second displays the Y-values of the dataset. These values are used to determine the relative sizes of the pie slices. Therefore, if you wanted to display in a pie chart the task times for a patching session, you do it for one date at a time, building the list like so (Table 2):
| Table 2 |
| Task |
Duration |
| Prep | 0 |
| OSU | 192 |
| SPU | 105 |
| GSP | 139 |
| PSC | 195 |
| OWA | 6 |
| Misc | 0 |
| Rev | 60 |
where this is the data for the patching session conducted on 7/16/2016, as shown in
Table 1. To display multiple patching sessions in a pie chart, requires adding the data from these sessions to Table 2 and also adding some columns to it as well that enable the table to be filtered for a specific dataset. The general process is:
- Add an additional column to the table that identifies the patching session for the dataset. This is effectively the X-value for the dataset Y-values. Call it the Session column, and it will be used to filter for a specific dataset.
- Add an additional filter column to identify whether a data item should be included in the chart. This is useful when, perhaps, a particular update session only need to engage in some tasks or was a one-off that you don't want to include in your general process improvement data capture.
- Also add a column that provides additional explanation or definition of the task, calling it the Definition column.
- Lastly, reformat the Y-value headers to make them more readable and so that they can be conveniently sorted.
Making these modifications results in Table 3, below.
| Table 3 |
| Session |
Task |
Duration |
Include |
Definition |
| 7/16/2016 | 1) Preparation | 0 | Yes | Review all updates |
| 7/16/2016 | 2) OS Updates | 192 | Yes | Install Operating System Updates |
| 7/16/2016 | 3) SP Updates | 105 | Yes | Install SharePoint Patches |
| 7/16/2016 | 4) Get-SPProduct | 139 | Yes | Run Get-SPProduct |
| 7/16/2016 | 5) PSCONFIG | 195 | Yes | Run PSCONFIG |
| 7/16/2016 | 6) OWA Updates | 6 | Yes | Install OWA updates |
| 7/16/2016 | 7) Miscellaneous | 0 | Yes | Miscellaneous tasks |
| 7/16/2016 | 8) Review | 60 | Yes | Testing and verification |
| 8/10/2016 | 1) Preparation | 0 | Yes | Review all updates |
| 8/10/2016 | 2) OS Updates | 192 | Yes | Install Operating System Updates |
| 8/10/2016 | 3) SP Updates | 105 | Yes | Install SharePoint Patches |
| 8/10/2016 | 4) Get-SPProduct | 139 | Yes | Run Get-SPProduct |
| 8/10/2016 | 5) PSCONFIG | 195 | Yes | Run PSCONFIG |
| 8/10/2016 | 6) OWA Updates | 6 | Yes | Install OWA updates |
| 8/10/2016 | 7) Miscellaneous | 0 | Yes | Miscellaneous tasks |
| 8/10/2016 | 8) Review | 60 | Yes | Testing and verification |
| 8/20/2016 | 1) Preparation | 0 | Yes | Review all updates |
| 8/20/2016 | 2) OS Updates | 192 | Yes | Install Operating System Updates |
| 8/20/2016 | 3) SP Updates | 105 | Yes | Install SharePoint Patches |
| 8/20/2016 | 4) Get-SPProduct | 139 | Yes | Run Get-SPProduct |
| 8/20/2016 | 5) PSCONFIG | 195 | Yes | Run PSCONFIG |
| 8/20/2016 | 6) OWA Updates | 6 | Yes | Install OWA updates |
| 8/20/2016 | 7) Miscellaneous | 0 | Yes | Miscellaneous tasks |
| 8/20/2016 | 8) Review | 60 | Yes | Testing and verification |
| 9/14/2016 | 1) Preparation | 0 | Yes | Review all updates |
| 9/14/2016 | 2) OS Updates | 192 | Yes | Install Operating System Updates |
| 9/14/2016 | 3) SP Updates | 105 | Yes | Install SharePoint Patches |
| 9/14/2016 | 4) Get-SPProduct | 139 | Yes | Run Get-SPProduct |
| 9/14/2016 | 5) PSCONFIG | 195 | Yes | Run PSCONFIG |
| 9/14/2016 | 6) OWA Updates | 6 | Yes | Install OWA updates |
| 9/14/2016 | 7) Miscellaneous | 0 | Yes | Miscellaneous tasks |
| 9/14/2016 | 8) Review | 60 | Yes | Testing and verification |
Note that the Session column lists the denormalized X-values associated with dataset Y-values. By listing the X-values this way, this column can be used for filtering purposes. By filtering for a specific date in this column, it's possible to extract a set of Y-values associated with a specific patching session. In other words, if Table 3 is filtered by a particular date, one effectively gets back a table like Table 2.
In summary, this is the process you need to work through in order to get the data prepared. Once you have a list like Table 3 created, you're ready to configure a chart to use that data. In the next section, you'll walk through the steps for accomplishing this.
Procedure
Step 1: Define Chart web part Filter Parameters
- Drop the Chart web part onto a page.
- Click Data & Appearance.
- Click Connect Chart to Data.
- Select Connect to a List, and then click the Next button.
- Select the website in the site collection containing the denormalized list, select the list, and then click the Next button. You'll now see your list displayed.
- Look up at the top of this list: do you see Filter Data? Click the arrow to expand it. This will reveal filters that you can define. Assuming that you have created a list having column names as shown in Table 3, configure the first parameter like so:
- Parameter Name: Session
- Type: DateTime
- Default value: 7/16/2016
- Now let's also implement the Include parameter. One thing: when you create this column, you selected the Yes/No type. Though it doesn't actually indicate this, it's implemented as a Boolean. When used in a list, and when you access the list in SharePoint, the value that you see for this column is Yes or No. But it is implemented as Boolean, but is not surfaced in that way. However, the Chart web part recognizes that column as in fact Boolean, and the values that it will show for that column are True and False. You don't need to change your list values, but you do need to account for this when specifying default or filter values.
- Parameter Name: Include
- Type: Boolean
- Default value: True
- Click the Preview Data button. This filters the list based upon the filter parameters you configured.
- Click the Next button. This page helps you configure a range of different chart aspects, including the data series' you want to present. For our needs, all you need to do is configure the Series Type, which should be Pie. Select that.
- Now click the Finish button. The pie chart will then be displayed. It needs some cleaning up, so let's do that next.
Step 2: Clean up the Pie Chart Display
- Click Advanced Properties. The first thing to do is configure a "collected slice," or a slice that contains other pie slices having less than some amount. Doing this helps clear up the clutter associated with having a number of very thin slices and their annotations all crammed together.
- From the Select an Element panel, select Series, and then scroll down to the bottom, to the Misc section. The configure as follows:
- CollectedThreshold: 10
- CollectedThresholdUsePercent: False
- CollectedThresholdExploded: True
Like so:
- Now, from the Select an Element panel, select Chart (root element), and then scroll down to the bottom to the Image section. Then configure as follows:
- Height: 450
- Width: 650
- Now, from the Select an Element panel, select Series again, and then scroll down to the bottom, to the Misc section. The configure as follows:
- PieLabelStyle: Outside
- LabelsHorizontalLineSize: 0
- Staying on the Series element, scroll up until you see the MapArea section. Configure as follows (include the "#"):
- ToolTip: #VALY/#TOTAL (#PERCENT)
- This completes chart cleanup. You can certainly do far more here, but this is enough for our purposes. In the next step, you will add and configure a Choice Filter web part.
Step 3: Add and Configure a Choice Filter
- Put the page back in Edit mode.
- Drop a Choice Filter web part onto the page.
- Select Edit Web Part from the Choice Filter dropdown menu (hover the cursor over the web bar to see the dropdown appear).
- In the Filter section, enter a name for the filter and then enter a list of dates. These dates should be exactly the same as what you entered into the SharePoint list. Also enter a date that will be the default date.
The default date is pushed into the Chart web part, when the page is instantiated and streamed to the user's browser. Having a default value ensures that the chart web part will display a chart when the user first connects to the page.
- Click OK. Now let's connect this filter web part to the chart web part.
Step 4: Connect the Choice Filter to the Chart
- Open the Choice Filter dropdown menu again, and then this time point to Connections, then point to Send Filter Values To, and then select the name of your chart web part.
A dialog will appear. Note that the names of the parameters that appear in the dropdown on this dialog. Our Session parameter appears first.
- Select the Session parameter, and then click OK. Note the date that appears now in the Choice Filter web part.
This is the default you entered previously.
- Click the small filter icon to the right of the Choice Filter web part. The Select Filter Value(s) dialog appears.
- Select another date, and then click OK. The dialog closes, and the page is refreshed, now displaying the data associated with that session date!
- At this point, you're set: you don't need any further significant configuration. The rest is customization.
- Here's a more cleaned up version:
Note that to remove the toolbar, you'll need to edit the web page markup and set the Toolbar parameter of the Chart web part to False. See the references for additional detail on this. Unfortunately, I don't know how to make the chart Title also update per the Choice filter - the Title text doesn't accept keywords. If you know of a simple way, add your comment! For additional hints and guidance on customization, see
References
- If you should subsequently edit the Choice Filter web part again - maybe changing its title or some such - don't be surprised that this seems to break the connection between this web part and the Chart web part. I find this occurs almost every time. Even if I'm in SharePoint Designer and make a single change to a parameter (e.g., changing True to False), and carefully save the file so that nothing else is changed - even this causes the connection to break. The fix is simple: just rebuild the connection in the usual way.
- You can also implement a similar capability using the List Filter instead. This takes a list of values from a column in a SharePoint list that you specify. This approach enables you to update the filter parameter list by just editing a list rather than having to edit a web page (which requires Design level permission).
