Building SharePoint Dashboards with Google Chart


Overview

Hey glad that I am back with another jQuery magic post here in Word Press. I apologize for taking so long to write this. I had a long list of TODO’s (rather TO-Writes) that I wanted to share but I got stuck onto one thing after another.

So here I am with another wonderful SharePoint recipe…with mixture of jQuery (essential ingredient) + SharePoint (it has to be) and Google Charts Tool.

I must admit that charting was one of the most required feature and which was missing in SharePoint 2007 (although it made place in SharePoint 2010). It was one of my longtime dreams to build a nice looking SharePoint executive dashboard. I also realized that people love to see Graphics more than the text/numbers. This was the motivation behind coming up with this article. And one fine day I accidently landed onto Google Charts Tool.

The code examples really looked difficult to me initially and it looked like Google level code. But somehow I started working with it and recently I had to create almost 10-11 nice looking charts that gave me enough confidence to publish this post.

Charts Gallery – Awesome collection of Tools

The collection of chart types available in Google Charts Tool is really amazing, I have seen some of the chart types for first time.

Have a look at Google Charts Gallery for the frequently used chart types, but there is really more to it when you look at specific chart types. They have got a really extensive list of charting possibilities.

Data Policy

None of the data is really posted to Google server, or travels through any network. It’s all client side rendering so you can be very sure about your data privacy. In fact you will find this end note in every chart samples.

Sample SharePoint Charts

Before we jump-in to the implementation details, below are some of the screenshots of what we are trying to implement. I am sure this samples will give you enough motivation to read further and implement your own chart, believe me all of those graphs are created using underlying SharePoint data.

Sample 1

Sample 2

Sample 3

Prepare Data for Chart

We will utilize the SharePoint grouping capabilities to calculate the data for chart. My strategy is to group the data by various aspects (for Example City, Customer, and Year). And then add the list view web part onto the dashboard page.

Our magical script is not intelligent enough to do the calculation, it just knows the place to read the information. Here is how I have prepared the data (I have included the list template along with the data).

  • Upload SalesData.stp onto list template gallery of your site collection. [Site Actions à Site Settings à List Templates under Galleries].
  • Create a new list based on Sales Data template; this will get you everything (data/views etc.) required for this sample.
  • Add list view web parts onto the dashboard page; we will use this later to read information.

Include jQuery Library [jQuerification]

I am sure by this time you must be pretty excited about looking at the implementation, alright enough of text and pictures onto this article it’s time to get our hands dirty with some coding.

As always the very first step in creating any magic is to add reference to jQuery files. Recently I shifted my jQuery implementation pattern a bit and instead of keeping a local copy of jQuery script onto SharePoint document library, I prefer to use the Google Hosted CDN. Thanks to Dave Ward for putting 3 reasons to let Google Host jQuery for you, this also saves me from keeping multiple local versions of jQuery.

Following line of code on element will load the jQuery library from Google CDN: – [copy the URL ]

Script 1

Load Google Charts API [gChartification]

Enabling Google Charts API is equally simple. It’s very much similar to what we do to load jQuery, add another script tab and make it point to https://www.google.com/jsapi

Script 2

Load appropriate chart package

You will need to load appropriate gchart package depending on what type of chart you are building. For most of the commonly used chart you can use “corechart” package. To include the chart package onto your code, write following line as first line of your script.

Script 3

While “corechart” contains most of the commonly used charts, there are some other packages that can be used when you are creating a different type of chart. To load a different chart you will use the google.load method (same as example above), provide the package name in the packages attribute. It is also possible to load multiple chart packages in the same script.

Chart Type Package
Gauge Chart “gauge”
Geo Chart “geochart”
Table “table”

Preparing the Code Structure

Once you have the jQuery and Google Chart script loaded along with appropriate chart package, next step is to combine the patterns for both the scripts.

Google charts expects google.setOnLoadCallBack() function and jQuery starts with $(document).ready(). In order to make them work together we have to come up with a structure that satisfied both the library.

Here is what I have come up with:-

Get the Google Chart data from ListView Web Part

Once we have the structure ready for google.setOnLoadCallBack() and $(document).ready() we can start writing the code to extract data and render the chart.

In this step we get the GUID for list view web part to read the information from group headings. I have prepared a short video on the process of reading GUID (Please feel free to comment for screencast).

Next step is to prepare the DataTable object using the data rendered in the ListView Web Part. Copy the “WebPartId” that we captured in the screen cast and assign the same to dataWebPartId
variable in the below script.

The script just does the job of looping through each group header and separating the label with the values. Group headers are located in the TD elements with class ms-gb

Draw Google Charts

Last step in this solution is to prepare the Google chart in the element of your choice (div, span etc.). Just create the appropriate chart object and invoke chart.draw() method along with DataTable we prepared in last step.

Complete Code

Below is the completed code for ProductSales.js file.

Source Code

Here is much awaited section of this blog, all the source code along with list template . Please download the code SudhirKe – SharePoint Google Chart repository.

Follow these steps to use the code from repository:-

  1. Upload SalesData.stp onto List Template Gallery.
  2. Create a custom list using the Sales Data Template.
  3. Add two list view web parts on the home page, make them point to different views.
  4. Get web part id for both the list view web parts.
  5. Paste them onto ProductSales.js and SalesChannel.js script file.
  6. Upload the javascript files onto document library.
  7. Add two content editor web part and make them point to the JavaScript files.

If you have done everything correctly you should see the Google Charts loaded onto your SharePoint home page.

Hope this helps, feel free to comment on this post. I am sorry for keeping it so long.

Happy Dashboarding….

Advertisements