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

28 thoughts on “Building SharePoint Dashboards with Google Chart

  1. Thanks Sudhir. It was a great post. But i would like to implement the charts as in sample 3.

    Can you please share the code for it

      • Hi Sudhir,

        The link has code for Sample 1. But not sample 3. So i’m not able to figure out, which method i should be calling to implement chart as in sample 3. Its exaclty suits my requirement. Would be thankful if you can guide me someway

      • Nithesh,

        That is the one that took me a lot of time to build. The real work is in parsing your data in fix columns and rows format

        One gentleman helped me in getting that (I owe him a beer) http://jsfiddle.net/asgallant/HkjDe/

        Once you have the data in fix column/rows, you can call following method to build the chart

        var chart1 = new google.visualization.ColumnChart(document.getElementById(‘chartdiv’));
        chart1.draw(pivotedData);

        Hope this helps you in implementing your chart.

      • Thanks Sudhir. I guess i’ll need to make it dynamic so that its independent of the number of records in the list

      • Yes Nitesh,

        I shared the answer that I got, you only need to structure your data in such a way that number of columns is fixed.

        Column chart requires such data table. It will create one series per column.

      • Thanks sudhir,

        your post helped me. Working on to make it dynamic to display charts as in sample 3.

        Ghosh..its not as simple as it seems 🙂

      • Nithesh,

        Sharing the code will not be possible. I can tell you what I did, I added the hidden web parts in the page with grouped by each column, so if I have to display 4 columns in the chart, I add 4 hidden web parts and the data grouped by different columns,

        that way the page has loaded the data that I need to use for charting. rest was easy to just scan the element and store column with value onto Google Table object.

        -Sudhir

  2. Hey sudhir,
    I got the charts to work but I am running into an interesting issue. The code is adding javascript evertime I refresh the page. If you get enough hit the page starts to through out of memory errors. I then have to remove the script mannually from the webpart. The two scripts that get added are listed below. How do I get around this issue?

    thanks

    • Reham,

      That’s an interesting finding, are you loading jQuery library from your local SharePoint?

      One way of improving performance would be to load it from Google CDN.

      You might also want to review your code once to check if you can remove some unwanted loops, variables.

      Do you think you have a log of javascripts on the page?

      Regards,
      Sudhir

  3. This is great, I’ve been trying to find a way to display sharepoint data as a graph for a while now (since excel services doesn’t work with it and the Chart webpart in sharepoint 2010 is useless). Do you know of a way to grab Totals that are set on a list (that way you could show sums as well as counts)? I assume you’d need to find the name of the element (like you did for the group element).

  4. Hi Sudhir,

    Great tutorial there! I was wondering though, I have data that I want to graph in csv format. What would you say is the best method to extract data in that format?

    Thanks,
    Rory

  5. Hi Sudhir,

    I have uploaded stp file in sharepoint online 2013 list template gallery. When I try to create a list using the SalesData stp its throwing an error as “Sorry, something went wrong.
    Microsoft SharePoint Foundation version 3 templates are not supported in this version of the product.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s