Interactive Charts using Google Visualization API: Examples

This post describes the use of the tool posted here. The latest changes are found here.

This post will be updated with examples on how to utilize this tool to build various charts. I will add examples upon request. If you have a smashing chart or a special trick, please send it to me and i will post the images here.

Combo chart

The chart:
IMG

The list:
IMG

The configuration:
IMG

The reason for using “chartArea.left:50” is to align the chart left so that the legend is not truncated.

NOTE: The “Column index” used when setting up the series is one notch down from the index shown to the left of the columns. This has to do with the series only applying to the numeric columns.

How to put multiple charts in one page

Here is a few screenshots showing how to put multiple charts in one page.
IMG

IMG

IMG

You must ensure that the CEWP holding the code is placed below all containers.

Motion chart

The chart:
IMG

The list:
IMG

The configuration:
IMG

URL filter

This filter is applied in the “Custom CAML” section like this:
The configuration:
IMG
Use the FieldInternalName of your field – my field is named “Status”.

The URL:
IMG

The chart:
IMG

Manual filter setup filter

This is used to filter on “Single line of text” columns. This filter is applied in the “Custom CAML” section like this:
The configuration:
IMG
To get the CAML, select the checkbox “Create a filter field above the chart using”, select “manual filter setup” and then click “Build CAML from selected filters”. You need to know the FieldInternalName of the field and insert it in the placeholder in the CAML.

The list:
IMG

The chart:
IMG

Grouped by choice column value

I got a request from Michael:

Hello,
Im trying to chart a Calendar, i created a Column called “Category” (its a drop-down box with the categories)
Now i want to create a chart that:
– is a bar chart
– the h axis is “created by”
– the v axis is a the sum of a column that calculate the hours between end and starttime of events (allready solved this with a formular)
– the Bars are grouped by the Creator
– the chart is stacked (allready done)
– the stacked bars show the name of the category
– for each category a specified colour

Here are some screenshots of the list fields

The “Time” field calculates the difference between start and end:
IMG

The Category field is a plain choice field:
IMG

The series in the chart are made up by one calculated column per choice in the “Category” field:
IMG
Make one for each category.

The configuration:
IMG

The chart:
IMG

Please make requests, and i will post examples to the best of my knowledge.

Alexander

42 Comments on “Interactive Charts using Google Visualization API: Examples”

  1. Hi Alexander, thank you for beeing open and share with all your great solutions. I’ve been looked on all those and find them excelent in day to day activity and with highly flexibility.

    I’m trying to customize more this chart functionality and added the isStacked property set to true, but unfortunately if I combine bars with line series I get an strange javascript error, and I’m loosing all the mouse over functionality.
    Could you please let me know if its’ posible to use those options?

    Many thanks again for your help
    Marius

  4. hi Alexander,

    This is awesome work !! I recommended this to many colleagues and they find it very useful in their daily reporting.

    I wanted to see if we have a way to create a indicator chart which compares a sharepoint list column to a static benchmark value and show green, red, yellow indicator bar. more like KPI’s.
    Thanks in advance and great work again.

    -VM

    1. Hi,
      If you use the Gauge chart you can set options for green yellow and red. There is not any method of obtaining these values “live” now, but if you look at the documentation over at Google’s, you will see that setting up a chart from scratch is fairly easy.

      Of course you will face the problem of getting the values from SharePoint.

      Take a look and see what you can make out of it.

      Alexander

  5. Hi,
    Thanks for the awesome script.
    I was trying to use the geoMap type in order to show values related to some countries.

    Since all the countries are in the Middle East region under options i need to put the region value as 145 (this is based on the geoMap help http://code.google.com/apis/chart/interactive/docs/gallery/geomap.html)
    When i use the google script and change it there using ‘145’ as the value for region it works. But it does not work on this script.
    I think the issue is that the field only accepts string.
    I tried to put 145, ‘145’. none of these work.
    If however i put the region as AE then it shows UAE Only.
    Do you have a solution for this?

    Thanks

  6. Alexander,

    I had all charts working in my environment until I moved the site (as site template) to the production. Now the Charts doesnt appear anymore? It points to the correct script location. Do you have any idea why it might be happening. Javascript is enabled.

    Thanks

    2. Hi,
      The charts will not survive being moved as a site template as they have a connection to the source list using the list and view GUID.

      These GUID’s will change when the site is moved.

      I have planned an option to use the displayname for the list and view, but haven’t had the time to complete this feature.

      Alexander

  7. can this chart across sites in the same collection? Either up or down? I cant get the URL to change, it looks like it has the capability but maybe I missed something.

    1. Yes, sites in the same collection can be used as long as the user has rights to the sites/lists.

      The web selector interface is different for site collection administrators and “the others”. SCA has a dropdown and non-SCA has an input field to write in.

      I do have some unresolved issues with the latest release v2.9.3 so if you are using this, try v2.9.2 or wait a day or two for an updated release.

      Alexander

  8. Thank you for the update. New question. Is there a way to change the table header text on a table chart. I have a table in an issue list. The data is returned in 2 columns. the status column and the count column. The first column is “Status”: Format as string and merge dup, the second column is “Status”: count, but both headers show status. Like to be able to change the second column to read something else. Or maybe I setup the table incorrectly.

    1. Hi,
      The label is the column name. To have another text on the label, create a calculated column with your preferred name that uses a formula like this:
      =Status

      Alexander

  9. Hi Alex,

    I think the combochart is not showing the correct numbers, if you take a close look, ‘Mars 2010’ is shown twice instead of adding up to just one entry.

    Chris

    1. Hi,
      My bad, if you look at the data source (image below the chart) you’ll see that i have a typo – it should be “March 2011”.

      This example does not “merge” the values and therefore the chart is correct based on the data set.

      Alexander

    2. Hi Alexander,

      that’s that word I was missing “merge”. I wasn’t sure if this chart is using a merge kind of option or not and obviously it isn’t 🙂

      Chris

  10. Hi Alex,

    I have a task list and want to show a pie chart or bar chart by the assigned to and another by status. How do I do this? I downloaded the version 3.1

  11. For status:
    Column “0” = Status – Format as string – merge duplicates
    Column “1” = ID – Count

    For Assigned To:
    Column “0” = Assigned To – Format as string – merge duplicates
    Column “1” = ID – Count

    Alexander

  12. Hi Alexander,
    thank you for is fantastic script. I need some advice. I have a bar chart with stack columns and and i would like to have a horizontal line ad the value 100. The legend of the x axis ist 200 – 400 – 600- 800, so it would be much easier to understand the char if you see this 100 line.

    britta

  13. Alexander,

    Using ChartUsingGoogleVisualizationAPI_v2.9.2-packer.js, I have a column chart that works like a combo chart. I have several data items displayed using bars and one data items displayed using a line. Works great.

    However, recently I needed to create a new set of charts for a different page. I wanted to use the latest revision, so this time I used SPJS_ChartsForSharePoint_v3.2.2_min.js.

    All of my new charts work fine except for my new combo column chart…when I set “series” equal to “{3:{type:”line”}}” I get a syntax error.

    I tried several times, but each time I encounter the same syntax error. Even if I copy all of the settings from my working v2.9.2 combo column chart, I still encounter a syntax error for the option that defines “series” equal to “{3:{type:”line”}}”.

    Can you recommend a solution?

    Thanks!
    DF

    2. Alexander,

      Previously, I used {3:{type:”line”}}.

      Today I tested using double quotes around the property as you recommended. i.e. {3:{“type”:”line”}}.

      In both cases, I encountered a syntax error for my series.

      Thanks,
      DF

    3. Hi,
      The syntax is correct, but ensure you have the correct double quotes – do not copy them from the comment as they are “wrong type” double quotes.

      Alexander

    4. Alex,

      I manually input double quotes, single quotes, single doubles, double singles, and double doubles 🙂

      In all cases, a syntax error was returned 🙁

      DF

  14. Mr. Bautz,
    I’m having some difficulties trying to understand why my bar chart isn’t working in SP2010. My company hasn’t switched over just yet, so the chart works fine in 2007.

    I’m in testing phase right now and all I get is the “Chart configuration for” box. Anytime I try to save the config, I get an error and that config box again.

    If you need screen shots, I can provide that. Is list size a factor in using the charts?

  15. Hi Mr. Alexander,

    I’m keep getting syntax error in configuring of combo charts.

    option : series
    value : {2: {type: “line”}}

    my sharepoint list data field source configuration
    Status – choice (dropdown “Open, In Progress, Overdue, Close”)
    Priority – choice (dropdown “High, Medium”, Low)

    also when I use column chart, the sequence of my legend for Status and Priority become “High Open Medium In Progress Low Overdue Close”

    I expect to be:
    “High Medium Low Open In Progress Overdue Close”

    any suggestion please?

    Thank you very much.

      1. Thank you Sir Alexander. Yes I did that syntax too but with no avail. Any idea..?

      2. I’ll have to look at the configuration screen – send me a screenshot and I’ll take a look.

        When it comes to ordering you must order the source list view. There is currently a bug if you use “merge duplicates” as the order is not always respected. This will be fixed in the next release.

        Alexander

  16. Alex,
    Can you please send an example of how to configure a Pie Chart. I have a list of all the tasks conducted by the Program and would like to have a pie chart showing percentage per status

    Thank you,
    Kirill

      1. Thank you for a quick response, Alex. I didn’t reply to the email with the screenshot as it said “donotreply” and there is no option to upload an attachment here. Any advise? Btw, I thought I saw a screenshot of an example configuration couple of years ago on your site. If you can point to the existing example, that would also be great.

        Thank you for all your help

        Kirill

      2. I’m not sure where I have a pie chart example so just send me a screenshot and I’ll look at it. You find my email in the “About me” tab above.

        Alexander

  17. Hello,

    Thanks for your great tool!

    I have having an issue when trying to set the Legend values on a column chart using version 3.4

    I set the following values
    Option: legend
    Values: {textStyle: {color: ‘blue’, fontSize: 16}}

    But it gives me a syntax error when i refresh the chart. Can you please advise what I am doing wrong? Thanks

    1. You must use a proper JSON string syntax (unfortunately the examples in Google’s pages are not using the correct format):

      {"textStyle": {"color": "blue", "fontSize": 16}}

      Hope this helps,
      Alexander

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.