Feeds:
Posts
Comments

Archive for January, 2012


Requirement: To color a SharePoint calculated column based on conditions. As per the below image there are 3 columns which are of our interest and they are Probability (Integer), Impact (Integer) and Risk Score (Computed).

RISK SCORE (Probability * Impact) COLOR
>70 Red
>30 and <=70 Orange
<=30 Yellow

Approaches: One can address this requirement in ‘n’ different ways. Below is the list of approaches which I could think of.

1)      Using SharePoint Designer

2)      Using XSL

3)      Using Object Model

As a developer, I would certainly not choose the first one as changes made to the SharePoint page will un-ghost it. I would rather choose between options 2 and 3 based on the situation. Here I am going to discuss option 2.

Solution: Steps in creating a colored SharePoint column based on conditions:

1)      Create a SharePoint List Definition.

2)      Create XSL and push it to LAYOUTS\XSL.

3)      Point Default View to the XSL file.

Step 1) Create a SharePoint List DefinitionBelow is the code snippet for the columns Probability, Impact and Risk Score as per our requirement.

<Field ID="{e35d236a-0694-4eb2-8076-512c5603aacd}" Name="RiskProbability" StaticName="RiskProbability" DisplayName="Probability" Group="MDP Site Columns" Type="Integer" Percentage="FALSE" Required="TRUE" />
<Field ID="{6008a835-ea39-49ad-bc2e-0c8501042821}" Name="RiskImpact" StaticName="RiskImpact" DisplayName="Impact" Group="MDP Site Columns" Type="Integer" Percentage="FALSE" Required="TRUE" />
<Field ID="{b23a007e-3cc2-4ea2-baf7-0378d1aa2c07}" Name="RiskScore" StaticName="RiskScore" DisplayName="Risk Score" Group="MDP Site Columns" Type="Calculated" ResultType="Integer" ReadOnly="True">
<Formula>=RiskProbability*RiskImpact</Formula>
<FieldRefs>
<FieldRef ID="{E35D236A-0694-4EB2-8076-512C5603AACD}" Name="RiskProbability" />
<FieldRef ID="{6008A835-EA39-49AD-BC2E-0C8501042821}" Name="RiskImpact" />
</FieldRefs>
</Field>

Step 2) Create XSL and push it to LAYOUTS\XSLCreate RiskScore.xsl and deploy it to LAYOUTS\XSL in 14 hive. The XSL will look like below.

<xsl:stylesheet xmlns:x=http://www.w3.org/2001/XMLSchema xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" 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" xmlns:o="urn:schemas-microsoft-com:office:office">
<xsl:include href="/_layouts/xsl/main.xsl"/>
<xsl:include href="/_layouts/xsl/internal.xsl"/>
<xsl:param name="AllRows" select="/dsQueryResponse/Rows/Row[$EntityName = '' or (position() &gt;= $FirstRow and position() &lt;= $LastRow)]"/>
<xsl:param name="dvt_apos">'</xsl:param>
<xsl:template name="FieldRef_body.RiskScore.RiskScore" ddwrt:dvt_mode="body" match="FieldRef[(@Name='RiskScore') and @Name='RiskScore']" mode="body" ddwrt:ghost="" xmlns:ddwrt2="urn:frontpage:internal">
<xsl:param name="thisNode" select="."/>
<span>
<xsl:attribute name="style">
<xsl:if test="$thisNode/@RiskScore &lt;= '30'" ddwrt:cf_explicit="1">background-color: #FFFF00;</xsl:if>
</xsl:attribute>
<span>
<xsl:attribute name="style">
<xsl:if test="$thisNode/@RiskScore &gt; '30' and $thisNode/@RiskScore &lt; '70'" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" ddwrt:cf_explicit="1">background-color: #FAAC32;</xsl:if>
</xsl:attribute>
<span>
<xsl:attribute name="style">
<xsl:if test="$thisNode/@RiskScore &gt;= '70'" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" ddwrt:cf_explicit="1">background-color: #FF0000;</xsl:if>
</xsl:attribute>
<xsl:value-of select="$thisNode/@*[name()=current()/@Name]" />
</span>
</span>
</span>
</xsl:template>
</xsl:stylesheet>

Hand-coding XSL is a difficult job so use SharePoint Designer (http://www.sharepoint4developers.net/en-nz/post/programmatically-setting-conditional-formatting.aspx)  

Step 3) Point Default View of the List Definition to the new created XSL fileOpen Schema.xml file of the list definition and change XslLink of the default view from main.xsl to RiskScore.xsl.

<XslLink Default="TRUE">RiskScore.xsl</XslLink>
That's it!!!
Advertisements

Read Full Post »