Transform SharePoint Data Into Actionable Business Information (BI)

Products:  SharePoint 2010, XML Playground, Sublime Text 2

Weekend Project

I recently attended a Microsoft SharePoint training session focused on transforming basic list and library data into something more consumable by our business users.  A glob of data, by and of itself, is not actionable.  And the last time, I checked, the Chart Web Part and Performance Point Web Parts were half baked solution options in SP2010.

This session demonstrated tools and techniques for applying XSLT to the XML that exists behind every list and library within SP2010.  Equipped with the right toolbox of tools, I/You can transform this mundane data into visual images that help the business end users really “see” areas of opportunity and areas that need greater focus.  In other words, these users will have real information at their finger tips; real, actionable information.  Information that they will be able to use to transform their team, department and company in ‘real-time’.

The timing was perfect as my client was wanting a few real-time charts implemented on their SharePoint Site with the end result being a Dashboard Page.

Here’s a microcosm of the experience; let’s get busy living …

Tools & Accessories

#1 Sublime Text 2 [XSL/XML Editor]
#2 XMLPlayground.com [not IE compatible; get FireFox]
#3 SharePoint Site List

XML Source
Copy the following source into the XML window of XML Playground.

<dsQueryResponse ViewStyleID="" BaseViewID="1" TemplateType="100" RowLimit="3">
<Rows>
<Row ID="599" Title="IR 9/4/2012 12:00:00 AM" Reporting_x0020_Year="2012" Classification_x0020_Code.="16;#Slip/Trip/Fall" Report_x0020_Type="Illness" />
<Row ID="600" Title="IR 9/6/2012 12:00:00 AM" Reporting_x0020_Year="2011" Classification_x0020_Code.="15;#Puncture" Report_x0020_Type="Near Miss" />
<Row ID="601" Title="IR 9/18/2012 12:00:00 AM" Reporting_x0020_Year="2012" Classification_x0020_Code.="19;#Vehicle" Report_x0020_Type="Near Miss" />
<Row ID="602" Title="IR 9/18/2012 12:00:00 AM" Reporting_x0020_Year="2012" Classification_x0020_Code.="19;#Vehicle" Report_x0020_Type="Near Miss" />
</Rows>
</dsQueryResponse>

XLST Code
Copy the following code into the XLST window of XML Playground

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output omit-xml-declaration="yes" indent="yes" />

<xsl:key name="keyReportType" match="Row" use="@Report_x0020_Type" />
<xsl:key name="keyYear" match="Row" use="@Reporting_x0020_Year" />

  <xsl:template match="/">
 <table border="0" cellpadding="3">
   <thead>
      <tr>
   <th>Report Type</th>
   <th>Year</th>
   <th>Count</th>
   <th>Graph</th>
      </tr>
   </thead>
   <tbody>

      <!-- IDENTIFY TOTAL NUMBER OF ROWS -->
      <xsl:variable name="totRowCount" select="count(//Row)" />

  <!-- START REPORTING OF THE GROUP: REPORT TYPE -->
  <xsl:for-each select="//Row[generate-id(.) = generate-id(key('keyReportType', @Report_x0020_Type)[1])]">

   <!-- SORT REPORT TYPE ENTRIES IN ASCENDING SEQUENCE -->
   <xsl:sort select="@Report_x0020_Type[1]" order="ascending" />

   <!-- SET A VAR EQUAL TO THE CURRENT REPORT TYPE BEING PROCESSED -->
   <xsl:variable name="varReportType" select="@Report_x0020_Type" />

      <!-- SELECT ALL YEARS BELONGING TO THE REPORT TYPE -->
       <xsl:variable name="grpYears" select="//Row[@Report_x0020_Type=$varReportType]" />

       <!-- REPORT DETAILS -->
    <xsl:call-template name="ReportTypeYears">
         <xsl:with-param name="grpYears" select="$grpYears" />
         <xsl:with-param name="totRowCount" select="$totRowCount" />
       </xsl:call-template>

   <!-- SEE IF THERE ARE MORE REPORT TYPES TO PROCESS -->
  </xsl:for-each>

   </tbody>
 </table> 

 <!-- EMBEDDED STYLESHEET -->
 <style type="text/css">
 .graphBar { background: black; height: 12px; }
 </style>

  </xsl:template>
  <xsl:template name="ReportTypeYears">
  <xsl:param name="grpYears" />
  <xsl:param name="totRowCount" />

    <tr>
     <td><hr/></td>
     <td><hr/></td>
     <td><hr/></td>
     <td><hr/></td>
    </tr>

    <tr>
     <td>
      <xsl:value-of select="@Report_x0020_Type"/>
     </td>
     <td></td>
     <td></td>
     <td></td>
    </tr>

  <!-- START REPORTING OF THE GROUP: REPORTING YEAR -->
  <xsl:for-each select="//Row[generate-id(.) = generate-id(key('keyYear', @Reporting_x0020_Year)[1])]">

   <!-- SORT YEAR ENTRIES IN ASCENDING SEQUENCE -->
   <xsl:sort select="@Reporting_x0020_Year[1]" order="ascending" />

   <!-- SET A VAR EQUAL TO THE CURRENT REPORT TYPE YEAR BEING PROCESSED -->
   <xsl:variable name="cntYear" select="@Reporting_x0020_Year" />
   <xsl:variable name="cntYearEntries" select="count($grpYears[@Reporting_x0020_Year=$cntYear])" />

   <xsl:if test="$cntYearEntries &gt; 0">
    <tr>
     <td>
      <!-- <xsl:value-of select="$grpYears[@Reporting_x0020_Year=$cntYear]/@Report_x0020_Type"/> -->
     </td>
     <td>
      <xsl:value-of select="$grpYears[@Reporting_x0020_Year=$cntYear]/@Reporting_x0020_Year"/>
     </td>
     <td align="right">
      <xsl:value-of select="$cntYearEntries" />
     </td>
     <td>
         <!-- CALCULATE THE GRAPH BAR LENGTH; ASSUME 100% EQUALS 600PX -->
      <div style="width: {($cntYearEntries div $totRowCount) * 600}px;"></div>
     </td>
    </tr>
   </xsl:if>

  <!-- SEE IF THERE ARE MORE ROWS TO PROCESS -->
  </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

End Result
Click on visual for Full Size
– left visual transformed using above XLST
– right visual can be transformed with a few tweaks to the XLST (see below code snippet) 

XLST Code [Transform XML into the Right Visual]
Copy the following code into the XLST Window of XML Playground

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output omit-xml-declaration="yes" indent="yes" />

<xsl:key name="keyReportType" match="Row" use="@Report_x0020_Type" />
<xsl:key name="keyClass" match="Row" use="@Classification_x0020_Code." />

  <xsl:template match="/">
 <table border="0" cellpadding="3">
   <thead>
      <tr>
   <th>Report Type</th>
   <th align="left">Classification</th>
   <th>Count</th>
   <th>Graph</th>
      </tr>
   </thead>
   <tbody>

      <!-- IDENTIFY TOTAL NUMBER OF ROWS -->
      <xsl:variable name="totRowCount" select="count(//Row)" />

  <!-- START REPORTING OF THE GROUP: REPORT TYPE -->
  <xsl:for-each select="//Row[generate-id(.) = generate-id(key('keyReportType', @Report_x0020_Type)[1])]">

   <!-- SORT REPORT TYPE ENTRIES IN ASCENDING SEQUENCE -->
   <xsl:sort select="@Report_x0020_Type[1]" order="ascending" />

   <!-- SET A VAR EQUAL TO THE CURRENT REPORT TYPE BEING PROCESSED -->
   <xsl:variable name="varReportType" select="@Report_x0020_Type" />

      <!-- SELECT ALL YEARS BELONGING TO THE REPORT TYPE -->
       <xsl:variable name="grpClass" select="//Row[@Report_x0020_Type=$varReportType]" />

       <!-- REPORT DETAILS -->
    <xsl:call-template name="ReportTypeYears">
         <xsl:with-param name="grpClass" select="$grpClass" />
         <xsl:with-param name="totRowCount" select="$totRowCount" />
       </xsl:call-template>

   <!-- SEE IF THERE ARE MORE REPORT TYPES TO PROCESS -->
  </xsl:for-each>
   </tbody>
 </table> 

 <!-- EMBEDDED STYLESHEET -->
 <style type="text/css">
 .graphBar { background: black; height: 12px; }
 </style>

  </xsl:template>
  <xsl:template name="ReportTypeYears">
  <xsl:param name="grpClass" />
  <xsl:param name="totRowCount" />

    <tr>
     <td><hr/></td>
     <td><hr/></td>
     <td><hr/></td>
     <td><hr/></td>
    </tr>

  <xsl:variable name="shortClassCode" select="substring-after(@Classification_x0020_Code.,'#')" />

    <tr>
     <td>
      <xsl:value-of select="@Report_x0020_Type"/>
     </td>
     <td></td>
     <td></td>
     <td></td>
    </tr>

  <!-- START REPORTING OF THE GROUP: CLASSIFICATION CODE -->
  <xsl:for-each select="//Row[generate-id(.) = generate-id(key('keyClass', @Classification_x0020_Code.)[1])]">

   <!-- SORT CLASSIFICATION CODE ENTRIES IN ASCENDING SEQUENCE -->
   <xsl:sort select="substring-after(@Classification_x0020_Code.[1],'#')" order="ascending" />

   <!-- SET A VAR EQUAL TO THE CURRENT REPORT TYPE CLASSIFICATION CODE BEING PROCESSED -->
   <xsl:variable name="cntClass" select="@Classification_x0020_Code." />
   <xsl:variable name="cntClassEntries" select="count($grpClass[@Classification_x0020_Code.=$cntClass])" />

   <xsl:if test="$cntClassEntries &gt; 0">
    <tr>
     <td>
      <!-- <xsl:value-of select="$grpClass[@Classification_x0020_Code.=$cntClass]/@Report_x0020_Type"/> -->
     </td>
     <td>
      <xsl:value-of select="substring-after($grpClass[@Classification_x0020_Code.=$cntClass]/@Classification_x0020_Code.,'#')" />
     </td>
     <td align="right">
      <xsl:value-of select="$cntClassEntries" />
     </td>
     <td>
         <!-- CALCULATE THE GRAPH BAR LENGTH; ASSUME 100% EQUALS 600PX -->
      <div style="width: {($cntClassEntries div $totRowCount) * 600}px;"></div>
     </td>
    </tr>
   </xsl:if>

  <!-- SEE IF THERE ARE MORE ROWS TO PROCESS -->
  </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

Last But Not Least … Associate the XSL File With A SharePoint Page Containing A List or Library Web Part

Steps
1.  Save the above XSLT locally using the .XSL file type
2.  Upload the file to your SharePoint Library
3.  Create a List (that relates to the above XML); here’s how:
     a) Title                         .. default column of every list
     b) Reporting Year      .. text, number of calculated field (does not matter)
     c) Classification Code .. text, choice, lookup (above XSL is expecting a lookup column)
     d) Report Type           .. text
3.  Create a Page
4.  Insert the List as a Web Part on the Page
5.  Edit the Web Part and associate the XSL file to it
6.  Save the Web Part
7.  Revise the XSL based on your presentation requirements
8. Enjoy

Another helpful resource related to visual BI with wicked No Code Solutions
http://blog.pathtosharepoint.com/2012/10/31/trick-or-treat-text-to-html-the-wicked-no-code-way-part-ii/

How has SharePoint affected you?

Advertisements
This entry was posted in Lists, SharePoint and tagged , , , . Bookmark the permalink.

One Response to Transform SharePoint Data Into Actionable Business Information (BI)

  1. Mitya says:

    Thanks for featuring XMLPlayground. It should be IE compatible, though depends which version of IE. Please let me know the issues you encountered in IE.

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