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

Modifying MOSS Search Results with XSLT

I finally got round to modifying the way MOSS returns it's search results.


Not satisfied with a single line of results I wanted to be able to show the results in tabular form with multiple rows to give the user a better experience and to actually inform them which item of data in the results corresponded to which item of metadata.



This is what I wanted to achieve



So how is this done?....XSLT !



1. Modify your core search results webpart

This is covered completely in Tobias's Blog Here




2. Creating your XSLT

So now we know what properties are available we can decide which ones to add to our XSLT.
So lets dissect one -

The first section is the declarative xsl

<xsl:stylesheet version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
<xsl:output method="html" indent="no"/>
<xsl:decimal-format NaN=""/>
<xsl:param name="FileName" />
<xsl:param name="dvt_apos">'</xsl:param>


The second section parses the search results - this returns all the default columns
NOTE the lines highlighted in GREEN


<xsl:variable name="dvt_1_automode">0</xsl:variable>
<xsl:template match="/">
<xsl:call-template name="dvt_1"/>
</xsl:template>
<xsl:template name="dvt_1">
<xsl:variable name="dvt_StyleName">RepForm1</xsl:variable>
<xsl:variable name="Rows" select="/All_Results/Result" />
<table border="0" width="100%">
<xsl:call-template name="dvt_1.body">
<xsl:with-param name="Rows" select="$Rows" />
</xsl:call-template>
</table>
</xsl:template>
<xsl:template name="dvt_1.body">
<xsl:param name="Rows" />
<xsl:for-each select="$Rows">
<xsl:call-template name="dvt_1.rowview" />
</xsl:for-each>
</xsl:template>
<xsl:template name="dvt_1.rowview">


The following section actually renders the results to the page.

This section is the code that is parsed to the page in the GREEN table above

The data items from the search are highlighted in PURPLE

<tr>
<td width="60%" colspan="" class="ms-vb">
<table width="100%" border="0">
<tr>
<td width="20">
<img src="_layouts/images/CMSEditSourceDoc.GIF"></img>
</td>
<td width="20%">
<b>Title:</b>
</td>
<td>
<xsl:value-of select="title" />
</td>
</tr>
<tr>
<td width="20">
<img src="_layouts/images/memberother.gif"></img>
</td>
<td width="20%">
<b>Author:</b>
</td>
<td>
<xsl:value-of select="author" />
</td>
</tr>
<tr>
<td width="20">
<img src="_layouts/images/LINKTOPAGE.GIF"></img>
</td>
<td width="20%">
<b>Link:</b>
</td>
<td>
<a href="{url}" target="_blank">
<xsl:value-of select="title" />
</a>
</td>
</tr>
<tr>
<td width="20">
<img src="_layouts/images/bizdatacontentsource.gif"></img>
</td>
<td width="20%">
<b>Description:</b>
</td>
<td width="70%">
<xsl:if test="description != ''">
<xsl:value-of select="description" />
</xsl:if>
<xsl:if test="description = ''">
No Description Added
</xsl:if>

</td>
</tr>
</table>
</td>

<td width="30%" colspan="" class="ms-vb">
<table width="100%" border="0">
<tr>
<td width="20">
<img src="_layouts/images/CHNGCOL.GIF"></img>
</td>
<td width="40%">
<b>Document Owner:</b>
</td>
<td>
<xsl:value-of select="documentowner" />
</td>
</tr>
<tr>
<td width="20">
<img src="_layouts/images/ICODCC.GIF"></img>
</td>
<td width="40%">
<b>Document Status:</b>
</td>
<td>
<xsl:value-of select="documentstatus" />
</td>
</tr>
<tr>
<td width="20">
<img src="_layouts/images/CAT16.GIF"></img>
</td>
<td width="40%">
<b>Document Category:</b>
</td>
<td>
<xsl:value-of select="documentcategory" />
</td>
</tr>
<tr>
<td width="20">
<img src="_layouts/images/VERSIONS.GIF"></img>
</td>
<td width="40%">
<b>Document Version:</b>
</td>
<td>
<xsl:value-of select="documentversion" />
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td width="100%" colspan="2">
<font color="dodgerblue">
<b>Notes:</b> This item was <b>modified</b> on <xsl:value-of select="write" />
This item is <b>ranked</b> at <xsl:value-of select="rank" />/1000
This item <b>size</b> is <xsl:value-of select="size" />B
<br/><br/> <b>Highlight Summary</b> : <xsl:value-of select="hithighlightedsummary" />
</font>
</td>
</tr>
<tr>
<td width="100%" colspan="2">
<hr class="ms-consolehr"></hr>
</td>
</tr>
<xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
<tr>
<td width="100%" colspan="2" class="ms-vb">
<span ddwrt:amkeyfield="" ddwrt:amkeyvalue="string($XPath)" ddwrt:ammode="view" />
</td>
</tr>

</xsl:if>
</xsl:template>
</xsl:stylesheet>

Although this is a simple table, I hope this provides you a primer into how to modify your search results.

stsadm error when using createsiteinnewdb

Had a peculiar problem where a customer received errors when using the above command to provision a new suite collection.
The error was: stsadm: Value cannot be null. Parameter name: v1

It appears that this was entirely down to a change in SQL access rights for the service running WSS

Further investigation found the following full error in the logs:

stsadm: Value cannot be null. Parameter name: v1 Callstack: at System.Version.op_LessThan(Version v1, Version v2) at Microsoft.SharePoint.Administration.SPDatabase.EnsureSqlDatabase(SqlConnectionStringBuilder connectionString) at Microsoft.SharePoint.Administration.SPDatabase.SetDatabaseOptions(SqlConnectionStringBuilder connectionString, Dictionary`2 options, Boolean wYukon) at Microsoft.SharePoint.Administration.SPDatabase.Provision(SqlConnectionStringBuilder connectionString, SqlFile sqlFileId, String sqlSignaturePath, Dictionary`2 options) at Microsoft.SharePoint.Administration.SPContentDatabase.Provision() at Microsoft.SharePoint.Administration.SPContentDatabaseCollection.Add(SPContentDatabase database, Boolean provision, Boolean allowUpgrade, Boolean flushChan...

Ensuring that your custom master file is loaded on each new site created

The following code ensures that when a site is provisioned it uses your master file.

Once you have compiled it you will need to register the http module in the web.config file.

e.g.

<add name="ensuremaster" type="ensuremaster.ensuremaster,ensuremaster, Version=1.0.0.0, Culture=neutral, PublicKeyToken=e2afe595d5cd71e3" />

using System;

using System.Web;

using System.Web.UI;

using System.IO;

namespace ensuremaster

{

public class ensuremaster : IHttpModule

{

public void Init(HttpApplication context)

{

context.PreRequestHandlerExecute += new EventHandler(context_PreRequestHandlerExecute);

}

void context_PreRequestHandlerExecute(object sender, EventArgs e)

{

Page page = HttpContext.Current.CurrentHandler as Page;

if (page != null)

{

page.PreInit += new EventHandler(page_PreInit);

}

}

void page_PreInit(object sender, EventArgs e)

{

Page page = sender as Page;

if (page != null)

{

// Is there a master page defined?

if (page.MasterPageFile != null)

{

// only change the default.master files as those are the offenders

page.MasterPageFile = "/_catalogs/masterpage/samplests.master";

}

}

}

public void Dispose()

{

}

}

}

In order to remove 'Open in Windows Explorer' from the 'Actions Menu' for all users other than Website Owners

· Make a copy of the DefaultTemplate.ascx form the 12\templates\controltemplates\ directory.

· Rename the copy to CustomDefaultTemplate.ascx.

· Find the line ID="OpenInExplorer" (usually line 1812).

· Change PermissionsString="UseClientIntegration" to PermissionsString="ManageWeb" and save the file.

· Reset IIS.

WSS Emailing Itself -: x-mailer: Windows SharePoint Services (version 3)

I had a case recently where a client wanted to send access request emails to an email enabled document library. We encountered the problem of WSS not being able to handle the x-mail header.
A solution was to create a vbscript that fired when an email entered the 'Drop' folder of IIS mailroot and stripped out the problematic header.
Initial testing proved successfull and this has now been implemented.


solution
1. copy Smtpreg.vbs into the c:\inetpub\AdminScripts - smtpreg is available to download from MS.

2. create a vbs file from the script below and copy it to the AdminScripts folder (I named mine wss)

<SCRIPT LANGUAGE="VBScript">


Sub ISMTPOnArrival_OnArrival(ByVal iMsg, EventStatus )

if iMsg.Fields("urn:schemas:mailheader:x-mailer") = "Windows SharePoint Services (version 3)" then
iMsg.Fields.Delete("urn:schemas:mailheader:x-mailer")
iMsg.Fields.Update
end if

iMsg.DataSource.Save
EventStatus = 0
End Sub


</SCRIPT>


3. run the following commands to register the script

cd c:\inetpub\adminscripts
cscript smtpreg.vbs /add 1 OnArrival DeleteMsg CDO.SS_SMTPOnArrivalSink "mail from=*"
cscript smtpreg.vbs /setprop 1 OnArrival DeleteMsg Sink ScriptName "c:\Inetpub\AdminScripts\wss.vbs"

4. This will now catch all mail on arrival and remove the offending header.