Charting for SharePoint using Google Visualization API: Update 27.03.2011

05.05.2011 I have released version 2.8.5. Read about it here

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

1. Filter the chart using a choice column from the list

IMG
The dropdown is created from the choice options supplied in the list settings for that column. You set the filter in the GUI like this:
IMG
You must use “custom CAML” and select “Create a filter field above the chart using”. In the dropdown you will find all the single-choice columns in your list. Click on the text “Build CAML from selected filters” to create the appropriate CAML query.

2. Manually build the dropdown filter

If you want to make a dropdown filter for your chart, filtering by a text in a single line choice column you can create it like this. Click “Create a filter field above the chart using”, and select “manual filter setup”. You can then build the filter manually. See the description below the “filter options” textarea for formatting options for the dropdown.

When using a manual filter setup and using “Build CAML from selected filters”, you must change the “FieldInternalNameToMatch” with the proper FieldInternalName of your target field in the CAML. In this example, switch the text “FieldInternalNameToMatch” with “Region”.
IMG
IMG

3. Filter the chart using a value from the URL query string

Use {url:ParameterName} as placeholder in the CAML.
IMG
Before the list is filtered, the chart looks like this:
IMG
Pass the filter in the URL like this:
IMG
IMG

4. Filter the chart by filtering the “attached” view

This only applies if the chart is in a page with a single list view. It reads the filter provided by the list filter action and filters the chart accordingly. There are no real connection between the list and the chart – it merely reads the URL and looks for a filter value matching the placeholder in the CAML.

Use {list:FieldInternalName} as placeholder in the CAML.
IMG
Before the list is filtered, the chart looks like this:
IMG
Filter the list by the appropriate column to render the chart.
IMG
IMG

Important

Users upgrading from v2.7 must add three columns to the configuration list:

  • UseCustomQueryFilter: Yes/No column (boolean)
  • CustomQueryFilterField: Single line text
  • CustomQueryFilterChoices: Multi line plain text

For new users, this list is automatically created, but for users upgrading from older versions these fields must be added manually. In that case, compare your configuration list with this image:
IMG

Bugfixes:
If a column name had “&” or “/” in it, the chart “broke”. Thanks to Eric Guy for finding the bug.

I might very well have forgotten something in this walktrough so please post any comment or question below.

Alexander

51 Comments on “Charting for SharePoint using Google Visualization API: Update 27.03.2011”

  1. This appears to be a powerful dashboarding tool, thanks for sharing. I read the original post and I am still a little confused (fairly new to SP). We are on SP 2010 and I downloaded all files to a Scripts library. I added a Content Editor and used the Content Link to reference the other files. Where do I select the type of chart I want to use along with their respective dialog form. Many thanks

    1. Hi,

      Did the script create the configuration list?

      The only CEWP you need is the one described in the original post.

      The only thing you need to change is the path in the bottom script scr:
      /test/English/Javascript/ChartUsingGoogleVisualizationAPI.js

      Change it to point to your local script.

      Alexander

  2. Hey A,

    I may not understand. I created a table chart. I have a name field and several Yes/No fields to display. I set the name to format as string and the yes/no to format as boolean. my results all come back as an”X”. If I switch it to format as string or number it returns 0 or 1. I know this is on the API side but was hoping you may be able to shed some light.

    also is there a way to add style or format to individual cells. Like when dealing with money () change the color. or even the boolean. the X and the check would be nice to have them different colors. any way to apply format on this side or from your script?

    1. I have confirmed the bug with bool values in v2.8 – i will look into it and release a fix shortly. as for the other question you have the “formatters” – click the link in the “formatter section” to learn how to use them.

      Alexander

  4. Could the script for example send text in an unrecognized format to google and then transfer it back in the intranet? Or is it possible to have the solution completely in corporate intranet so that no traffic would go to google servers at all?

    1. This being a client side solution all data has to be pulled down to the client and the dataset is specific for each chart. I do not think it would be possible to cache it.

      You should select a smaller chunk of data – if possible insert a filter to split it up on for example month.

      Alexander

  6. Great update! Have been looking for a simple solution to filtering within the API. I have one issue: I’m trying to query on calculated columns that return text. I followed your first option above, but am getting an error: “Check list guid and CAML query”. I verified the column names and CAML code, and have also verified the chart works without using this custom CAML query. Any help on this?

  8. How to show an integer without eleven zeros after decimal?

    Example: I use an integer column ‘Year’ for grouping. Here I guess I have to use the ‘Format as string – merge duplicates’. However, charting doesn’t seem to be able to format the number correctly:
    2007.00000000000
    2008.00000000000
    2009.00000000000
    how to replace those with
    2007
    2008
    2009

    1. Hi,
      All “Number columns” have this format in the DB. Either switch the field type to Text or make a calculated column that reads the value from your Number field with a formula like this:
      =MyNumField&” ”

      The added white-space behind the value “forces” it to render as text and should be fixing your problem.

      Alexander

  9. Thanks: I guess the =MyNumField&” ” is the solution for me (I recall some challenges in getting the ordering right in grouped list views when having numbers in text fields)

  10. Hi Alex,

    I downloaded the lates version files 2.8.1 and configured as per the instruction .

    But i am not gett ing options like legends,filtes etc.

    Can you please let me know wht could be the problem?

    Regards
    Sunny

    1. In the CEWP code you must change the script src to point to where you stored the file “ChartUsingGoogleVisualizationAPI_v2.8.5.js”.

      Alexander

  14. Pingback: Charting for SharePoint using Google Visualization API: Update 05.06.2011 « SharePoint JavaScripts

  16. hello Alex,

    Thanks much for your solutions. How can we have a filter where user selects “Title” field which is single line of text?this doesnot show up in the filter? Thanks much

  17. Hello Alex,
    Can you pelase help me with the above question.? Also i get error when i try to use the Filter the chart by filtering the “attached” view ? I have a list called “Documents” I want the user to create a filter for user names and get teh chart accordingly. Can this be achieved?

    Please help me out.

  18. Is there a way of adding a line representing a running total of a column to a column chart? I would like to compare partial costs with the aggregated cost so far for a project.

  19. Hello Alex , thanks for the reply. i have seen the example – but what i need is to have a dropdown like a filter where user can select the name of the person(which is my title field) and get the chart accordingly. But the issue is if I want to use the “Create a filter field above charting” I do not see the option “Title” there since it is a single line of text and not a choice field.

    Thanks much for you help.

    1. Hi,
      To achieve this you must provide a list of the names like the example i linked to above. Replace “Blue|Red|Green” with the names.

      If you would like the chart to be filtered automatically and only show the values for the current user, use “Get filter value from this profile property:Title”, and click “Build CAML from selected filters” to get the CAML Change “FieldInternalNameToMatch” to “Title”.

      Alexander

  20. Hello Alex,
    thank you . I have tried all the ways before posting the forum. What my request is – to have a dropdown where user can select the name which is my “title” field. I cannot use profile property for this. The other alternative is to have a manual filter where user can see a dropdown . but I want this to be automatic + dropdown where user can select values and chart will be displayed.

    Thanks much for your time.

    1. Hi,
      As the field you want to filter by is not a choice-field, the only option is to provide all the “names” in the same manner as the example i have provided for manual filter setup:
      Name1|Name2|Name3

      Alexander

  21. Hi Alex,

    Is it possible to have more than one chart filter? Also, none of my columns show up in the drop-down when I select “Create a filter filed above the chart using: ____”. I only have the even though several columns are “Single line test” columns. Any help is much appreciated. Thanks!

    1. Hi,
      This solution lets you combine the “user profile” filter and a custom field filter. For single line of text fields, your only option is to use the “manual filter setup”. It is only choice columns that show up as options in the selector.

      Alexander

  23. Hi, This is an excellent post. i have used this in my Sharepoint site…and i have around 7 graphics placed show up different details…However the page is taking around 15-20 Sec to load..Is there are way I can make the Loading of the page better please?
    I have all the JQ files downloaded and place in a folder hoping that it would better the loading time but it not make much difference.

    My Other question is on a Column Graph…i have around 8-10 items in the Horizontal access….But the Column Bar looks like a line instead of displaying a Bar…how can i increase the width of the Bar please?

    Thank,
    Ven

    1. Hi,
      7 charts in one page could be to much – depending on the amount of data that makes up the chart. You must remember that this solution is client side – all the data must be downloaded to the client before the chart can be rendered. If you have many items in the lists making up the chart – it will take time.

      Regarding your last question – the option to set the bar width is not available in the Google visualization API.

      Alexander

  24. Hi Alex,

    I am using pie chart and using CAML to get parameters from querystring. I set the page with chart as the default loaded page and the same big red box with comments “the url-filter XXX is missing” because the CAML WHERE could get any default value when page load.

    Is there any way to set default by CAML, such as if, then, else.I tried but seemed not working.

    Or there are some way to hide the piechart when the first time load, and the charts will be shown after click the treeview from which the variables in CAML will be set through querystring?

    1. Hi,
      The intended use for the URL filter is to have a link rigged with the filter value – the user then clicks this link and thus already have the filter in place.

      In the current release there is no support for a default value for the filter.

      If you use a filter value corresponding with the FieldInternalName of the field you are applying the filter to, the error message will at least be better.

      Alexander

  25. Hi Alex,

    I have Single-line text columns used in my column chart and tried to use CAML but i never get values been listed in the “Create a filter above the char using:”..I always see in the drop down. How can I get the column name populated in the drop down please?

  26. Hi Alex,

    I have a list with an id, task, date it was completed and create a YM_dt_comp using year month … than add the & ” ” to build up this as string …
    Very good indeed .. now when a present my chart , the year date comes our aleatory like 201104, 201103, 201105, etc …

    How can I sort the date presentation ?

    regards
    Joao

    1. If you are using a view, the view must be sorted in the correct order. If you are using a custom CAML, you must include an – section in the CAML.

      Alexander

  27. Does this chart filter work on a pie chart too, I would like to be able to select a salesperson for example and have the data in the pie chart update for that selected sales person.

  28. Hola! I’ve been reading your site for some time now and finally got the courage to go ahead and give you a shout out from Lubbock Tx! Just wanted to mention keep up the great work!

Leave a Reply

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