Setting up a Project Status Rollup dashboard – The Easy Way

I’ve been working on a project at a clients site and a request came through from the Program Manager to have a reporting dashboard where the management team could quickly see:

  1. A list of project statuses collected from all project sites
  2. Present the statuses in a format that makes it easy to see where the issues are
  3. Allow mgt to quickly view issue detail
  4. Provide quick access to the detailed report

Immediately I thought:

  1. Content Query Web Part, with a common “Status Report” content type being used across Sub-sites
  2. Traffic lights or something
  3. Hmm… maybe some kind of layer that popped up when the user moused over it? Easy done with an Alt tag on an image
  4. Link to the Status report document

This incredibly long-winded blog article is going to take you through the process of going from this:

image

To This:

image

Don’t be disheartened though – it’s long because it’s detailed, not because it’s hard. The second time you do this, I expect it would take 1-2 hours.

So, the first thing to do is to create the common Content type that the CQWP would use to “see” all of the status reports. It needed to contain some HTML Calculated fields in it, that would be used to present the traffic light images to the user.

Ingredients:

For each aspect of a Project Status Report (Overall, Scope, Schedule, Risks and Issues, Resources and Budget) the following fields were created:

  • Status (Choice – Red / Yellow / Green)
  • Details (Multiline Text field)
  • Calculated Field
  • Hidden Field (Single Line Text – To store what was in the details field last time, and see if it’s changed)

The other field that was also created was an Executive Summary, which described the project at a high level (some project names – especially acquisition / sensitive projects – are intentionally vague 🙂 ). The result was something like this:

image

If the company has an existing “Status Report” Word document template then you can build a Document Content type based on this, with embedded Status, Details and Executive Summary fields into the relevant areas of the document, so they can be modified while the document is open (Word 2007 allows you to edit SharePoint Fields in the document, then save the document and fields in one step).

Activate the “Publishing” Site Collection Feature, which gives you the CQWP (if you don’t already have it activated, you will need to).

Finally, add the new content type to all of the relevant document libraries and to the site template that is being used to create the project sites. To make it easier to implement on other sites, you could also create a Status Report Document Library template and save it to the root site.

Let’s Get Started.

For those of you unfamiliar with the HTML Calculated field, basically we build a HTML string in the calculated field using properties within the same list item, then with a bit of JavaScript magic we turn it into real HTML that gets rendered by the browser. You can find out more about it here: HTML Calculated Column – where it all began!

Where To Start:

First thing is to write down all of the project aspects you want to appear in the Dashboard, then create the fields and the Content Type at the Parent Level of the sites that will be containing the information being “Rolled up” into the dashboard. We’ll then use these tracked items to create “sets” of fields for each item.

HINT: – When creating the Site Columns, do yourself a favour – create them WITHOUT spaces, apostrophes, hyphens, etc – UseCapitalLettersAtTheStartOfEachWord – Then once the field is created, go back and change it to something more readable (otherwise you’re dealing with _x0020_’s in the field names).

The List Fields and Calculated Columns

So once you’ve created a document library and added the “Executive Overview”, “Status” and Details” columns you want to track in it, you then need to add 2 more columns – A Calculated HTML column for each tracked status, and a single line details column which captures the first 255 characters of the Multiline Details column (more on that later). Both columns are hidden in the content type you are using The calculated fields contained the following formula

="<DIV><nobr>Risks and Issues: <IMG id='HoverImage' src='/_layouts/images/KPIDavid-"&IF([Risks and Issues]="Green",0,IF([Risks and Issues]="Yellow",1,2))&".gif' alt='"&[RiskDetailsShort]&"'  align='absmiddle' /><img src='/_layouts/images/projects_blank.gif'></nobr></DIV>"

Basically, this formula identifies what the “Choice” field is set to (in this case [Risks and Issues]) and displays an image based on the setting – Green displays “KPIDavid0.gif”, Yellow Displays “KPIDavid1.gif” and Red / Null displays “KPIDavid2.gif”. The Details Field is then attached to the image as the “Alt” property, and finally we Tag it with the “HoverImage” ID and give it a text heading (in this instance, “Risks and Issues: “). The reason we give these images a specific unique ID of HoverImage is because we need a way to identify which ones get affected by our popout bubble script – we don’t want it to affect all the images on the page 🙂

One other thing to note: I created 3 image files and called them KPIDavid0.gif, 1.gif and 2.gif (Green, Yellow and Red respectively). I also created a “spacer” blank gif file which I used to generate nice pixel-accurate separation between the results, applying the same effect across all results (this is the projects_blank.gif that appears in the formula above).

The SharePoint Designer Workflow

The one field that needs to be updated using an SDP Workflow is the Hidden Details field – you’ll notice in the ingredients listed, the details field is a multiline field whereas the Hidden Details field is a single line text field. I did this because inputting data into a multiline text field with a toolbar is intuitive, but I didn’t want random line breaks or HTML breaking my script (note I never tested to see if it did, but I figured it would – and this was a safe way to filter out the rubbish). Also, you can’t use multiline text fields in a Calculated Field – which we need to do. At the time this was a “SkunkWorks” project – Hidden budget, no committed resources – so I minimised the test cycle any way I could.

The SharePoint designer workflow looked like this:

clip_image001

Essentially, it checks to see if the multiline fields start with the same as the single line fields. If they are the workflow finishes. If the file is currently checked out, the workflow finishes (Not having this will lead to failing workflows when a user hits save while editing the Status report online). If both of these conditions are false, then it updates all of the “Hidden Details” fields with the content in the multi-line detail fields the user fills out. I did this prior to finding out that SP2 came with a fix for infinite looping of “On Change” workflows – see here for the details. Had I known, I would have skipped the first condition.

So we have the Content Type, the Columns, the Workflow – What’s next?

The Content Query Web Part and ItemStyle.xsl!

There’s 2 parts that need to be customised – the XML to return the fields we want (you can change it when you export the CQWP), and the XSL that will render the additional fields we’re interested in. Load up the page you want the dashboard to appear on, then add the CQWP – you will probably want to use settings like this:

  • Source – Show items from following site and all subsites, then browse to the current site (or type it in) – this will allow you to “See” all of the Project Status Reports on the current site or any project site below it.
  • List Type – Document Library (Assuming you’re status reports are a document) or a custom list you have built for this piece of work.
  • Content Type – First choose the Content Type Group, then the content type you created earlier.

Click OK and make sure the CQWP returns a result – you will need to add a dummy file and data in there so you can be sure (it’ll help if you need to troubleshoot as well).

ItemStyle.XSL – Round One

Now you know you’re returning the status report documents, it’s time to see what’s already getting retrieved from the CQWP – for that we need to add a new Item Style to the ItemStyle.xsl file you’ll find in the Style Library – http://<SiteRoot>/Style%20Library/XSL%20Style%20Sheets/ItemStyle.xsl.

<xsl:template name="RenderStatusReportFields" match="Row[@Style='RenderStatusReportFields']" mode="itemstyle">
    <xsl:for-each select="@*">
        P: <xsl:value-of select="name()" /><br />
    </xsl:for-each>
</xsl:template>

This piece of code goes between the final </xsl:template> and before the </xsl:stylesheet> at the bottom of the file.This will give you a neat list of all the properties being returned from the CQWP.

  • Save the ItemStyle.xsl file back to the http://<SiteRoot>/Style%20Library/XSL%20Style%20Sheets/ library,
  • Publish a major version of it
  • In the CQWP Properties, change the Item Style Drop-down list to the one you just created (in our example, it’s called RenderStatusReportFields) then
  • Refresh your page with the CQWP on it – You’ll see something like this:

image

So… now we know what fields are already there. Hmmm… There’s a couple of useful fields there… We might use “Author” and “Modified” so we can see who last updated it and when… we have Title as well… but none of our custom fields are coming through. Easy to fix 🙂

The Content Query Web Part

Export the Content Query Web Part, and open it in Notepad. For each additional field you want, go through the following process:

  • From the “Status Report” Content type, click on a column name
  • Right-click the “Open in a new Window” link and click “Properties”.
  • Highlight (with your mouse) the “Path” on the properties window, Copy it onto the Clipboard and Paste into a separate Notepad
  • <CTRL>+F, type in &Field= and search for that string
  • The word that appears after that string is the Internal Column Name you need to Write down. If you followed my HINT above, it should be the column name you first typed in.

Make the following changes to the CQWP:

Change the CommonViewFields property Type so it contains the extra fields you want (All you need are the calculated fields and the Executive Overview field)

        <property name="CommonViewFields" type="string">Exec_x0020_Overview,Note;Budget_Calc,Calculated;Overall_Calc,Calculated;Resources_Calc,Calculated;Risks_Calc,Calculated;Sched_Calc,Calculated;Scope_calc,Calculated</property>

You can see in the list above that I created Executive Overview field with a space, which then had to be written in as Exec_x0020_Overview. Each field has an InternalName and a ColumnType reference. To determine the Column Type (if you have a multiline text field for example, it’s a Type “Note”), refer to this table of column types:

Type As String (Use this Column) Type Display Name Type Short Description
Boolean Yes/No Yes/No (check box)
Calculated Calculated Calculated (calculation based on other columns)
Choice Choice Choice (menu to choose from)
Computed Computed Computed
ContentTypeId Content Type Id Content Type Id
Currency Currency Currency ($, ¥, €)
DateTime Date and Time Date and Time
File File File
Guid Guid Guid
Integer Integer Integer
BusinessData Business data Business data
ContentTypeIdFieldType Content Type Id Content Type Id
HTML Publishing HTML Full HTML content with formatting and constraints for publishing
Image Publishing Image Image with formatting and constraints for publishing
LayoutVariationsField Variations Page Layout Variations
Link Publishing Hyperlink Hyperlink with formatting and constraints for publishing
PublishingScheduleEndDateFieldType Publishing Schedule End Date Publishing Schedule End Date
PublishingScheduleStartDateFieldType Publishing Schedule Start Date Publishing Schedule Start Date
SummaryLinks SummaryLinks Summary Links data
TargetTo Audience Targeting Audience Targeting
Lookup Lookup Lookup (information already on this site)
LookupMulti Lookup Lookup (information already on this site)
Number Number Number (1, 1.0, 100)
Recurrence Recurrence Recurrence
Note Multiple lines of text Multiple lines of text
Text Single line of text Single line of text
URL Hyperlink or Picture Hyperlink or Picture
User PeoplePicker People Picker Control

 

You will also need to update the Title key:

 <property name="Title" type="string">Project Status Dashboard</property>

This makes sure you’re not trying to pick between 2 webparts called “Content Query Web Part” when you have them both uploaded :). So, upload the customised CQWP to the Webpart gallery (http://<siteRoot>/_catalogs/wp/Forms/AllItems.aspx), then return to the dashboard page and out with the old, in with the new!

ItemStyle.XML – Round 2

Now this time, we need to check the ItemStyle.xml file back out and replace this

    <xsl:for-each select="@*">
        P: <xsl:value-of select="name()" /><br />
    </xsl:for-each>

With this

<xsl:variable name="SafeLinkUrl">
  <xsl:call-template name="OuterTemplate.GetSafeLink">
    <xsl:with-param name="UrlColumnName" select="'LinkUrl'" /> 
  </xsl:call-template>
</xsl:variable>
<xsl:variable name="DisplayTitle">
  <xsl:call-template name="OuterTemplate.GetTitle">
    <xsl:with-param name="Title" select="@Title" /> 
    <xsl:with-param name="UrlColumnName" select="'LinkUrl'" /> 
  </xsl:call-template>
</xsl:variable>
<xsl:variable name="LinkTarget">_blank</xsl:variable> 
  <div id="linkitem" class="item link-item">
    <xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate" /> 
    <a href="{$SafeLinkUrl}" target="{$LinkTarget}" title="{@LinkToolTip}" style="font-size:12px;font-weight:bold;">
      <xsl:value-of select="$DisplayTitle" /> 
    </a>
  <div class="description">
    <table border="0" cellspacing="0">
      <tr>
        <td>
          <xsl:value-of select="@Overall_Calc" /> 
        </td>
        <td>
          <xsl:value-of select="@Scope_calc" /> 
        </td>
        <td>
          <xsl:value-of select="@Sched_Calc" /> 
        </td>
        <td>
          <xsl:value-of select="@Risks_Calc" /> 
        </td>
        <td>
          <xsl:value-of select="@Resources_Calc" /> 
        </td>
        <td>
          <xsl:value-of select="@Budget_Calc" /> 
        </td>
      </tr>
    </table>
  </div>
</div>

The areas to focus on are:

  • The closing </a> after <xsl:value-of select=”$DisplayTitle” /> is where I had the “Modified” and “Author” info. I then had a <br> and the Exec_x0020_Summary field values displayed before the calculated fields. At the end it was decided that these were not required in the final delivered solution, but it’s easy enough to add them back in if required.
  • The Field Names, Properties are both CaSe-SeNsItIvE – Above you can see where I created a field called Scope_calc – The C starting calc is lowercase, whereas all of the other ones are upper case. It does not throw an error, it just displays nothing – so check the case if you can’t get your fields to render.
  • See *** Update *** Below – You can add a property to the XSL that will automatically render the text as HTML, negating the need for the TextToHTML web part – add disable-output-escaping=”yes” to the Calculated Field properties in the CQWP eg <xsl:value-of disable-output-escaping=“yes” select=”@Overall_Calc” /> – A disadvantage of this though is that debugging your HTML gets a little trickier, because you can’t see it on the screen… on the other hand, if you’re like me and you build your formulas in Excel, it’s going to work first time every time 🙂

Progress Check!

At this time, you should have a fairly ugly CQWP that looks like this:

image

We’re spitting out HTML code – Congratulations, you’ve done all the heavy lifting! Now to the easy part 🙂

  • Create A “Resources” document library, enable versioning.
  • Give the “Style Resource Readers” group the ability to read documents in the Resource Library.
  • Grab all of the files from my SkyDrive folder
  • Extract the contents of the TooltipJS ZIP file to
    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\1033\STYLES\tooltipJS
  • Put the 4 images into
    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\IMAGES
  • Upload the HTM files to your Resources library you just created.
  • On the page with the CQWP, create a hidden Content Editor Web Part (CEWP) and link it to the TextToHTML.htm file you just uploaded to the Resources Libraryimage
  • Create a second hidden CEWP and link it to the Tooltip_Bubble_Code.htm file you just uploaded to the Resources Library

Voila! – That gives you everything you see on this page… except the Auto-Refresh (the countdown timer’s in the bottom of the window). If you want that as well (useful for an active dashboard) then create another CEWP and link it to the 5_minute_page_refresh.htm file.

Project Page

Thanks for taking the time to read through this article – hopefully you can see that with some pre-engineered modules and a quick tweak to the CQWP you can build your own KPI Indicators and reporting dashboards in no time. For other visual reporting enhancements (like heat graphs, highlighting rows or values based on Excel logic) using similar techniques, be sure to visit Cristophe‘s Blog. I got the Tooltips popup from Alessandro Fulciniti – http://web-graphics.com/mtarchive/001717.php – and modified it to suit the scenario above.

Cheers!
Brad

*** UPDATE *** – 10/Aug/09

Cristophe has pointed out that if I disable Output escaping in the XSL, I don’t even need the TextToHTML script! Not so bad for me (because I use it elsewhere on the page) but it makes it even easier to deploy. In the code sample above, you change the Calculated fields from this –
<xsl:value-of select=”@Overall_Calc” />
– to this –
<xsl:value-of disable-output-escaping=“yes” select=”@Overall_Calc” />
which means you can skip using the TextToHTML Script / Web part – this speeds up the load time incrementally too… Thanks Christophe!

Advertisements

About Brad Saide

I'm a SharePoint consultant. I'm also slowly going bald, seem to have a permanent spare tyre around my waist and enjoy socialising with friends over a beer or 10. The last 2 may possibly be related. Started working with SharePoint when the first version was in limited beta release (participated in the Technology Adoption Program while at Woolworths) and have been committed to the adoption of the technology as a business enabler ever since.
This entry was posted in Uncategorized. Bookmark the permalink.

17 Responses to Setting up a Project Status Rollup dashboard – The Easy Way

  1. Ramachandra says:

    Hi,Nice blog realy helpfull, can you help me, i am trying exactly how it is in the blog, in the CQWP where to link the resource library file. Thanks,Murthy

  2. WB says:

    Hi Great blog, I wondered how I would be able to modify this to suit a \’KPI\’ lists from each subsite reporting to a central dashboard on the home page?

  3. Brad says:

    @WB – That\’s actually how it works currently – provided your subsites are all part of the same Site collection, all you need to do is change the settings in the CQWP Web Part Settings to include "This site and all sub-sites" (you know, when you edit the page).

  4. Brad says:

    @Ramachandra – Sorry, I\’m not sure what you mean by "where to link the resource library file".

  5. David says:

    I just implemented your Project status rollup and I love it. I have run into some issues and I think it has to do with my limitations at the site. I had to roll everything into the src locations under my style library as I do not have access to the physical server. The rollovers are not working so I have been looking into what you said “The Details Field is then attached to the image as the “Alt” property, and finally we Tag it with the “HoverImage” ID and give it a text heading (in this instance, “Risks and Issues: “). The reason we
    give these images a specific unique ID of HoverImage is because we need a way to identify which ones get affected by our popout bubble script – we don’t want it to affect all the images on the page” my
    code for the calculated looks like this =”Budget: ”
    and I edited my master page to include

    and one more thing I added the CEWP with a link to my resource library popup.htm but I am not sure it is processing correctly, is there a good way to tell?

    If you are ever in the states I owe you a beer. 😉

    • Brad Saide says:

      Hi David.

      The rollovers are probably not working because there’s something wrong with the javascript – when you mouse-over the indicator blobs, do you get a javascript error in the bottom left corner of your browser window?

      Best suggestion I have is to look at the code that is generated by your page and compare it with the code in the article to see if there are minor differences (like an extra semicolon or one that’s missing, etc) – The file download contains the working code too, so it might be a good reference point.

      I remember a similar thing happened to me when building it and it turned out to be a Javascript error, so it’s the best (only?) suggestion I have 🙂

      Finally, Welcome to SharePoint. It’s one of those products where you’ll love it and hate it at the same time – because it’s so broad with its capabilities, you keep hitting limitations you need to code around, or go back to the business and reset their expectations on what can be delivered… But the fact that a business user can create a site where they can manage documents and store information and control access and share ideas… and all without having to interact with the IT department… that’s just gold.

    • Brad Saide says:

      By the way, it looks like you are not pulling the data out from the Content Query… or the formula in the calculated column is wrong… Might be worth cracking a new site collection and getting the solution working in a clean environment then working out what the differences are.

      Re: the workflow… I think that’s what puts the data into the Img Tags (which you seem to be missing)… but I have not built this in 2010, so I do not know what the different terms are that 2010 workflow uses compared to 2007 workflow from the screenshot.

  6. David says:

    one more thing, I left out the sharepoint designer workflow. That may be critical info. I left it out because the conditionals don’t seem to be valid in sharepoint 2010 designer. either that or I am missing something. I just started working on sharepoint two weeks ago and I have a Unix background, so I feel slightly handicaped.

  7. David says:

    I am not getting any error and when I run the js debugger in ie I get no issues. I agree it is probably a small bug somewhere in here. Are you still using the project status and have you found any enhancements worth adding? Thanks! I really enjoy your blog and can you recommend other reading?

    • Brad Saide says:

      I built this solution a couple of years ago, I don’t even think it’s running where I created it any more.

      What you could also do (apart from running up another site collection and building it all there as a test bed) is unhide your hidden columns, enter some text and see if it comes up in the mouseover bubbles…

      Recommended blogs – Google the following:

      – christophe text2html (he has 2 blogs and a demo site – lots of great ideas there) – https://www.nothingbutsharepoint.com/sites/eusp (the old End User SharePoint site) – sharepoint jquery (this is a non-developer’s “hammer for every nail” – it’ll get you out of a jam 99 times out of 100)

  8. David says:

    Thank you very much for the info, I will let you know what I find out and write it up, no matter how embarrassing it is.
    PS below is a snippet from the rendered html
    Overall: Scope: <IMG id='HoverImage' src='/sites/istest/Style%20Library/pmis/PmisKpi-2.gif' alt='this is the single line ProjectResourcesDetailsAlt ' align='absmiddle' /…..

  9. Mack says:

    Thanks for the info, Is there anyway this can be done without sharepoint designer?

  10. Ajay Jain says:

    The last activity on this blog was few years ago. Still giving it a shot in desperation. Brad, great blog. I need to implement exactly the same roll up dashboard with SP2010. I tried to follow everything to the tee. Got stuck at style templates. Waiting to get access to those. Also I figured I need to download files you had uploaded. Wondering if I could get those. Let me know if you can still help.

  11. SharePoint says:

    Hi Brad,

    First of all thanks for posting this valuable information.

    Few queries:
    a) I was trying to download the files from the Onedrive link, however it says “This item might not exist or is no longer available” , please help!

    b) In my case, have multiple list part of a site. Now working on building the dashboard. Therefore wondering creating the content types with the required site columns that are required to showcase on the dashboard is fine. However, how will it fetch the relevant data from the multiple lists and do I need to replace the Site column in those existing list that matches with the site column part of the new site content type?

    Kindly assist and thanks in advance for taking time to read my queries.

    Jason

  12. Tanu says:

    HI Brad,
    Your article is very helpful indeed, but my requirement is slightly more complicated as I want Overall column as a calculated field that will depend on other 5 indicators. SO basically I need is
    – the Overall Project status should be Green if all 5 indicators are green OR 3 indicators are amber OR 1 indicator is Red
    – the Overall Project status should be Red if >2 indicators are Red

    I am trying to workout a formula for this. Any suggestions on this would be much appreciated.
    Thanks
    Tanu

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s