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:

To This:

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:

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:

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:

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:

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:

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 Library
  
  
• 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.

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!