Trend Reporting Against a SharePoint List

A programme manager I work with wanted to view trend information regarding the projects in his portfolio. His problem was that weekly reports were all well and good but with around a dozen projects under his wing he could not readily visualise where projected milestone delivery dates were, in general, getting worse or better.
To meet this requirement I knew that we had to create a 'snapshot' record on a regular basis, which would be completed by individual project managers. Some information would need to be recorded each week, with one variable field – the Forecast Date.
- Reporting Date [Created]
- Milestone [Title]
- Baseline Date
- Forecast Date
The next step was to add a calculated field to display the number of days between the Baseline Date and the Forecast Date
- Milestone Slip =[Forecast Date]-[Baseline Date] – this is used to display days slipped

We now have the basics of a slip report – the more entries in this list, the more we can see if that Milestone Slip figure is increasing or decreasing over time.
We are way off though on the visual aspect! I thought that as a list is made up of a number of horizontal rows we could fake a horizontal bar graph by adding colour to an html element that has it's width set to the value of a calculated field. Then as the list stacks up entries we could see that bar changing size over time.
I chose to create a Data Form Web Part to display my list (but I guess you could use Christophe's method of displaying HTML in a Calculated Column) which I did within a new (temporary) Web Part Page that I created in the Document Library on my site.
I opened that page in SPD and added a data view of the list, then using the Common Data View Tasks menu reordered, renamed and grouped the columns. I also reformatted the date fields to remove the time and display in DD-MMM-YY format.
The first cut came in as follows:

The code was:
and I amended it to create a span with a red background the same width in pixels as the actual days slipped. Not very good if you only slipped a week or two.

I decided to make the span a percentage of the available width and did this by creating a new field in my list that was the days slipped divided by 100. I then used this, displayed as a percentage to drive the width of the span. I also decided that I wanted the count of the days to be shown on the right of the span.
This was getting there but I wanted to show the bar as green when the slip was slight and red when it was more significant. I did this by cheating! I created a background graphic in Photoshop that was a nice Green Amber Red gradient then used that as a fixed, non tiling background for the span.


Last step was to export that web part from my temporary web part page and import it onto the homepage for the site, which worked fine.
The intention was for this site to be used as a template for new projects and I ran into problems with this web part when a new site was created from the template: the data view binds itself to the list GUID of the original data source. When a new site is created a list with the same name is created but the GUIDs of course change and the web part busts.
The solution was to add curly braces around the list GUID inside my web part wherever it is mentioned i.e.:
From:
ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="0FE9F3E3-4F18-4324-8B06-4F1387E66464"
To:
ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{0FE9F3E3-4F18-4324-8B06-4F1387E66464}"
SharePoint then replaced the GUID of the old list with the GUID of the newly created list when the site was created.
On reflection there are some data issues you may run into with big slippages but that is a solution design flaw not a process flaw, but this was my first attempt at visualising data in SharePoint and has stood me in good stead with similar problems since.
Ben Schlaepfer
Owner, principal consultant and tea-boy
Mode2 Ltd
http://www.mode2.com
After setting the background colour of my first html table 12 years ago I’ve been obsessed ever since with presentation on the web. From Creating Killer Websites to Designing with Web Standards I’ve enjoyed watching online user experience evolve and change.
I’ve been specialising in SharePoint for the last 2 years and am always looking for and learning new ways, from sites just like this, to help people get their information across with the style and impact it deserves. The biggest challenge I face is balancing my background in fairly strict Usability and Accessibility disciplines with my new found love of SharePoint…
Awesome article, Ben. Very informative and easy to read. Thanks for providing the steps to get to the final solution.
Hi Ben,
Yes, this can also be done without SharePoint Designer, using my “HTML calculated column” method. So I’m going to steal your nice Photoshop picture, thanks ;-)
Christophe
Ben, I started using your article and set up an example here:
http://8002.freesharepoint2007.com/Lists/TrendReporting/AllItems.aspx
A major issue I see is that the rendering depends on the list width, when based on a %. If you resize the window in my example, you’ll see that the look of the last column changes.
Christophe
Christophe – Ben has provided us with a very serviceable version that will get people thinking. I don’t see this as a ‘major’ issue.
Very few people are going to collapse their brower to the point where the red disappears in the graphic.
I look forward to your solution that doesn’t use SPD.
Mark
Hi Mark,
first, I agree that I went to far by calling this a major issue! And, as you say, it’s a startpoint to get people thinking – including me.
Just one thing though: I suggested window resizing as a quick test. In a real life scenario, the list may be displayed in several pages – its own default page (dispform.aspx), the home page, a dashboard, etc. In such case the rendering will be different, even if the user doesn’t resize the window.
For my example I’ll apply all of Ben’s ideas, except that I’ll force a fixed width.
btw in my opinion two tips mentioned in Ben’s article would deserve more visibility and more explanations:
1/ the use of a temporary Web Part page
For people who are not familiar with this, I recommend these two references:
http://www.sharepoint-tips.com/2007/05/sharepoint-designer-article-2-creating.html
http://pathtosharepoint.wordpress.com/2008/08/24/your-first-data-view-web-part/
2/ the GUID manipulation to allow the template creation.
I have never seen 2/ mentioned before, and I haven’t tried it either. If this actually works as a generic solution, I would nominate it for the “SharePoint tip of the year” awards!
It’s great to see this discussion develop … I’m honoured to have Christophe and Mark interested in the concept I’ve tried to convey!
I’ve been behind the curve a little on the HTML calculated column and now that I’m catching up I realise it’s a fantastic solution – especially on the dispform.aspx front. Very adaptable and easily teachable. I’m having fun right now integrating it with the Google charts API to create on-the-fly charts from a list item.
A problem I have with some of my dashboards though is working with counts of items “What percentage of Issues in a list are Red / Green / Amber” … and display them accordingly. As far as I can see that can’t be done with the calculated column? I.e. the HTML calculated column works on values in a single list item?
I’ve been tweaking the fantastic forty tutorials to achieve this. I’ll put together a demo over the holidays and submit it here.
Cheers
Ben
Ben, I have sent to Mark my article that adapts your example to the “HTML calculated column”.
I actually experimented with a similar approach in the past, using the OWC components, but your photoshop work is way cooler!
The “HTML calculated column” method is limited by the possibilities of the calculated column, for example for cross-row calculations. In this case, the DVWP comes to the rescue, but don’t dismiss other options, like the URL protocol (see my blog) or client side jQuery (see Waldek Mastykarz’s blog). I am also working on a secret weapon for next year ;-).
Christophe,
Look forward to those articles.
As for the GUID and {curly braces} – yes, it works. But don’t take my word for it … give it a go – you’ll be pleasantly surprised. I tend to do this editing on the raw .webpart file in notepad prior to importing it into my destination page.
I’m going to put together a short post on why I start with a temporaty web part page for all my DVWP work (but you’ll guess it’s to do with avoiding customisation of core pages).
Ben
Hi Ben,
The temporary Web part is already explained in the links I provided above.
As for the curly braces, I haven’t found any reference to this elsewhere. I’ll give it a try, and if it is confirmed, it means that your method addresses a key issue of DVWP.
Christophe
this was a great post, with much feedback. The comment that this would get people thinking was very true. When I first implemented this I came across the issue of negitive dates. my requirements were to show when tasks came in under and over target dates. with some modifications I figured out to resolve this, but then my problem was trying to see the difference between these 2 types, besides the “-”. whith some more modification I have offset one, so the teo overlap in the middle. if over task date, image red and to the right, if under image green and to the left.
——-21
45————–
here is the calculted formula I ended up with:
=”=0,”‘>”&ROUND(Modified-[Publish Date],0),”text-align:right;’>”&ROUND(ABS(Modified-[Publish Date]),0))&”"
if anyone can come up with a cleaner or simpler code I am open to feedback.
[DIV style='width:250px;"&IF(ROUND(Modified-[Publish Date],0)>=0,”‘>”&ROUND(Modified-[Publish Date],0),”text-align:right;’>”&ROUND(ABS(Modified-[Publish Date]),0))&”</DIV]
last try
[DIV style='width:250px;"&IF(ROUND(Modified-[Publish Date],0)>=0,”‘][DIV style='margin-left:122px;background: red url(/sites/learning/SiteImages/traffic_bg.jpg) no-repeat; font-weight:bold; text-align:left; width:"&ROUND(Modified-[Publish Date],0)&”%;’]”&ROUND(Modified-[Publish Date],0),”text-align:right;’][DIV style='margin-right:122px;background: green url(/sites/learning/SiteImages/traffic_bg_grn.jpg) no-repeat right; font-weight:bold; text-align:right; width:"&ROUND(ABS(Modified-[Publish Date]),0)&”%;’]”&ROUND(ABS(Modified-[Publish Date]),0))&”[/DIV][/DIV]