Our PivotPoint web part for SharePoint allows you to summarise data from your SharePoint lists in a similar way to Excel’s Pivot Tables and Charts.
A few users have asked how you can add highlighting or conditional formatting to the Pivot Tables – to do things like highlighting the High Risk column, or any cell above a certain value.
If you’re looking for an easy way to add conditional formatting/ highlighting for SharePoint Lists then check out Highlighter.
In SharePoint 2013 you can add a Script Editor web part (under Media and Content). This blog post walks you through.
For SharePoint 2010 and 2007 you have to use the Content Editor web part (CEWP)
A much better way is to save the script as a .txt file in a document library and then include the file on the page with the Content Editors “Content Link” feature. Christophe walks you through this method here.
jQuery or not?
You could adapt these ideas to run without jQuery if you like – but you’re on your own with that!
You can see the reference to jQuery on the first line of the example scripts.
One thing you should determine is
a) Has jQuery already been added to all your SharePoint pages (by an administrator adding them into a custom master page)? If so then you should remove the jQuery reference from these examples as there is no point in including it twice.
b) These jQuery files are loaded from Googles CDN servers. If you don’t have external internet access you will have to copy jQuery to some other location (such as a document library).
Show me the examples already!
Download and unpack these zip files – inside you will see
.htm files that you can experiment with.
Open them in a browser to see what they look like. Open them in a text editor like Notepad to change how they look and then re-load in the browser (F5).
.js.txt files that you can use in SharePoint.
The two examples are :-
Highlighting PivotPoint cells based on the value
The first example we’re going to look at is to highlight different cells depending on the value that’s in them.
For our example –
- If it’s less than or equal to 160 then we want the text in red
- If it’s more than or equal to 180 then it should have a background in yellow.
The result should be a web part like this.
See the source of highlight-by-cell-value.html and highlight-by-cell-value.js.txt in the ZIP file
Highlighting PivotPoint based on the row and column
In this example we’re looking to highlight a cell based on what Row and Column it appears in.
E.g. in this risk matrix we want to highlight the “High Risk” and “Frequent Probability” in red.
The first is to highlight based on the position, e.g.
Will highlight the cell in column 0, row 0 by setting the background red. (Yes sorry, the rows and columns start from number 0!).
But – if the layout of your table changes in future (e.g. someone adds a “Super Extra High Risk”) column) then everything will move and this will be in the wrong place.
In that case the better way is to highlight by the value of the Column and Row headers – e.g.
See the source of highlight-by-column-and-row.html and highlight-by-column-and-row.js in the ZIP file
- You’re not just limited to setting colours, you can also add in any other effect possible in CSS.
- Use the developer toolbar (F12 in IE and Chrome) if you have an error – this tool will enable you to track it down, but how to use it is beyond the scope of what we can show here!
.css('border','1px solid red');
- You can also add images (e.g. flags)