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

jQuery Magic – Search List Web Part


Contents

  1. jQuery Magic – Search List Web Part 1
  2. Overview.
  3. Search Web Part.
  4. See it in action…..
  5. HTML Script 3
  6. Inline Style Sheet 4
  7. jQuery(fication) : Enabling the Magical Library. 4
  8. jQuery: $(document).ready() handler 5
  9. jQuery: The Search() function. 6
  10. jQuery: the processResult() function. 7
  11. References. 7
  12. Summary. 8

Overview

After being quite for sometimes now, I am back with another jQuery magic. The search list web part, this is really handy when you have some data in SharePoint that hardly gets updated, rather than opening the list we could directly add a search web part on the home page and get the data.

Search Web Part.

The search web part that we are going to see will look something similar to figure1. I have made the script to search in the Calendar list based on the event location.

Figure 1

This web part can be configured to search on any SharePoint list. Following section of this article will cover the scripts in detail.

See it in action…

I thought of showing it in action to you before we dig deeper onto the code. Follow the instruction carefully.

1. Download the required files from jQuery Magic – Search List Web Part

· jquery.highlight-latest.min.js: This is the jQuery highlight plunging. This is used to highlight the searched terms. Sincere thanks to Johann Burkard for his Highlight Plugin

· searchList.js: This contains the logic to interact with the SharePoint services and render the required controls.

2. Update the references.

3. Upload both the files onto a SharePoint document library.

4. Add a content editor web part à and update the Content Link property to point to searchList.js

Figure 2

5. Click ok and you are done. You will see the search list web part in your page.

HTML Script

Following is the HTML script that is required to render the UI of the search web part. This HTML defines following element in the web part

quickSearchTextBox: Text box to enter the query.

cmdSearch: Command button to initiate search.

resultCount: Div element to show the number of records found from query.

quickSearchResults: Div element to show the result.

I have used the SharePoint style sheet classes to format the UI look similar to SharePoint.

Inline Style Sheet

The inline style sheet classes have been used to format the output and display it in the form of block. Following are the classes in this style sheet.

– .highlight : This class is used to define formatting for highlighting the search terms in the results.

– .ul: this class is used to remove the bullets in the UL element.

– .ul li : This class is used to display the search results in the form of block.

jQuery(fication) : Enabling the Magical Library

To load the jQuery library, I have used the direct link (ajax.googleapis.com). I am not uploading the jQuery library on my SharePoint (I did that in all my previous posts).

In case you want to load jQuery from local SharePoint site you will have to upload jQuery library onto your SharePoint document library and update the SRC parameter in the following code. (Note: You will have to update the {SPURL} for jquery.highlight-latest.min.js file)

jQuery: $(document).ready() handler

$(document).ready() handler is similar to void main() in C J. This script written inside this hander is executed when the entire DOM elements have been loaded.

jQuery script should be written inside ready() handler. Please refer to jQuery Documentation to learn more about writing jQuery script.

I have used ready() handler to associate various event handlers to the controls in my web part. This contains various ways to initiate search operation and other handler to clear search result when “X” is clicked.

Note that to clear the result I have used $(selector).live() handler, this will make sure to attach event handler with “X” button only when it is present in the UI.

jQuery: The Search() function

The search function takes care of talking to the lists.asmx SharePoint web service. This function prepares the soap request with the query and processes the result.

The soap packet contains the CAML query to search the list. It defines following parameter to execute the query:

– listName : Name of the source list.

– Query : Query to execute against the list

– viewFields: Fields that should be returned in the result.

jQuery.ajax() function is used to send the request to the lists web service. The complete attribute specifies the function name that should be executed once this request has been processed. The process result method will receive the resulting xml from the web service call.

jQuery: the processResult() function

The processResult() method is used to generate the resultant HTML script from the web service call result xml file. The role of this method is to parse the xml and get the required HTML.

Please excuse me for the bad formatting (I am really bad in the designing).

Notice the code that we have used to highlight the searched terms in the result set.

References

http://docs.jquery.com/Main_Page

http://johannburkard.de/blog/programming/javascript/highlight-javascript-text-higlighting-jquery-plugin.html

http://spservices.codeplex.com/

http://weblogs.asp.net/jan/archive/2009/04/09/calling-the-sharepoint-web-services-with-jquery.aspx

Summary

I have done very basic formatting to the search results. Be as creative as you can in the processResults() method, use the jQuery UI library to format the output.

We can build some really fantastic application with no server side coding and deployment overheads using jQuery.