You are currently browsing the monthly archive for November 2009.
For details on the upcoming Sparklines online workshop, see the end of this article, or click here.
Edward Tufte, who coined the term “sparklines”, describes them as “data-intense, design-simple, word-sized graphics.”
While traditional charts aggregate information from a full list, and are set off the flow of text, a sparkline pictures an individual item, and is directly embedded in the list.
You’ll find a good introduction to sparklines on this page from BonaVista.
The most common type is a line graph (hence the name), like in this screenshot from Google Finance. But sparklines come in various other shapes, as you can see from the (jQuery) samples on this page.
For example, you’ll use:
- line and bar graphs to track data against time
- progress bar or bullet chart to assess data against thresholds or objectives
- pies for categories (e.g. region, product line)
- discrete charts for status (project phases, game results)
Sparklines apply to many situations:
- enterprise dashboards (revenue, expenses, market share, all within one list)
- healthcare (patient stats)
- education (attendance rate, success rate)
- project management (issues resolution, costs)
- products/services dashboard (sales, support calls)
- transportation (passenger traffic, delayed flights)
- sports and games (won, lost or drawn)
- website stats: pageviews, visitors (cf. Google Analytics)
- baby name trends for a given year
- etc.
Sparklines can also be placed on supports other than lists, like maps, although I find this more difficult to read. Here is an example with the New-York subway.
How about SharePoint?
I already published last year a tutorial showing how to include Google sparklines in SharePoint lists. The Google approach is very convenient, but has its downside. First, in your corporate environment, you may not have access to the Google charts website. Then, even if you can access it, you may not like the idea that your data goes public, as you send it on the Internet to get the charts in return. If so, you’ll have to find an in-house solution to build your sparklines.
In a couple days, I’ll publish a tutorial to explain how to I created simple, homemade graphs for SharePoint lists (see screenshot below).
If you are wondering how I built the pie charts, check out this picture for a clue. More details after Thanksgiving…
To apply the tutorials, you’ll need to know about the “HTML Calculated Column”. For a refresher on this method, start with the post I published last week, and follow the links.
The above bar graphs and pies are basic solutions, but their great advantage is that they don’t have any external dependency.
If you are looking for more advanced charting capabilities, check out Gareth Watts’ sparklines plugin for jQuery. Back in March, I showed an example combining this plugin with SharePoint.
Inline charts are the central theme of our upcoming live online SharePoint workshop, scheduled on December 2nd at 1 pm (EST). Mark Miller and I will guide you through the steps to implement these customizations. Like for the other workshops, you’ll receive a SharePoint sandbox, where you can directly try out the solutions.
I am publishing today a minor update (v 2.1.1) of the TextToHTML script:
http://www.pathtosharepoint.com/HTMLcc/default.aspx
You’ll find the updated files under the “download” tab.
Why this update?
First, I am responding to comments from readers on the EndUserSharePoint.com blog. Several months ago, Jim Bob Howard posted an example using my method. His post offers detailed step by step explanations, and is very popular. However, a few readers reported issues with the calendar week/day views, which didn’t render the colors in some configurations. The code I posted today aims at fixing this issue.
I am also taking the opportunity of this update to propose two separate scripts:
- a light version, for both SharePoint 2003 and 2007, that applies the rendering to displayed items only.
- a full version, SP 2007 only, that applies the rendering to displayed and hidden items (grouped views, preview pane, etc.). Be aware that this version encapsulates some of the SharePoint OOTB code.
Tell me again, what is this “HTML Calculated Column”?
If you are an end user, the HTML Calculated Column is a simple yet powerful method that allows you to enhance your SharePoint sites. Even if you don’t plan to use it, you should at least be aware of its capabilities. It many situations, it can replace or even do better than other SharePoint options, like the Data View Web Part, Excel Services or the KPIs available in MOSS. To give you an idea of what you can accomplish, take a look at these examples:
- indicators for lists
- color coded calendars
- KPI roll-up
- some other applications…
For more explanations, read the original article.
To be fair, the method also has its flaws. It leverages the OOTB calculated column, but also relies on a client side script to render the HTML on the page. If you don’t feel comfortable with the “hack” part (TextToHTML script), note that you can avoid it if you use the method with a Data View Web Part, a Content Query Web Part (see above link: KPI roll-up), or an XML source (RSS, URL protocol, Web Service).
Why two different file formats, .dwp and .txt?
I am releasing the code in two formats: Web Part (.dwp) and text (.txt).
The .dwp file is for your convenience, if you only use the method sporadically, or for the first time. Simply import the Web Part to your page and you’re good to go. It is just a Content Editor Web Part containing the script.
If you plan to use the method on a regular basis, you should grab the text file instead: store the code in a central location and point to it. This will greatly facilitate future updates. For more details on this “best practice”, read this article.
A couple reminders
- Remember that the Text to HTML script must be placed below the lists you need to modify.
- The HTML you create via the calculated column must follow the HTML standards. For example for calendar views you’ll need to use inline elements (“span” for example), not block elements (like “div”).
What’s next?
I am currently working on version 3 of the script, which will make the method work on column filters, and headers of grouped views. Some people have already received intermediary versions, and I am waiting for their feedback.
I am also listening to feedback on the current version (big thanks to the readers who shared their findings!). For example, the use of the method in discussion lists, blogs or wikis will require more investigations.
How can I be informed of upcoming releases?
The preferred way is to subscribe to the RSS feeds, either for this blog or for my SharePoint downloads library. If you don’t use RSS, feel free to contact me, and I’ll send you an e-mail when v3 is out.
We started with the HTML calculated column, then we later extended the technique to JavaScript. Today is the turn of the jQuery calculated column!
To demonstrate this technique, I have included animated countdowns in a SharePoint list:
http://www.pathtosharepoint.com/Calendars/Pages/Workshops.aspx
The list displays two countdowns, one based on Flash (from clocklink.com) and the other using the jQuery countdown plugin by Keith Wood.
The Flash countdown uses an HTML calculated column that contains an “embed” tag (the HTML can be found on the clocklink site). The jQuery countdown is a also a calculated column, which calls the jQuery countdown function.
Now, take a closer look at the demo: do you see a difference between the values displayed by the two countdowns? Can you explain why? Here is a clue: if you live in New-York, you should see the same time on both countdowns.
As usual feel free to use the comments section to ask for more details!
“jQuery animations in SharePoint” is a live, online workshop scheduled on November 17th at 1pm EST. We’ll talk about date and time in SharePoint, and each participant will receive a sandbox and step by step instructions to set up countdowns. The other animations featured in the workshop are scrolling news and image rotator, also based on jQuery plugins. For details and to register, click here.
On November 17, Mark Miller and I will host our live online workshop dedicated to animations in SharePoint.
The main featured customizations will be scrolling news and the image rotator. To give you a feel for what you’ll be playing with, I have created a live demo:
http://www.pathtosharepoint.com/Pages/Animations.aspx
[This is a mockup, I am still working on the final scripts for the workshop]
The engines behind these customizations are two highly acclaimed jQuery plugins: Scrollable and Cycle. There are also a couple others working behind the scene, like the Easing plugin (which creates the optional bouncing effect in the image rotator).
An important note: the focus of the workshop is the SharePoint UI, not jQuery. We’ll learn how to pull the content from SharePoint lists, and format it to feed the plugins. We’ll also see how to optimize the display of images to reduce bandwidth.
If you like what you see, feel free to use the comments section below, to give me feedback and ask for more details. We are very flexible, and we’ll adapt the workshop content to your needs!
The live online workshop is scheduled on November 17 at 1:00 PM (EST). I hope to see you there!
If you need dynamically generated visualizations for your SharePoint data, have you considered leveraging the power of the Calculated Column? This Tuesday at 1:00pm EST, Mark Miller and I will give you all the keys to master this simple yet powerful technique. At the end of the two hour entry level workshop, you’ll be able to add color coding, KPIs and other effects – like the one described in this post – to your SharePoint lists.
Green/Yellow/Red is a standard color palette for dashboards. You can just use 3 colors to visualize discrete states, for example the status of a project (on track – drifting - late). But if your purpose is to communicate progress, or a measure on a scale, you need a larger color palette. This is for example the case in my screenshot, where the color reflects the level of completion (in %), in a tasks list.
So, how can I do this in SharePoint? Of course, my plan is to use a calculated column that will determine the color, based on the value in the [% Completed] column.
Method 1: nested IFs
This is the most basic approach:
if [% Completed]>90, select green
else if [% Completed]>80, etc.
I am not going to detail it, as we can do much better.
Method 2: CHOOSE function
The CHOOSE function is more elegant than nested IFs, and is a natural choice when dealing with multiple options. You’ll find all the explanations to achieve a color gradient in this post.
Method 3: pure calculation
So, how can we go even further? Well, colors can be identified by their name, but also by their rgb code, as each color can be generated from a combination of red, green and blue. For example:
red: rgb(255,0,0)
green: rgb(0,255,0)
blue: rgb(0,0,255)
yellow: rgb(255,255,0)
white: rgb(255,255,255)
Using these values, we can “easily” create our red/yellow/green gradient:
rgb(255,0,0) –> rgb(255,255,0) –> rgb(0,255,0)
The following formula, entered in a calculated column, will give you the rgb value for each value of the [% Completed] column:
=”rgb(“&INT(MIN(510-[% Complete]*255*2,255))&”,”&INT(MIN([% Complete]*255*2,255))&”,0)”
To obtain the visual effect as in the screenshot, use the HTML Calculated Column method, with the following formula:
="<span style='display:inline-block;position:relative; width:60px; height:14px;border:1px solid;'><span style='display:inline-block;position:relative;background-color:rgb("&INT(MIN(510-[% Complete]*255*2,255))&","&INT(MIN([% Complete]*255*2,255))&",0); width:"&([% Complete]*100)&"%;height:14px;'><span style='position:absolute; top:0px;'> "&TEXT([% Complete],"0%")&"</span></span></span>"
Note that the method works in both SharePoint 2003 and 2007.
SocialVibe