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!

This is a great reference when talking to clients who say "But SharePoint looks so bad out of the box, and it's so hard to customise it"! There's a few hundred there, but I got bored counting it.

http://www.wssdemo.com/Lists/Resources/AllItems.aspx, and the Top 100 - http://www.wssdemo.com/Pages/topwebsites.aspx

• Increase the maximum upload size
  Click Start, point to All Programs, point to Administrative Tools, and then click SharePoint Central Administration.
  Under Virtual Server Configuration, click Configure virtual server settings.
  On the Virtual Server List page, click the virtual server you want to change.
  On the Virtual Server Settings page, under Virtual Server Management, click Virtual server general settings.
  Under Maximum upload size, type the maximum file size (in MB) that you want to allow to be uploaded.
  Click OK.
  If IIS is timing out when you upload large files, you can configure the Connection timeout setting in IIS to allow more than the default 120 seconds (2 minutes).
  
• Tune the IIS connection timeout
  Click Start, point to All Programs, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.
  Right-click the virtual server you want to configure, and then click Properties.
  Click the Web Site tab.
  In the Connections section, in the Connection timeout box, type the number of seconds you want IIS to wait before timing out.
  Click OK.
  
• Increase the default chunk size for large files
  The large–file–chunk–size property must be set from the command line. This property is configured for a server or server farm, and cannot be configured for an individual virtual server. To set this property, use the following syntax:
  Stsadm.exe –o setproperty –pn large–file–chunk–size –pv <size in bytes>.
  After making a change to this property, you must restart IIS. You can restart IIS by typing iisreset on the command line.

• On your server computer running Windows SharePoint Services, open Notepad.
  In Notepad, navigate to the %HomeDrive%\Inetpub\wwwroot folder and open the web.config file.
  Locate the PropertySize attribute in the configuration\SharePoint\WebPartLimits element.
  Change the PropertySize attribute to the maximum size you need.
  Save and close the web.config file.

Large file support limitations

The following features do not support files larger than 50 MB:

• Virus checking.
• Picture libraries.
• Streaming files.
• Client-side restoration of smigrate backup files (limited to 2 GB). The manifest files for an smigrate backup cannot be larger than 2 GB.
• Site templates (limit of 10 MB per site template, including content).
  

While I was working in CustomWare I had the great pleasure of working with a guy called Sukri, a SharePoint developer. He's the kind of guy you'd mention something to, and it would tick over in the back of his mind, while he was doing his normal day-to-day work…. then a few days later, he'd come to you and say - "By the way, I know how we can address your client's requirements". Like he did for one of our clients… we were going through the client's needs, and we talked about how the client wanted to be able to present financial data in SharePoint, then allow the user to hover over and highlight a row of data… and allow the user to click the row and make it stay highlighted.

He came up with a script that allowed you to do both – then one of the other guys, Ben, decided to enhance it so you could select and deselect multiple rows. Here's a screenshot of how it looked on the screen…

Code:

<script language="javascript" type="text/javascript">
var preRow;
var preClassName;
var orgBColor;
var hoverColor = "#ffcc99";
var selectedColor = "#d2d2ff";
var SPGridViewGuid = "{<<LIST GUID>>}-{<<LIST VIEW WEB PART GUID>>}"; 

_spBodyOnLoadFunctionNames.push("addSelectedRowEvent");
function addSelectedRowEvent() {
// Get all TABLE tag objects
var oTables = document.getElementsByTagName("TABLE");
for (var i=0;i < oTables.length; i++ )
{
  // Filter where table id equal to SPGridView guid
  if (oTables[i].id == SPGridViewGuid)
       {
        // Get all TR tag objects
        var oRows = oTables[i].getElementsByTagName("TR");
        for (var j=0;j < oRows.length; j++ )
   {
    if (j != 0)
    {
     // Add client event onMouseOver
     oRows[j].onmouseover = function() {
      this.style.backgroundColor = hoverColor;
     }
     // Add client event onMouseOut
     oRows[j].onmouseout = function() {
      this.style.backgroundColor = "";
     }
     // Add client event onClick
     oRows[j].onclick = function() {
      // If previous row is not null 

      if(typeof(preRow) != 'undefined')
      { 

       // Assign previous stored background color and class name to TR object
	//Code below has been commented out prior to demo
	//
        //preRow.bgColor = orgBColor;
        //preRow.className = preClassName;
        // Change font weight
       // try{ChangeFontWeight(preRow,'normal');}catch(e){;}
      }
      // If this row is alternating row
      if(this.className != "")
      {
       // Assign previous alternating class to TR object
       preClassName = this.className;
       this.className = "";
      }
      // Assign new background color to TR object
       orgBColor = this.bgColor;
       if(this.bgColor == "#d2d2ff")
       {
       this.bgColor = "white";
       try{ChangeFontWeight(this,'normal');}catch(e){;}
       }
       else
       {
       this.bgColor = selectedColor;
        // Change font weight
       try{ChangeFontWeight(this,'bold');}catch(e){;}
       } 

      preRow = this;
     }
    }
   }
       }
}
}
function ChangeFontWeight(selectedRow,fontWeight) {
;
for (i=0;i<selectedRow.cells.length;i++)
{
  selectedRow.cells(i).style.fontWeight = fontWeight;
}
}
</script>

How it works:
The code you see above affects the HTML that is already on the page, so you need to position this at the end of the page below the list you are trying to modify. Because it is restricted to a list and a view, you can put it on a standard page (such as default.aspx) and it will only affect one of the views on the page.

Configuration:
The only thing you need to do before using this script is to replace <<LIST GUID>> with the list GUID, and <<LIST VIEW WEB PART GUID>> with the web part GUID. you get both of these from the URL when editing a "View" – all you need to do is strip off the %7B and &7D, and the %2D's replace with hyphens " - ".

Deployment:
I recommend creating a library and storing this as a .htm file – then referencing it from the Content Editor Web Part. Place the CEWP after the list view, and mark it as hidden. Change the title to something meaningful (such as Sukri's cool mouseover highlighting and multiclick code) and you're done!

Storing the code as a separate file in a helper library gives you maximum flexibility, minimum change (no unghosting of pages) and version control of your code.

Issues:
The only problem with the code is that it does not capture what the original background colour was – so if you click and unclick a row, it stays white. Not so bad if your list view is white rows, but an issue if you have alternating background colours. Ben was working on getting that resolved too, but he didn't get it debugged in time for the demo (check the highlighted part above). If anyone can see where the issue in the code is, please let me know :)

Cheers!

Brad