Tag Archives: Microsoft (Office) SharePoint

Creating a dashboard using a SharePoint list (part 4): bringing it all together

This week has been a bit of a SharePoint-fest as I’ve walked through the stages of creating my SharePoint dashboard. For obvious reasons, I can’t share the version I use at work, so I’ve created a mockup using the SharePoint site that comes with my Office 365 subscription. Feel free to take a look…

The three posts that explain how I built this dashboard can be found below:

As the code examples I gave use quite a lot of SharePoint formulas for calculated values, I thought it might also help to link to Microsoft’s formulas and functions documentation as well as their examples of common SharePoint formulas.

Right, that’s enough SharePoint for now… next week will be back to the usual mix of tech, marketing, photography and whatever else I stumble across…

Creating a dashboard using a SharePoint list (part 3): indicating several states of progress

Yesterday’s post looked at using ticks/crosses on my SharePoint dashboard to track progress on activities. I was quite happy with this, but my manager wanted to show progress, rather than a simple binary done/not done – and I wanted it to be visual, not text-based.

We settled on a system using a series of circles with various stages of “filling-in” (none, quarter, half, three-quarters, full) to show how far an activity had progressed, and I amended the code that I used for the ticks and crosses.

Using the same principle as with the ticks and crosses, this time the font changed from Wingdings to Arial Unicode MS, which includes the appropriate shapes under the block elements and geometric shapes subrange:

First of all, I changed my column to indicate progress from a Yes/No to a Choice with radio buttons for Not Started; Started; Additional Information Required; Almost Complete; Complete and Not Applicable.

Next I needed to adapt the formula used to calculate the correct HTML code for the display column by nesting repeated iterations of

IF([Task Progress]="Started","<DIV style='font-family:Arial Unicode MS; font-size:24px;'>?</DIV>","<DIV style='font-family:Arial Unicode MS; font-size:24px;'>?</DIV>")

Eventually this ended up as:

=IF([Task Progress]="Complete","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Started","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Additional Information Required","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Almost Complete","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Not Applicable","<DIV style='font-family:Arial; font-size:36px; color:gray;'> </DIV>","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>")))))

The code is not pretty, but it seems to work, and the result is something like this:

Creating a dashboard using a SharePoint list (part 2): tick/cross status indicators

In my last post, I wrote about how I used a script I found on the Internet and a couple of calculated columns to show a Red/Amber/Green (RAG) status on a list in SharePoint. I also wanted to clearly indicate which items in my list were complete, and which were still ongoing.

My inspiration for this came from Chris Bell’s posts on displaying tick marks in HTML, combined with the same code that converts my text RAG Status to HTML.

This time, I converted a Yes/No Checkbox (actually, it’s TRUE/FALSE) to HTML code for a tick or a cross in another column, using the following formula:

=IF([Task Completed]=TRUE,"<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:green'>ü</DIV>","<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:gray;'>û</DIV>")

The end result is something like this:

It needs the Wingdings font to be installed, but everyone who needs to access this list is using a Windows PC – feels like a bit of a fudge but it works for now… and can always be replaced with a graphic (as I did for the KPI indicators on the RAG status).

Creating a dashboard using a SharePoint list (part 1): colour coded status indicators

I’ve been having some fun over the last couple of weeks, developing a dashboard in SharePoint to track a number of activities that I’m co-ordinating.

Within our company there are some people who criticise SharePoint as a platform, largely because of some of the confusing messaging around some of the collaboration features and the way in which it gets mis-used as a document store (complete with folders!) but, whilst it can certainly be infuriating at times, I have worked on quite a few sites over the years where it’s struck me just how powerful the concept of a list is (I should know that really – I remember spending quite a bit of time discussing linked lists in my Computer Studies degree, but that was 20 years ago…).

I wanted to create a list with a red/amber/green status for each item – but I didn’t just want the words – I wanted colour to jump out of the page and say – this is the stuff that’s on track… and this isn’t…

Some calculated values and a script to generate HTML from text

A bit of digging around on the ‘net turned up a method that works for me – and the details are in Anjali Bharadwa’s post on colour coding custom lists and tasks:

Firstly, I  interrogate the contents of my text column (in my case this is called RAG Status, with pre-defined choices or Red, Amber and Green) and convert that to an HTML colour code in another column (called Color). The formula is  as follows:

=IF([RAG Status]="Red","red",IF([RAG Status]="Amber","orange","green"))

I created a page that includes a Web Part that displays a view on my list and used a hidden Content Editor Web Part below this, containing Anjali’s script (there’s an alternative script available from Christophe Humbert at PathToSharePoint but the download link wasn’t working on the day I needed it…), to read this and to generate HTML in another column with a calculated value (called RAG).  For SharePoint 2007, the script can be included within the HTML source for the webpart but, for SharePoint 2010, it’s necessary to link to a separate file (Adam Preston explains how to do this in his post on inserting JavaScript into a Content editor Web Part) in the This time, the formula is:

="<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:"&Color&";'>n</DIV>"

Using a particular font is risky (how do you know that everyone has it?) but in this case I could be sure that all of my audience would be running our corporate build with Windows and Internet Explorer. I later changed the formula to use a different symbol:

="<DIV style='font-family:Arial Unicode MS; font-size:36px; color:"&Color&";'>?</DIV>"

Sorting the RAG status

One annoyance with this approach was that the list was sorted based on my RAG Status column, but I wanted Red-Amber-Green and the alphabetical order was Amber-Green-Red. My fix for this was to change the RAG Status choices to include a number so they become: (1) Red; (2) Amber; and (3) Green.

I also tweaked the calculated value for the Color column:

=IF([RAG Status]="(1) Red","red",IF([RAG Status]="(2) Amber","orange","green"))

With this change in place, my column ordering works – and it gave me the opportunity to change the way I represent the status too…

Alternative visualisations

Path to SharePoint has some ideas for alternative visualisations for colour-coding (and a follow-up post). I particularly like the KPI indicator version, which is based on some of SharePoint’s standard images:


To use these, I changed the formula for my RAG column to:

="<DIV><IMG src='/_layouts/images/KPIDefault-"&(3-RIGHT(LEFT([RAG Status],2),1))&".gif' /></DIV>"

This change also means that the Color column is redundant, although I’ve left it in place in case I want to go back to it later.

What next

So that’s my RAG status sorted. In the next post, I’ll explain how I used this method to show ticks and crosses to indicate activity completeness.

Calculated value based on today’s date in a SharePoint column

One of my current projects involves developing an internal system to keep track of the technologies that are approved for use in our solutions.  As part of that, I’d like to set a flag on each entry so that it is regularly reviewed – for example every 90, or 180 days.

I had hoped to use a calculated column for this (i.e. today’s date plus x days) but it’s generally recognised that calculated fields only work on data entered by a user, and not from the system. Indeed, Microsoft’s own documentation for the TODAY function says:

“[…] You can use the TODAY function only as a default value; you cannot use it in a calculated column.”

Even so, I’ve had some success (with Microsoft Office SharePoint Server 2007) in setting a column type of Date and Time, in Date Only format, with a Calculated Value of =Today+90.  I’m not sure how/why this is working, given Microsoft’s statement above, but it may be worth considering.  Just be aware that your mileage may vary…

(Christophe Humbert has more information and another approach over at Path to SharePoint)

More SharePoint shenanigans

This week, I’ave mostly been working in SharePoint (those of a certain age may spot the reference to Jesse from the Fast Show?)

Earlier this month, I wrote a post with a few hints and tips I’d picked up whilst developing a site based on SharePoint.  Since then, I’ve come up against a few more barriers…

Item level permissions on document libraries

SharePoint allows administrators to set permissions on lists so that users can read and/or edit only their own items. Unfortunately, whilst that functionality is exposed in the user interface for lists, it’s not for document libraries (although it is in the object model). Basically, if you can write some code, you might be able to set the requisite permissions, but that’s beyond my abilities.  Thankfully, others have done the legwork, either in the form of a rough-and-ready utility like Matt Morse’s or Tim Larson’s Read/Write Security solution.  Either way I need to get the the changes applied to our SharePoint farm… which may well be more trouble than it’s worth…

Changing the contact for a page

Even though there is a page contact in the properties for each page (at least there is in my company – that might just be metadata that we use…), changing that property doesn’t seem to affect the page contact (which our templates show at the bottom of the page – and is also shown in a view of all pages).  The answer is to edit the page settings (in the same way as to change the page layout). Thanks to Ian Mitchell (@ianmitchell2) for setting me on the right path there…

A calculated column, with a formula based on a Yes/No field

I wanted to display a tick or a cross instead of a yes or a no in a view on one of my lists and, in order to do this I needed to create a calculated column that produced the necessary HTML (and a script to display it…). I’ll write another post about the fancy formatting but I really struggled to work out how the Yes/No is recorded (Yes/No; 1/0; TRUE/FALSE?). Exporting my list to Excel proved that SharePoint stores boolean values as TRUE/FALSE (confirmed by Peter Allen) but the trick is to leave out any quotes – if you look for =IF(Column="TRUE","This","That") it will always be negative and the outcome will be “That”.  The correct formula is =IF(Column=TRUE,"This","That").

Editing in data sheet view but the view is read-only?

I needed to perform some bulk updates on lists in SharePoint but, frustratingly, the view was marked as Read Only so I couldn’t make any edits.  I couldn’t see why this was, but googling turned up an explanation – the list was set to require content approval.  PointBeyond has more information on configuring approval in SharePoint but temporarily removing this setting allowed me to make the necessary updates, before re-enabling it.

Connecting data in web parts

Back in around 2003/2004, I remember attending a SharePoint training course where I connected a couple of webparts to work together.  For someone like myself with little or no coding skills, this was magical… and then I forgot how to do it.  Yesterday, I ran up against an issue where, partly as a result of some database design decisions by the previous  designer, I found myself unable to display the view on a list that I wanted to, as there was an implied hierarchy in the data, but the lower levels in the hierarchy link to their parent, rather than parents linking to children (it would be better still if things could work both ways…).

The workaround, albeit clunky, was to configure two webparts, each showing a view on a different list, before configuring a data connection so that the first list provided a row upon which to filter the second list.  I needed to be careful in selecting columns (i.e. the second list needs to have a column that is a lookup on the first) but, with that in place, I was able to at least show the relationships between the items in each list.

A collection of SharePoint shortcuts

I spent most of last Friday developing a business system with Microsoft Office SharePoint Server (2007).  I’ve worked on a few SharePoint sites over the years and I’m impressed at how much can be done using just standard functionality (lists, etc.) but, whilst the platform is powerful and flexible in many ways, it’s also intensely infuriating at times.

In developing this latest site, there were a few things that I had to Google for – and I’m hoping that posting them here might help others…

Changing the page layout

I created a new page using one of the templates provided for me by my IT department.  Unfortunately I found that the webpart layout was a little too restrictive and I needed to change the page layout.  I hunted around for a while (even after a colleague had told me to look for the page settings) and then I found a post by Shane Young that helped me out. As Shane descibes, the steps are:

  1. “Browse to the page
  2. Click Site Actions, Edit Page
  3. From the tool bar click Page
  4. In the drop down list click Page Settings
  5. Now pick your Page Layout
  6. Click OK”

With the new page layout in place I was able to get the page looking (almost) how I wanted.

Hiding the Title column from forms

My site is built around a document library with a number of columns. One of the default columns is called Title and it’s not really that useful to me as it really just duplicates the Name field (doubling up the details that users need to enter for a document in the library). I can always hide column from list views but I can’t delete it completely and the field still appears in forms. Sometimes, I repurpose Title by changing the column name but I can’t change the column type – it’s always a single line of text. Then I found John Owings’ post which describes the steps to hide the Title column from forms:

  1. “From the list view click Settings [then] List Settings
  2. On the Settings Screen, under the ‘General Settings’ heading, click ‘Advanced Settings’
  3. On the Advanced Settings screen click ‘Yes’ for the value: ‘Allow Management of Content Types?’
  4. Click ‘OK’
  5. Now, back on the Settings Screen, under the ‘Content Types’ heading, click ‘Item’
  6. On the Content Type Management Screen, under the ‘Columns’ section, click on the ‘Title’ column
  7. On the next screen click the radio button for ‘Hidden (Will not appear in forms)’
  8. Click ‘OK’”

Internal anchors

Whilst I’m sure it’s possible to use inline CSS, my SharePoint pages resort to some awful HTML hacks at times, like using tables for layout (and then having to mess around with valign directives and other such code that I haven’t used in about ten years…). I probably shouldn’t admit to such awful practices but I also had to relearn something I’d forgotten many years ago – the use of internal anchors within a page.

It’s worth noting though that, using SharePoint’s Rich Text Editor to create a link to #anchor actually created a link to http://server.domain.tld/layouts/RTE2PUEditor.aspx#anchor. I had to explicitly include the full pathname (e.g. http://server.domain.tld/Pages/Page.aspx#anchor) in the link in order to avoid this behaviour.

SharePoint, Dropbox, and shadow IT

This morning I had a problem with SharePoint. Well, when I say the problem was with SharePoint, it could be considered a “layer 8 problem” (i.e. user error) but it still illustrates a major issue  with corporate IT provision – not just in my organisation but in many, many businesses, all over the world.

You see, last night, I uploaded a presentation to our intranet. It was a 20MB file over an ADSL/VPN connection and the browser upload session timed out so I used SharePoint’s Windows Explorer view (which I think is WebDAV).  The file was copied, I edited the properties in the browser and all was good, I thought.

Fast forward to this morning and people were telling me the links to the presentation in my team’s newsletter didn’t work. But they did for me… embarrassingly (because the newsletter goes right up the company – to CEO level), I sent an email with the correct link in naked form (horrible long URL, rather than as a hyperlink on some nice text) but people were still getting HTTP 404 responses (file not found).

To cut a long story short, the WebDAV upload had not checked in the file (by design, I now think) and even editing the properties afterwards didn’t. I could see the file, but no-one else could. Once the file was checked in all was well – except from  my red face (and my insistence that HTTP 404 isn’t a permissions error – that would be 403).

I lost a good chunk of this morning on this and the related clean-up activities when, essentially, all I wanted to do was share a file with some colleagues – a common business requirement that shouldn’t really be a problem in 2011. So I tweeted:

I need Corporate Dropbox; SharePoint is just one usability nightmare after another... (cue flow of tweets telling me SharePoint is great...)
Mark Wilson

I expected a deluge of people supporting SharePoint and telling me that I’m just a dumb user, what I actually got was RTs (showing this is not just an issue for me) and then a succession of people suggesting various Dropbox-like  products for that could be used by corporates.

Lots of people are suggesting Box.net and there’s Dropbox for TeamsOxygenCloud and ShareFile too. I suppose, taken at face value this sort of product is exactly what my tweet asked for but it’s not really a corporate version of Dropbox that I need – it’s the simplicity of Dropbox (dump “stuff” in a folder and it’s wherever I need it – in the cloud, on other machines, available to share with others, etc.) – I’m sure there are many solutions that do this, with varying degrees of success (just that Microsoft SharePoint is not one of them…). But technology is only one part of the issue.

My scenario (and the reason I’m writing this) is actually a perfect example of why we have shadow IT in organisations today. End users (consumers) want to do “something”. That “something” is hard to do with their enterprise tools, so they find another way around the problem. Over time that solution becomes embedded – that’s when the problems start for the CIO (or, maybe, for the individual who didn’t follow the stated IT policy…). Those problems generally boil down to one of two things: security and manageability. In this case, the file is already available on SlideShare, but it could have been something confidential – like the business model I was creating yesterday afternoon – and that wouldn’t have been something I wanted floating around on servers that my company doesn’t control.

I’m sure that the multitude of “solutions” to my problem are all great in their own way but if I start to use them, well, all I’ll really be doing is perpetuating the issue of shadow IT.

(Incidentally, I did come across some interesting projects from the responses I received: remember Novell iFolder? it’s still around in open source form from Kablink; and VMware’s Project Octopus could have potential too.)

Exporting SharePoint Calendar details in iCalendar format

One of my current activities involves sharing the contents of a SharePoint calendar, which is hosted on an Intranet site, with external contacts.  An extranet portal would be one possible approach but it’s probably over-engineering the solution and a simple calendar export, updated on a regular basis would also suit the requirement.

SharePoint allows RSS export from a Calendar but the events are exported in the order in which they were added to the calendar, rather than in chronolical order.  I thought it would be far more useful to export them in iCalendar format and it turns out that’s possible too – with the addition of  an open source webpart called iCal Exporter (which my colleague Andrew Richardson tracked down).  You can also interrogate the SharePoint object model directly but that’s beyond my limited coding abilities.

Installing the webpart is pretty straightforward:

  1. Unzip the compiled version of the iCal Exporter webpart and copy the iCalExporter.wsp file to the hard drive on a SharePoint server (I used Windows SharePoint Services 3.0).
  2. From the command prompt, issue the following commands to navigate to the folder containing stsadm.exe, install the solution and deploy the solution:
    cd “%commonprogramfiles%\Microsoft Shared\Web Server Extensions\12\bin”
    stsadm –o addsolution –filename “c:\iCalExporter.wsp”
    stsadm –o deploysolution –name iCalExporter.wsp –local
    (it may be necessary to specify other options if deploying in a multi-server environments.)
  3. Using a web browser, navigate to the server’s site collection features page and click the Activate button on the iCalendar export button feature.

Once installed, there is an additional option on the Actions menu export the calendar in iCalendar format.  Give the resulting file an .ics extension and distribute it at will – most calendar clients (I tested in Outlook but it should work for others too) will be able to view the appointment details.

Introducing Microsoft PowerPivot

I spent last week hanging out in West London at the Microsoft UK TechDays events in order to learn something more about the technologies I work with and something new about a few others.  Thursday’s SQL Server/Business Intelligence sessions definitely fell into the latter category and I saw some pretty cool stuff including PowerPivot.

Formerly codenamed Gemini, PowerPivot is part of the SQL Server 2008 R2 release but does not really need SQL Server.  It is intended to provide business end users with access to distributed data. Available for both Excel (2010 only) and for SharePoint, it lets Excel power users know where data is, how to get to it and need to share it.

PowerPivot allows users to pull in large quantities of data from disparate sources for fast analytics.  It can store hundreds of millions of rows of data in Excel and runs analysis services on the client, with data stored in memory – not SQL. Column-based compression is used to reduce the data size on disk (the actual ratio depends on whether that data is textual or numeric – numeric compresses well and that’s generally the sort of data that is used for this type of reporting). There is a limit of 4GB of address space and a 2GB file limit on disk but Microsoft state that’s not a limitation of PowerPivot – these restrictions mean that reports can be deployed to SharePoint later.

In order to make PowerPivot easy for business users to use, Microsoft has come up with a set of Data Analysis eXpressions (DAX) that provide a simplified set of analysis functions using Microsoft Excel-like syntax.  Business users can perform DAX functions and standard Excel calculations on the data, including the use of Excel slicers (a new feature so that end user do need to understand pivot tables in order to filter the data). Pivot tables, slicers and graphs can be built up to create a dashboard, which is just a few clicks away from being saved to SharePoint.

Once published, users can view a PowerPivot Gallery in SharePoint – including previews of reports, and so an existing report can be used as a data source for a new report using ATOM for the publishing/syndication.   Taking that a step further, PowerPivot for SharePoint allows pivots to be published as web applications for a team – and administrators can track usage of the dashboards that users create to discover those “apps” that are becoming business critical, in order to transition them to a state that is properly cleansed/governed. 

Whilst SharePoint is the only supported platform in order to publish PowerPivot reports (and obtain management data), the data consumed by the pivots may originate from a variety of sources.  It’s worth noting though that, if SharePoint is used, an Enterprise SharePoint platform is required in order to provide the Excel Services capability.

Whilst some are concerned about bringing together data from disparate sources, PowerPivot does not represent anarchy. The data may not have been cleansed (e.g. using fuzzy logic) but, if needs to be governed with proper stewardship, it can always be brought into the data warehouse. 

As for securing the data – if users can access the data, they can access it regardless of PowerPivot. Organisations should look to use rights management and other security mechanisms to protect data from information leakage.

In summary, PowerPivot allows:

  • Analysis of external data within the context of corporate data.
  • Analysis of large data sets beyond the limits of Excel.
  • Sharing of insights.
  • Consumption of reports as data sources.
  • Easy access to data, without a major IT project.
  • The ability to gather business requirements (e.g. identify commonly used reports) prior to implementing a fully managed reporting solution.

More details of PowerPivot can be found in the PowerPivotPro FAQ or at Microsoft’s PowerPivot site, where there are videos, demos, and the software can be downloaded for free.

1 2 3 4 5