SharePoint List Summaries & Reports with XML

One of the drawback of SharePoint lists is the ability to actually roll-up, report and summarize the data.



Sure we can open datasheet view and use the office web components but what if we want a summary of all the data as soon as we open the list.

This can be easily achieved with a little XML and a dash of SharePoint Designer!




In this list we have a rollup summary below showing calculations and a little progress bar for the project progress.

Here's how you do it:

1. Using SharePoint designer, insert a data view web part under the List by selecting Data View > Insert Data View from the main menu.

2. Expand the Shareoint Lists in the Data Source Library Task Pane and select the list or library that contains the information you wish to display. From the drop down select 'Show Data' to populate the Data Preview tab.

3. From the list of columns, select the ones that you wish to display and select ‘Insert selected fields as… multiple item view' from the drop down menu at the top of the window.

4. Now lets look at the code generated for the DataView....


a. The following line shows the data fields that we are going to work with



@Title,Title;@Resource,Resource;@Effort,Effort;@Project_x0020_Activity,Project Activity;@Planned,Planned;@Planned_x0020_Cost,Planned Cost;@Actual_x0020_Cost,Actual Cost;@Profit,Profit;@Rate,Rate;@ID,ID;@ContentType,Content Type;@Modified,Modified;@Created,Created;@Author,Created By;@Editor,Modified By;@_UIVersionString,Version;@Attachments,Attachments;@File_x0020_Type,File Type;@FileLeafRef,Name (for use in forms);@FileDirRef,Path;@FSObjType,Item Type;@_HasCopyDestinations,Has Copy Destinations;@_CopySource,Copy Source;@ContentTypeId,Content Type ID;@_ModerationStatus,Approval Status;@_UIVersion,UI Version;@Created_x0020_Date,Created;@FileRef,URL Path;


b. Now look for the following lines


xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
<th class="ms-vh" width="1%" nowrap="nowrap"></th>

<td class="ms-vh" nowrap="">
<?xml:namespace prefix = xsl /><xsl:text escaping="yes" preserve="yes" ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime">&amp;nbsp;</xsl:text>

We are going to add our bespoke data immediately after these lines


c. Declare our variables and any calculations we want to add


<span ><xsl:variable select="format-number(/dsQueryResponse/Rows/Row[@Rate='1000'], '#####')" name="a">
<xsl:variable select="sum(/dsQueryResponse/Rows/Row[@Rate='1000']/@Planned)" name="planned_pm">
<xsl:variable select="sum(/dsQueryResponse/Rows/Row[@Rate='1000']/@Effort)" name="effort_pm">
<xsl:variable select="sum(/dsQueryResponse/Rows/Row[@Rate='800']/@Planned)" name="planned_con">
<xsl:variable select="sum(/dsQueryResponse/Rows/Row[@Rate='800']/@Effort)" name="effort_con">
<xsl:variable select="53 - ($effort_pm + $effort_con)" name="project_remaining">
<xsl:variable select="($effort_pm + $effort_con)" name="project_cost_days">
<xsl:variable select="($effort_pm * 1000) + ($effort_con * 800)" name="project_cost_value">
<xsl:variable select="format-number($project_cost_days div 53 *100, '##')" name="project_cost_pc"></span></xsl:variable></xsl:variable></xsl:variable></xsl:variable></xsl:variable></xsl:variable></xsl:variable></xsl:variable></xsl:variable>
<xsl:variable select="format-number(/dsQueryResponse/Rows/Row[@Rate='1000'], '#####')" name="a"><xsl:variable select="sum(/dsQueryResponse/Rows/Row[@Rate='1000']/@Planned)" name="planned_pm"><xsl:variable select="sum(/dsQueryResponse/Rows/Row[@Rate='1000']/@Effort)" name="effort_pm"><xsl:variable select="sum(/dsQueryResponse/Rows/Row[@Rate='800']/@Planned)" name="planned_con"><xsl:variable select="sum(/dsQueryResponse/Rows/Row[@Rate='800']/@Effort)" name="effort_con"><xsl:variable select="53 - ($effort_pm + $effort_con)" name="project_remaining"><xsl:variable select="($effort_pm + $effort_con)" name="project_cost_days"><xsl:variable select="($effort_pm * 1000) + ($effort_con * 800)" name="project_cost_value"><xsl:variable select="format-number($project_cost_days div 53 *100, '##')" name="project_cost_pc"><span style="color:#ff6600;"></span>


d. Create our table to hold the values and then add our variables


<table class="ms-propertysheet" border="0" bgcolor="white" width="100%">
<tr><td valign="top">
<table class="ms-vb">
<tr><td><b>Project Management</b></td></tr>
<tr><td>Planned PM Days: </td><td> <xsl:value-of select="$planned_pm"/></td></tr>
<tr><td>Actual PM Days: </td><td> <xsl:value-of select="$effort_pm"/></td></tr>
<tr><td>Profit PM Days: </td><td> <xsl:value-of select="$planned_pm - $effort_pm"/></td></tr>
<tr><td>Profit PM Value: </td><td> <xsl:value-of select="($planned_pm - $effort_pm) * 1000"/></td></tr>
</table>
</td><td valign="top">
<table class="ms-vb">
<tr><td><b>Consultancy</b></td></tr>
<tr><td>Planned Consultant Days: </td><td> <xsl:value-of select="$planned_con"/></td></tr>
<tr><td>Actual Consultant Days: </td><td> <xsl:value-of select="$effort_con"/></td></tr>
<tr><td>Profit Consultant Days: </td><td> <xsl:value-of select="$planned_con - $effort_con"/></td></tr>
<tr><td>Profit Consultant Value: </td><td> <xsl:value-of select="($planned_con - $effort_con) * 800"/></td></tr>
</table>

And the pretty looking progress bar!

<</td><td valign="top">
<table class="ms-vb">
<tr><td><b>Summary</b></td></tr>
<tr><td>Project Remaining: </td><td> <xsl:value-of select="$project_remaining"/></td></tr>
<tr><td>Project Days Taken: </td><td> <xsl:value-of select="$project_cost_days"/></td></tr>
<tr><td>Project Cost Taken: </td><td> <xsl:value-of select="$project_cost_value"/></td></tr>
<tr><td>Project Profit : </td><td> <xsl:value-of select="($planned_pm - $effort_pm) * 1000 + ($planned_con - $effort_con) * 800" /></td></tr>


</table>


5. And that's pretty much it!

A mixture of SPD and a little xml calculation and you can achieve a quick solution to fill the gap in functionality that the list doesn't give us.

My next post will illustrate how to create and manipulate xml variables in MOSS Lists