Create Dynamic Bar and Pie Charts in WSS with any RSS feed: No code required
When Ming Fung Yong saw the solution, he found another source of data for feeding the charts: RSS feeds. The charts below were created using the Google Charting API against a SharePoint RSS that is then displayed within a Content Editor Web Part. This is a no code, no assembly solution that can be implemented at the site level.
This really opens the charting field now, because if you take this a littler farther, the RSS feed doesn’t even have to reside in SharePoint, much less within the same site collection… think about it.
Setup & Configuration
We would need to setup a list beforehand and view its RSS Feed. It might not be enabled so you might need to get your SharePoint administrator to enable it.
Take a look at the RSS Feed URL and make a note of the List number. We will need this later.
In your site settings, you need to make some minor configuration to your RSS settings. This determines how many items and how long an item will last. The maximum number of items that will be published in the RSS feed is 9999. Maximum allowed 9999 days is approximately 27 years. Highly doubt your chart will be up for more than 27 years? =)
In RSS settings:
If this setting is not configured, you might wonder where does some items disappear to later down the stage. =)
Code
Now to the fun part. The following example shows how the bar graph from Google API is utilized here. Add a Content Editor Web Part which will ultimately graph your bar.
What the javascript does is it loads the RSS Feed(XML), and loads an XSL stylesheet, which is currently stored in doc/bar.xsl. Then it does the transformation.
Content Editor Web Part Source Code
<script> //[email protected] //Sharepoint Multibroswer Javascript XSLT (Client) function loadXMLDoc(fname) { var xmlDoc; // code for IE if (window.ActiveXObject) { xmlDoc=new ActiveXObject('Msxml2.DOMDocument.3.0'); } // code for Mozilla, Firefox, Opera, etc. else if (document.implementation && document.implementation.createDocument) { xmlDoc=document.implementation.createDocument("","",null); } else { alert('Your browser cannot handle this script'); } xmlDoc.async=false; xmlDoc.load(fname); return(xmlDoc); } function displayResult() { xml=loadXMLDoc("_layouts/listfeed.aspx?List=%7BCC60DDDA%2DB57A%2D4D91%2D9C98%2D19C971C04C7E%7D"); xsl=loadXMLDoc("doc/bar.xsl"); // code for IE if (window.ActiveXObject) { ex=xml.transformNode(xsl); document.getElementById("example").innerHTML=ex; } // code for Mozilla, Firefox, Opera, etc. else if (document.implementation && document.implementation.createDocument) { xsltProcessor=new XSLTProcessor(); xsltProcessor.importStylesheet(xsl); resultDocument = xsltProcessor.transformToFragment(xml,document); document.getElementById("example").appendChild(resultDocument); } } _spBodyOnLoadFunctionNames.push("displayResult"); </script> <div id="example"> </div>
Next, create a stylesheet and save in a document library in SharePoint. I called my document library doc and save my xsl as bar.xsl
Doc/bar.xsl
<?xml version="1.0" encoding="ISO-8859-1"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <img> <xsl:attribute name = "src"> <xsl:text>http://chart.apis.google.com/chart?chs=500x300&cht=bvg&chbh=a&chco=4D89D9,C6D9FD&chdl=Actuals|Budgets&chxt=x,y&chd=t:</xsl:text> <xsl:for-each select="rss/channel/item"> <xsl:sort data-type="number" order="ascending" select="substring-before(substring-after(description, 'ID_Order:</b>'), '</div>') "/> <xsl:value-of select="substring-before(substring-after(description, 'Actuals:</b> '), '</div>') "/> <xsl:if test="position()!=last()">,</xsl:if> </xsl:for-each>|<xsl:for-each select="rss/channel/item"> <xsl:sort data-type="number" order="ascending" select="substring-before(substring-after(description, 'ID_Order:</b>'), '</div>') "/> <xsl:value-of select="substring-before(substring-after(description, 'Budgets:</b> '), '</div>') "/> <xsl:if test="position()!=last()">,</xsl:if> </xsl:for-each> <xsl:text>&chl=</xsl:text> <xsl:for-each select="rss/channel/item"> <xsl:sort data-type="number" order="ascending" select="substring-before(substring-after(description, 'ID_Order:</b>'), '</div>') "/> <xsl:value-of select="title"/> <xsl:if test="position()!=last()">|</xsl:if> </xsl:for-each> </xsl:attribute> </img> </xsl:template> </xsl:stylesheet>
For my case, I needed an ordering to my graphs since they are all months. So I added an extra column in my list called ID_Order so it can be sorted as per liking. The caveat here is that the RSS Feed returns the data without any order. You could also use XSL to sort it by the ID order when the data is input.
As another example of using the Google Charting API, you can use the same SharePoint list to create a pie chart of a single column in the list.
Doc/pie.xsl
<?xml version="1.0" encoding="ISO-8859-1"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <img> <xsl:attribute name = "src"> <xsl:text>http://chart.apis.google.com/chart?cht=p&chs=350x300&chtt=Actuals&chco=ff0000&chd=t:</xsl:text> <xsl:for-each select="rss/channel/item"> <xsl:sort select="substring-before(substring-after(description, 'ID_Order:</b>'), '</div>') "/> <xsl:value-of select="substring-before(substring-after(description, 'Actuals:</b> '), '</div>') "/> <xsl:if test="position()!=last()">,</xsl:if> </xsl:for-each>|<xsl:for-each select="rss/channel/item"> <xsl:sort select="substring-before(substring-after(description, 'ID_Order:</b>'), '</div>') "/> <xsl:value-of select="substring-before(substring-after(description, 'Budgets:</b> '), '</div>') "/> <xsl:if test="position()!=last()">,</xsl:if> </xsl:for-each> <xsl:text>&chl=</xsl:text> <xsl:for-each select="rss/channel/item"> <xsl:sort select="substring-before(substring-after(description, 'ID_Order:</b>'), '</div>') "/> <xsl:value-of select="title"/> <xsl:if test="position()!=last()">|</xsl:if> </xsl:for-each> </xsl:attribute> </img> </xsl:template> </xsl:stylesheet>
Final Output of Bar and Pie Chart from RSS Feed
Guest Author: Mind Fung Yong
Ming just started dabbling in Sharepoint and tries his best not to crash the test server =)
He is a Project Systems Knowledge Leader at a leading engineering consultancy in Melbourne, Australia, doing all things systems related, from cost control, planning to mobilization.
Apart from Ming’s more than usual spur of geekiness, his greatest passion is to align systems with business and people, as a means to an end, not an end in itself.
Nice work Ming. I can see these charting solutions growing arms and legs (literally, within the last couple of days or so!)
Both this and Claudio’s charting options are a huge addition to the interface. I can see great potential for this solution, as Mark points out, the list need not necessarily lie in the site collection the graph is on.
Nice… for IE.
Nicer still if it would work for Firefox and Chrome. Then again, this is SharePoint and it has its own issues with Firefox and Chrome. (Let’s see how SP2 resolves those…)
My question is related to data security when you call the Google Charting API.
I am working for a corportaion now and I want to make sure the data is kept secure.
Thanks for your comments and for sharing this… great solution.
Ernesto – I would not consider the data to be secure in this transaction. The Google API is not running under https, so it will be unencrypted as it is passed over the network. Plus, an image is being generated and we are not told if this image is stored anywhere before it is passed back to your server. — Mark
Well it could work for Firefox if you don’t use Microsoft XML parser. You could check which browser is running and use the appropriate parser. For Firefox, ie.
xmlDoc=document.implementation.createDocument("","",null) xmlDoc.async="false" xmlDoc.load("rss feed")As for data security, no https as yet and google API FAQ states that “The chart data included in the HTTP request is saved in temporary logs for no longer than two weeks for internal testing and debugging purposes.”
-Ming.
Ming – Would appreciate an update of the script that checks for browser and then loads the data appropriately… assuming you have time, of course! :-)
Mark
This is really cool, Ming! I know nothing about XSL, but your solution shows it can be really powerful.
Thank you for sharing it!
Claudio
I think I will it will be really cool if people starts contributing their XSL and have a library to share for different charts.
Ie.
Line chart.xsl
Radar chart.xsl
Scatter plot.xsl
Venn diagram.xsl
etc.. whatever’s in Google’s API..
oh btw, XSL is actually quite powerful. if i’m not mistaken, you can do calculations like sum and average etc. but i have yet to test them. so you could potentially group and sum your data.
I did a lot of work with XSL/XSLT when I wrote my VoiceXML book. Yes, it is a VERY powerful language that can be used to transform pretty much anything XML related. — Mark
I am getting following error
RSS FEED for Test: Chart
With Really Simple Syndication (RSS) it’s easy to track changes to important lists and libraries. If you have an RSS reader, simply subscribe to this RSS feed, and your reader will record the changes for you. You can also browse the RSS feed here in your browser.
Table of Contents
After that Page can not be displayed
Hi Atish,
Can you see the RSS feed in the first place? Is this the default RSS generated by Windows SharePoint Services V3 RSS Generator? If you were going to use it for some other RSS feed (apart from WSS’ default), you’ll probably need to make some minor changes to the XSL to suit your RSS feed. You’ll need to parse it correctly. -Ming.
Could you provide some more details on how you actually created the XSL file? It looks like it could be a really powerful tool, but I can’t quite figure out how you generated the XSL file or how to reverse engineer it to apply it to my own situation. ;)
It took some time, but after reviewing the google chart API website and then staring at your code for half an hour I finally figured it out. It would be great if this was a little bit easier for our page designers to use, and the data concerns are still there, but it could probably even be built into a webpart with configurable parameters to make it easier for our end users to use… but if we are going that far it might make sense to just grab some webparts from a provider like Bamboo Solutions. Or for that matter, it might even make sense to move to SRSS / PerformancePoint if it is really going to get used that much!
Ming: have you considered using the OOTB RSS Web Part (MOSS) or XML Web Part for this?
Christope – A problem with the OOTB web part for RSS is that it can’t read authenticated feeds. With that limitation, if a site is running under https, the web part can’t read any RSS content from the site itself. — Mark
Good point Mark. But I was thinking that this method was more suitable for non secure environments, as anyway you send the data out on the Internet to get the charts.
I did try using XML Web Part in the beginning but just as Mark mentioned, my environment is in https so it didn’t work. I guess the rest can still be secured apart from the part which data is sent out to grab the charts.
On a side note, I tried using a Mozilla parser but it somehow refuse to work in a Sharepoint environment though the parser works fine outside of Sharepoint.
Spoke a bit too soon =) Here’s the code that works for both IE and Firefox. I’d only tested it on IE6 and Firefox 2…
Hi,
I have been unable to get his to chart. I have followed the instructions and replicated everything as per the example but no joy.
Any advice appreciated.
Hi Richard,
Can you elaborate more? Any sort of errors or anything? It’s hard to tell what the problem is from your description.
Ming,
I don’t get any charts after following the examples provided.
I have created a list similar to your own with two columns of data, captured the rss output, used a CEWP and pasted the supplied code in and changed the rss listfeed, created a doc library and xsl sheet exactly like yours (same doc library name and filename).
when i save the CEWP part i don’t get anything!
I have only tried using the bar chart, wanted to get that working before the pie chart.
Any help you can offer is appreciated.
I am using MOSS, but I assume it works all the same?
Well,
I actually have another hidden column called ID_Order. That’s just for me to order the charts in sequentially in months. If you copy the XSLT straight off, it prob won’t find ID_Order if there’s no such column? Not sure if that could be the problem =)
Very good work Ming. I needed a burndown chart on our scrum dashboard. This was the one and only solution for me, while I don’t have full control over our sharepoint server. Thanks for sharing it.
Glad that it has been useful.
Anyway, another tip that I’d learnt. If your numbers exceed a thousand, you might want to use column type as single line of text instead of number. Reason that when it is type number it returns a comma after a thousand, ie. 1,000 instead of 1000. This could potentially confused google chart with an extra variable. And you might want to do scaling with you have big numbers. The parameters for scaling google charts are “chds” and “chxr”
could this same code be used with a sharepoint list that has been converted to xslt?
chiqnlips: yes, it’ll work, but you’ll need to adapt the code, as the field names in a list output and in a RSS output are different.
It will also work with Web services or the URL protocol.
Hi Ming
Fantastic article and just what i have been looking for.
However i am new to this and require a little help if possible.
I cannot work out what columns you have in your doc library from the code is it description, budgets, actuals and ID_Order?
Also i know this is very basic but how would i form the URL for to point to the .xsl on another site collection (doc/bar.xsl = http://test.com/doclibrary/bar.xsl)??
Thanks in advanced and keep up the good work
I too am having trouble with recreating the project Ming described. I included the columns as well as the hidden ID_Order column. Created my CEWP and introduced the source code but when this is all done I’m getting an error:
“Switch from current encoding to specified encoding not supported. Error processing resource ‘http://dsitsharepoint/SiteDire…
<xsl:stylesheet version=”1.0″
——————————————-^
”
I have a list that was created to include the hidden ID_Order column, I have the CEWP updated with the coding provided, I updated the java script to reflect the ID given from my RSS feed and still no chart display.
Should the CEWP be on the same page as the Library or List?
Are there any other changes to the code that needed to have taken place?
What could I be missing at this point?
Any suggestions would be greatly appreciated.
TMac
Steven&TMac,
The columns I have are Month,Actuals,Budgets & ID_Order.
I have yet to try cross-site xsl.
So all my stuff is under the same site.
Ie.
/Subsite/default.aspx – main page
/Subsite/doc/bar.xsl – XSL
/Subsite/Lists/Report/AllItems.aspx – List
Hope that helps.
Ming.
Ming,
I am anxious to get your application working for mysel, but haven’t gotten it yet.
In the bar.xsl file code, the last loop, the line , is this a column in the Report list, or is this assigning the ID_Order number or ?
The other loops get the value-of by a string function; this one is quite different.
Thanks in advance to your help or anyones help.
Hi Ron,
It’s been a while since I last looked at this but if my memory serve me well, the last loop should be looping through the different months, which will be sent as labels for the x-axis of the graph.
Ming
Authenticated Access Work-Around
I could not get the admin to enable Anonymous Read Access, as believed to be the problem with not being able to access authenticated feeds.
I discovered a work around, using SharePoint Designer.
The key is that SharePoint Designer has in the Data Source Library, under Server-side Scripts a “Connect to a script or RSS Feed” that has four different options to configure authentication. I was able to get the “Save this username and password in the data connection” option to work. Here are the steps:
Open the SP site you want to place the chart in SharePoint Designer
In the “Data Source Library”, under “Server-side Scripts” (you may need to expand this item) click on “Connect to a script or RSS Feed…”, this opens up a “Data Source Properties” window
In the General tab, create a “Name” for your RSS feed
In the Source tab, HTTP method should already be “HTTP Get” with data command “Select”; then paste the URL (from the RSS view of your source data list) in “Select Connection Info, Enter the URL to a server-side script:”
In the Login tab select “Save this username and password in the data connection” and enter a valid User name and Password in the entry boxes (note: I tried the option “Use Windows authentication” which would be better, but it didn’t work for me; also I was working on the network the same as the server so my User name did not need the trailer @zzz.com)
Click “OK”
The name for your data source should show up under the Server-side Scripts, from its pull down menu select “Show Data” (if don’t get the rss folder, there is still a problem getting the RSS feed)
Scroll down to “Item” and select “title” and while holding “Ctrl” select “description so that only those two items are selected (“link” should not be selected)
In the pull down menu for “Insert Selected Fields as …” click on “Multiple Item View” which will create and place the RSS data in the preview window
Place you cursor over the “WebPartPages:DataFormWebPart” to click hold and drag it to one of the conventional columns of the other web parts of your site
At the top most tool bar under “File” click “Save” and then “Exit” SharePoint Designer
Refresh your original site (you should see the RSS feed data)
Under “Site Actions” click “Edit Page”
In your RSS feed data web part, under edit, select “Modify Shared Web Part”
Click on the “XSL Editor” and replace the script with the bar.xsl (or the other one for the pie chart) provided by Ming and click “Save”
Click “OK” (the chart should appear) then “Exit Edit Mode”
Note the web part does not have a title or a pull down menu but that can be fixed back in the “Web Part Editor” under “Appearance”, “Chrome Type” change from “None”.
One thing to keep in mind, when others go to this site, they will be viewing this web part with the RSS feed via your login and password. Should you change your password, the RSS feed will not be available until you go back into SharePoint Designer and change the Properties of your named scripted RSS feed.
If anybody has any insight as to why the “Use Windows Authentication” option doesn’t work for this SharePoint Designer scripted RSS feed, I’d sure like to hear from you or see an additional reply posted.
Ron K
I’ve been trying to reverse engineer this code to work with one of our lists. I created a RSS feed for our list and inserted it into the CEWP webpart code in place of your RSS info. I also changed the name of the document to match our own. I created a XSL document using the same format although I’m substituting your ID_Order with just the ID field and then swapping Actuals with a PercentComplete field and Budgets with a Status field. Nothing will display in the webpart. Any ideas on what might be holding us back?
Could you please reload the javascript for this article. I love this site it has really helped me out.
Thanks,
Jerad
The code in the article got completely wiped out. Ming, if you’re watching, please send me a new copy. Thanks. — Mark
Thanks Ming. The article is updated with the code samples. — Mark
Ming-
I replicated your list with the four columns (Month, Actuals, Budgets, ID_Order) from Jan through Apr and with associated ID_Order values of 1 through 4. I made the RSS settings tweaks. I created the xsl document without updates. I created the content editor webpart with your code and updated the pointer to the RSS feed and the pointer to the xsl document. The list, webpart page, and xsl file are all in the same site. I receive the following error when loading the webpart page in IE. Any advice to resolve the error?
Message: The stylesheet does not contain a document element. The stylesheet may be empty, or it may not be a well-formed XML document.
Line: 1039
Char: 3
Code: 0
Thank you,
Sean
Hi,
i looke at your article and i wanna replicate the same thing on our sharepoint site.
i am following the above instructions but only the axis is being shown , no data is shown in the web part.
can you please help!!!!
thanks.
Do you mean there is no bar graphs? Does your RSS feed has the data?
For those that are having problems getting the chart to display data, I would first run a stylesheet script that dumps the raw RSS feed to make sure the data is being brought in to the SharePoint Web Part and is in the anticipated formate for the substring manipulation of the rest of the code.
My initial problem was getting the SharePoint OS settigs correct to deliver the RSS feed, then the rest of the XSL stylesheet code that filters the entries should run as in the earlier examples. Below is a simple XSL script to to the RSS dump.
Simple Item Dump
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:exsl="http://exslt.org/common" extension-element-prefixes="exsl"> <xsl:output method="html"/> <xsl:template match="/"> <!-- <xsl:variable name="Rows" select="/dsQueryResponse/rss/channel/item"/> --><!-- location of merged RSS feed items --> <xsl:variable name="Rows" select="rss/channel/item"/> <!-- location of single RSS feed items --> <html> <body> <basefont size="2"/> <p> <textarea rows="32" cols="80"> <!-- Keep the web part size within reason --> <xsl:for-each select="$Rows"> <xsl:value-of select="."/> </xsl:for-each> </textarea> </p> </body> </html> </xsl:template> </xsl:stylesheet>Note, this will dump the whole RSS item, one after another. Ming’s script works with the “description” field of the SharePoint’s RSS which contains the column information (no entry IDs). If you want to use the IDs, don’t use “description”, use “.” and the proper substring filtering to select the IDs. The dump from the script above will show the RSS item IDs.
I have updated Ron’s comment with a marked up XSL snippet. In additions, Ron sent this extended comment:
“This whole charting using the Goggle Chart API with RSS feeds in SharePoint I have been using extensively. I have found using SharePoint Designer that the RSS feeds from mutiple can be merged and applied to this charting application.
“I even use this RSS feed merging discovery to create simple HTML tables that provide me better “Roll Up” web parts better than third party Roll Up applications (more flexible because I have the latitude to format the table per HTML parameters the way I want (I can add summations to the columns of the merged data, and in those that view the finished table in SharePoint, they can copy it to Word, PowerPoint where it maintains the table layout formatting information and they can modify the rows and columns to fit their document needs).
“It has been quite the discovery process for me to realize just how much one can do with SharePoint RSS feeds.”