Data Pack v3 Job Grade Breakdown Chart Data

When making any modifications to the Data Pack v3 Job Grade Breakdown slide, it is not entirely obvious how the charts work and the data is populated. The aim of this article is to clear up any confusion and help you find how to go about amending them.

The job grade breakdown slide in the v3 data pack is (at the time of writing) slide #17 in the template file and looks like this.

The slide is in two sections, the job grade response rate triangle on the left and the job grade factor charts on the right. This article will concentrate to the factor charts on the right.

Each chart is populated using data which is held in an attached worksheet. You can access this worksheet by right-clicking on the chart and selecting 'Edit Data' in the context menu.

The worksheet will then appear and look something like this.

Each of the factors has it's own row. The columns represent a range for the percentage difference score for each of the factors. i.e. if the Leadership factor had a percentage difference of -4, the value would be populated in column F for row 2.

Each column has a series which appear when a value is present in the cell. Each of these series have a different colour for each of the difference ranges. i.e. a difference of anything below -14 will have a dark red colour and as you get towards 0, the red colour becomes lighter. As you get in to positive scores, the colour becomes green, getting darker as you move away from 0.

Code

When a request is generated by the user, the WIT service picks them up, recognises what type of data pack is requested, then creates a generator to generate the pack.

In the v3 data pack generator, there is a method to populate this slide. In this method, the code uses OpenXML to populate the worksheet data for each of the charts. When you view a presentation, what is actually showing in the chart is cached data generated from the worksheet data. For this reason, so the chart appears correctly when you first open it, the code also populates the cached data.

Example

Below is an example of a generated data pack showing real data.

And this is the populated chart data for the top chart.

Making Changes to the Series Colours

If you need to make changes to the colours of the ranges, you must follow these instructions for each of the three charts:

1. Make sure that you check out the template file from source control by finding it in Visual Studio's Solution Explorer and right-clicking on it.
2. In the menu select 'Check Out for Edit...'.
3. Open the template file and navigate to the slide.
4. Right-click on the chart and select 'Edit Data'.
5. In the worksheet add a value (i.e. '1') in every cell for the range you wish to change colour. For example, if I want to change the colour for any value below or equal to -15, I would add a 1 in cells B2..B9.
6. Close the worksheet and you should now see the series for that range.
7. Click on a bar in the series making sure that they are all selected (or select them all with a shift-click on each of them).
8. Right-click on one of the bars and select 'Format Data Series...' from the context menu.
9. In the panel that appears in the right of the screen, select the 'Fill and Line' option (the tin of pouring paint icon).
10. Select 'Fill' and next to the Color option, there is a menu button the select the colour.
11. Change the colour to your desired colour.
12. Right-click on the chart and select 'Edit Data'.
13. Remove all the values in the cells that you added in step 2 and close the worksheet.
14. If you need to do the same for another series, repeat these steps for the different range.
15. Save and close the file.