Charting for SharePoint using Google Visualization API: Update 05.05.2011


I have posted a new version of the “Charting for SharePoint using Google Visualization API” – v2.8.5. The changes are described here, but the background story and the code is found in original article. This post describes the new features and changes from v2.8.

You should read the original post found here, and the update for v2.8 posted here.


Changes from v2.8:

  1. Removed option to “listen to” specific list filter columns using “{list:FieldInternalName}”
  2. Added option to “consume” a list view web part filter (read from the URL). You can now have the chart “connected” to a list view. Further details below. This new feature requires existing users to manually add one column to the configuration list: “ConsumeFilterFromListView”.
  3. Fixed a bug regarding the use of “&” in a custom filter field which broke the CAML
  4. Added a “counter” to display the total number of items below the chart
  5. Added a few “options” to be set in the CEWP code: “showFilterInfo”, “showItemCount”, “itemCountPrefix”, “itemCountSuffix”, “labelIfEmpty”. All these have a default value and are optional.
  6. Added option to pull the CAML from an existing list view to use as “Custom CAML”

Details on item number 2:

In v2.8 i introduced an option to “hook into” the list view veb part filter for specific columns. In this release this is removed and replaced with an option to “connect to” a list view web part and consume all filters applied to any column.

This has a few limitations:
It reads the filter values from the URL and therefore only one list view webpart can coexist with the chart. If more than one list view web part is placed alongside a chart, it would not know which list the filter came from.

To use this option, check this box:
IMG

This requires existing users to add one column to the configuration list: “ConsumeFilterFromListView”. For new users this field will be added automatically when creating the configuration list. The chart configuration list should look like this:
IMG

Details on item number 4:

I have added a “counter” to display the total number of items below the chart:
IMG
This counter can be switched off – see next item.

Details on item number 5:

These variables can be added to the CEWP code:

  • showFilterInfo: true [default] or false. Controls the visibility of the “filter label” below the chart when using a filter.
  • showItemCount: true [default] or false. Controls the visibility of the item count below the chart.
  • itemCountPrefix: A string to put in front of the item count. Default empty string.
  • itemCountSuffix: A string to add after the item count. Default ” items”.
  • labelIfEmpty: A string representing the default label for empty values. Default is “(empty)”.

Example CEWP code:
[javascript]
<div id="MyChart1"></div>
<div id="MyChart2"></div>
<script type="text/javascript">
/*****************************************************
Address all containers
*****************************************************/
// All charts must be represented by a container with a unique id. This container must be present in the page
arrOfChartContainers = ["MyChart1","MyChart2"];

var showFilterInfo = true;
var showItemCount = true;
var itemCountPrefix = "A total of ";
var itemCountSuffix = " items";
var labelIfEmpty = "(no value)";

</script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript" src="/test/English/Charts/Javascript/v2.8.5/ChartUsingGoogleVisualizationAPI.js"></script>
[/javascript]

Details on item number 6:

Use this link to pull the CAML from the list view:
IMG
Select a view in the “View selector” and click this link to pull it into the textarea.


Please post a comment if you have questions.

Alexander

46 thoughts on “Charting for SharePoint using Google Visualization API: Update 05.05.2011”

  1. Do you think it would be possible (in some new version) to add check boxes / multiple simultaneous filter values (as in Excel pivot charts)?

  2. Hi Alex,
    Some nice features added in this update. But it has caused me some problems with regard to layout/positioning. In all places i have used this I have at least 2 charts and usually they are placed side by side by adding the style to the div. But it seams this styling is only applied to the div that contains the iframe for the chart so the editChartBtn, customFilter and itemCountInfo etc. do not follow inline with the chart styling and everything looks a bit odd.

    Is it possible to maybe group all these div’s in one outer one and apply any styling included in the CEWP to that div rather than just the one containing the chart iframe?

    Thanks
    Brian

    1. Hi,
      Try wrapping the “chart div” in another div, styling the outer one like this example:

      <div style="position:absolute;left:300px;top:400px">
      <div id="MyChart1"></div>
      </div>
      

      Alexander

    2. Hi Alex,

      The problem is I have multiple charts. If I wrap one in another div while leaving the others outside I get the pop up saying “The container with id MyChart2, is not defined!”

      So i can only do multiple charts if they are all within another div. Which leaves me with the same problem.

  3. Hi Alex,

    I am absolutely astonished how easy the installation & setup of your charts was. I am working on a map that shows different locations on the google map retrieved from a sharepoint list.

    So far it looks excellent and all locations are shown.

    But I could not come up with a solution if I wanted to have more than one detail shown within the info box (showTip) of each item.

    Do you have an idea how I could do that?

    1. Hi Alex,

      Thank You for your swift reply. It worked pretty good. I wonder if it is possible to load the map first and wait for the markers.

      Currently it takes about 50 seconds for 40 Markers and I fear my visitors dont want to stare at a grey rectangle for about a minute. 😉

      Or is there a loading message I could publish to keep the visitors on this site?
      Thanks in advance for your reply… Very much appreciated!

      Cheers,
      Michael

    2. Hi Alex,

      You’re right.. I am using only the country which is automatically transformed into the corresp. marker.

      I’ll try the difference between the lat/lon method and the the encoding and let you know.

      Thanks,
      Jürgen

    3. Hi Alex,

      Wow! I’ve tested it with a few hundred markers… It went extremely fast if I am using the lat/lon coordinates.

      Is there a chance within the list to have a hidden column that retrieves the coordinates from the country name? I saw one thing called “geocoder” for it and I don’t want to fill the Lat/Lon for each item.

      Cheers,
      Michael

  4. hi Alex,
    First, i really want to thank you for the wonderful tool you have created for us. I am trying to use the Custom CAML-query feature in the same tool.

    (for the below query project1)

    but everytime it says “An error occured in the custom CAML for the chart. Checl list GUID and CAML Query”

    Can you pls help me know what could be the issue. I verified that “Project Name” is of type “Single Line of Text”. What else could be the issue?

    Thanks

    1. thanks for fast response
      pls see the CAML below

      <Where><Contains><FieldRef Name='Project Name'/><Value Type='Text'>project1</Value></Contains></Where>
      
  5. Hi,

    is there a way so specify the column order with caml?

    Lets say i have a column “Status”, with values of “On Hold”, “Started”, “Completed”

    My Chart is configured as “Status, Format as String – merge duplicates” and “Status – count”

    The column chart is displaying 3 Bars based on the items with the current status.

    Is there someway to make the column order “On Hold, Started, Completed” ?

    I know how to order them by name “Completed, On Hold, Started”,
    but i need to order them directly, is this possible?

    1. Hi,
      Try making a calculated column like this:
      =IF(Status=”On Hold”,”1″,IF(Status=”Started”,”2″,IF(Status=”Completed”,”3″,”N/A”)))

      And order your source view using that column.

      Alexander

    2. I created this column “SortOrder”, and the numbers are correct, but the chart wont order them.

      This is the code:

      
      

      [sourcecode]

      Works fine with “Title” for example. Any ideas?

    3. Hi,
      If you are using a view as source for the chart, it is this view that must be sorted. Is it a view or is it custom CAML you are using?

      Alexander

    4. Ok got it,

      i used a view combined with the custom caml, but that didnt work.
      Now i just ordered the view.

      Works fine.

      Thx Alex, your doing great work!

      One more thing, do you have any plans to add functionality for the PatternFomatter? That would be awesome. It could basicly replace the DVWP.

    5. Hi,
      You cannot combine a view and custom CAML – the latter overrides the view.

      I will take a look at the PatternFormatters and see what i can do.

      Alexander

  6. Thank you very much for an excellent and easy way to interface into Google’s Charting API. As with any powerful and flexible tool there can be challenges. The challenge I’ve run into is related to creating column charts of date grouped data. So I have three related questions:

    1. I have a dataset from which I need to display summary charts for a selected year summarized by month- twelve columns reflecting January thru December. I’ve created two calculated columns on my SharePoint list, one that extracts the year and one that creates a string reflecting the year and month, e.g. 2011-01 for January 2011, 2011-02 for February, etc. The view, which is sorted and grouped on this column, gives an accurate properly ordered monthly summary. Unfortunately when I chart the list (using a url filter to select the year) the columns are not displayed in order. Meaning, when the chart is displayed the columns may come up ‘2010-12’, ‘2011-02’, ‘2010-10’, ‘2011-03’, ‘2011-01’, etc. And I cannot find anyway to ‘force’ the chart to either respect the source view’s sort order or to apply a ‘new’ sort through the tool.

    2. It’s probably related to the sort issue, but I would much rather use a month name column (Jan, Feb) instead of the numeric yr-mth label. Is it possible to apply a different set of labels through the API? I know it can be done when using the charting API. But I can’t find a similar option in the visualization API.

    3. Also related to the visualization API, column values only appear as a hover-hint and I can’t find an option to write the column’s value on the chart. Is there an option to enscribe the values when using the visualization API and your tool?

    Thoughts? Advice?
    Thank You! Ralph T

    1. Hi,
      Q1: I assume you are using custom CAML, if so you must set the sort in the CAML like this:
      [sourcecode lanhuage=”javascript”]
      <Where><Eq><FieldRef Name=’Year’ /><Value Type=’Text’>{1}</Value></Eq></Where><OrderBy><FieldRef Name=’MyDateField’ /></OrderBy>

      You can always setup a “dummy list view” to see that the order is correct and the go to the Chart configuration below the Custom CAML textarea and press “Get CAML from selected view”.

      Q2: When sorting on the date solumn in Q1 you can setup your calculated column like “Jan 2011”.

      Q3: This is only possible using pie chart. I hope Google will provide an option for this also in other charts soon.

      Alexander

  7. Thank you! I had no idea I needed to filter a custom CAML. And your recommendation about reformatting the calculated date column works perfectly. Now one follow-on question if I may:

    I have several related charts on a page. And I have a drop-down list at the top of the page that, when changed, reloads the page with a new URL filter and therefore applies the same filtering to all the contained charts. I would prefer to show the item count and URL filter only once. But, unfortunately it appears the showItemCount setting is global. Is there a way to configure the charts so only one of the group shows the count number?

  8. Hi Alex,

    When i try to save the site as template and create a new site fromthat templae…all charts are gone…

    Any better solution to hold them tight on tot he new site aswell.

  9. Hi, Alex,

    I can get this to work from a “normal” sharepoint default.aspx, but I can not get the configuration box to open when I include it on a new webpart page created in Sharepoint Designer. I’m running WSS 3.0 with SPD 2007.

    Any help is appreciated.

    1. Hi,
      I haven’t implemented the dateformatter in this solution. Create a calculated column with the format you want and use this column in the chart.

      Alexander

    2. Thanks for your suggestion. I have created a calculated field using text function and that solves my query.

      I have another request; I need to display a custom text on mouse over instead of the usual values. Is it possible to tweak your code for achieving the desired result? Let me give a brief background about my request. I have a project parameter plotted over time. When there is mouseover at the coordinate point, it displays a value at that point. Instead of just displaying the value, is it possible to have a description field column value also gets displayed on mouseover.

      Thanks for your help in advance

  10. Hi Alexander
    Using Google Visualization API v2.9.2. I can successfully have my pie chart consume filter from a single list view web part, but I’m actually trying to do the opposite: clicking on a slice in the pie chart would filter my single list.
    My pie chart shows statuses of all of my tax returns (some are In Prep, some are In Review, etc). Each status is a slice of pie chart. For example, I’d like to be able to click on the In Review slice, and have it filter my entire list of tax returns to only show those with status of In Review.
    Open to options.
    Thank you in advance
    Bob

    1. Hi,
      I haven’t had the time to dive into this yet, but i suspect the select event will only work for tables. I will have this in mind and let you know if i find out some more.

      Alexander

  11. Hi Alexander,
    Using Google Visualization API v2.9.2. when I use CAML
    I receive an error
    Message: Object doesn’t support this property or method
    Line: 695
    Char: 3
    Code: 0
    URI: ….JScripts/ChartUsingGoogleVisualizationAPI_v2.9.2.js
    Thank you in advance

Leave a Reply