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">
<FieldRef ID="{E35D236A-0694-4EB2-8076-512C5603AACD}" Name="RiskProbability" />
<FieldRef ID="{6008A835-EA39-49AD-BC2E-0C8501042821}" Name="RiskImpact" />

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="."/>
<xsl:attribute name="style">
<xsl:if test="$thisNode/@RiskScore &lt;= '30'" ddwrt:cf_explicit="1">background-color: #FFFF00;</xsl:if>
<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 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:value-of select="$thisNode/@*[name()=current()/@Name]" />

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!!!

Continue Reading »

In my last SharePoint project, the requirement was to bind the folder structure of a document library to a tree view control. There are couple of blog’s available on the web, which talk about looping through folders and files recursively of a document library and adding them one after the other to the tree node of a tree view control. I think this is a costly affair. Instead the better solution would have been to get the results from the document library at one go in the form of a data table or a data set and bind them directly to the tree view control. This approach is not straight forward but is not a costly affair unlike the previous approach.

So the question is how do we achieve this? Below is the list of step’s to achieve the above requirement.

  • CAML query to get all the folders in a document library.
  • Convert the structured data table into hierarchical data table. I couldn’t have completed what I wanted to without referring this post http://www.codeproject.com/KB/webforms/aspnettreeviewbind.aspx.
  • Binding the tree view control to the converted hierarchical data table.

Recently, I had to hand-code a complex dynamic CAML (Collaborative Application Markup Language) query. U2U CAML Builder has always come to my rescue in building queries. I never had to understand the pattern behind the CAML query. But this time around, U2U CAML Builder misguided me in a sense. Let see how.

The document library had couple of columns, one of which was a DateTime Column (MeetingDate). The requirement was to get list items from the document library which are in a specific year(s).


 Now, What if I want to get the items in the year 2001, 2008 and 2010 only? We don’t have IN() operator in CAML as in SQL. So the question is how do we retrieve specific records based on the year.

As we don’t have the IN() operator, I have used the below logic.

     <FieldRef Name=’MeetingDate’ />
     <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2010-01-01T20:17:06Z</Value>
     <FieldRef Name=’MeetingDate’ />
     <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2010-12-31T20:17:06Z</Value>

The logic is Meeting Date >= 01/01/2001 AND Meeting Date  <= 12/31/2001.

This is a static query built using U2U CAML Builder. It works great in this scenario. Now, if I want to get list items in the year 2001 and 2008 you will see U2U CAML builder spits out in-correct query for you. This is how it looks.

            <FieldRef Name=’MeetingDate’ />
            <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2008-01-01T20:17:06Z</Value>
               <FieldRef Name=’MeetingDate’ />
               <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2008-12-31T20:17:22Z</Value>
                  <FieldRef Name=’MeetingDate’ />
                  <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2001-01-01T20:18:57Z</Value>
                  <FieldRef Name=’MeetingDate’ />
                  <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2001-12-31T20:19:09Z</Value>

This query will not work. Instead the CAML query should have been like below.

      <FieldRef Name=’MeetingDate’ />
      <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2008-01-01T00:00:00Z</Value>
      <FieldRef Name=’MeetingDate’ />
      <Value IncludeTimeValue=’FALSE’ Type=’Text’>2008-12-31T00:00:00Z</Value>
    <FieldRef Name=’MeetingDate’ />
    <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2001-01-01T00:00:00Z</Value>
    <FieldRef Name=’MeetingDate’ />
    <Value IncludeTimeValue=’FALSE’ Type=’Text’>2001-12-31T00:00:00Z</Value>

After spending many hours figured out that I will have to code differently depending on number of year(s) as input.

This is how it goes.

# If the input given is just one year i,e 2001

  customQuery.Append(“<And><Geq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’DateTime’>”+  SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“01/01/”+year)) + “</Value></Geq><Leq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’Text’>”+ SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“12/31/”+year)) +“</Value></Leq></And>”);

 # If the input given is exactly two years i.e 2001 and 2008

foreach (string year in documentYear.Split(‘;’))
  if (!string.IsNullOrEmpty(year))
    customQuery.Append(“<And><Geq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’DateTime’>”+ SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“01/01/” + year)) + “</Value></Geq><Leq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’Text’>” + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“12/31/” + year)) + “</Value></Leq></And>“);

# If the input is more than two years

int counter = 1;
StringBuilder queryBuilder = new StringBuilder();
foreach (string year in documentYear.Split(‘;’))
   if (totalYearsSelected – 2 >= counter)
       customQuery.Append(“<Or><And><Geq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’DateTime’>”+ SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“01/01/” + year)) + “</Value></Geq><Leq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’Text’>” + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“12/31/” + year)) + “</Value></Leq></And>”);
      if (!string.IsNullOrEmpty(year))
         queryBuilder.Append(“<And><Geq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’DateTime’>”+ SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“01/01/” + year)) + “</Value></Geq><Leq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’Text’>” + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“12/31/” + year)) + “</Value></Leq></And>”);
customQuery.Append(“<Or>”+ queryBuilder.ToString() + “</Or>”);

NOTE: I had a multi-select listbox for meeting date. So, I wasn’t sure in advance the number of years as selected input. If your requirement is the same then you will have to use the queries for all the 3 different scenarios in your code.

I have been planning to blog for quite sometime now but have been reluctant. Finally, decided to get by foot down and pay the community back. My focus is to share information of anything and everything about SharePoint.

When it comes to SharePoint, Microsoft always talks in terms of Workloads the pie chart we are used to. Here, I will be talking about the new features with SharePoint 2010. This is just the brief introduction to the new features of SharePoint 2010

So, Let’s get started with SharePoint 2010 New Features.


  • Sites
    • Adoption of Office Ribbon User Interface
      • Ribbon is contextual(The options in the ribbon keeps changing depending on the item selected. If a list is selected it has different options and if a list item is selected it has other set of options )
      • AJAX based (No more post backs, In SharePoint 2007 if a particular action is done on the page the entire page refreshes but now because of the Modal dialog boxes no complete refresh)
      • Provides extensible framework (One could add custom actions in the ribbon)
    • Easy Web Editing
      • Site is now collection of pages (In SharePoint 2007, a Team Site Template did not have Pages library as Publishing Site Template. But now even Team Site Template ‘Site Pages’ library allowing developer’s to add custom pages to Team Site)
      • Preview capability
      • Silver light web part (SharePoint 2010 provides OOTB silver light web part. Silver light media web part is also available OOTB)
      • Import a theme from PowerPoint deck to SharePoint site
      • Auto Save (Keeps saving the content of a page intermittently without user interaction)
      • Ctrl + Z WORKS!!!!!!!!
    • Audience Targeting
      • Targets: documents, events, web parts, custom list items and even entire site
      • Audience Object Model (Rules)
    • List and List Item Validation(Now validations can be applied to a particular field or the entire row using calculated columns)
      • Ex: Start Date cannot be greater than End Date
      • Unique Column (In SharePoint 2007 there was no unique column concept OOTB, if i wanted to column as unique then i will have to handle it explicitly creating custom events but now we get it OOTB with SharePoint 2010)
    • Cross-browser support and improved mobile experience
      • Tier1 Support: Firefox and IE 7.0 and above
      • Tier2 Support: Safari
    • Off-line experience with SharePoint Workspace 2010 (initially called Groove)(Entire SharePoint 2010 site can be taken offline and the changes made will sync when online)
      • Caches the data when offline
      • Synchronizes the data back when online
    • Multilingual User Interface (MUI)
    • Standard and Accessibility compliance
      • Doc type : XHTML 1.0 (Unfortunately, SharePoint 2007 did follow any Doc Type but SharePoint 2010 is planning to follow XHTML 1.0 strict)
      • WCAG 2.0 AA (for disabilities)
  • Communities
    • My Sites and Knowledge Mining(Microsoft has invested a lot in Social Computing with the new version of SharePoint)
      • MySpace and Facebook
      • MySites and User Profiles
      • Status updates and activity feed
      • Knowledge Mining (Keywords and Tagging)
    • Social Tagging
      • Social Tagging (Social tagging and Enterprise tagging)
      • Bookmarks (replaces MyLinks 2007 feature)
      • Feedback (rate and comment on activities)
      • Note board
    • Enhanced blogs and wikis
      • Rich Editor (CEWP) – supports cross platform browsers
      • Personal blogs linked to My Sites
      • External tools for blogging (Microsoft Word and Window Live Writer)
  • Content (Document Management, WCM, ECM & Records Management)
    • Metadata Everywhere(Now in SharePoint 2010 an administrator  or end-user could add a tag or a keyword to the content)
      • Managed Metadata (Managed Metadata Services; Taxonomies & folksonomy; Term Group, Term Sets and Terms;) – Metadata can be re- used cross farm environment
      • Managed Metadata Column
    • Document Set (It is a special kind of content type which holds multiple documents like word, excel, power point etc)
    • Document ID (Every document now has a unique identifier attached to it. Doing so if a document is moved from one location to another it can still be tracked)
    • In-place records management (In SharePoint 2007 i can do Records Management in only Records Center Site Template only now that is not the case as Records Management is added as a feature which i could activate on any site template)
    • Advanced Routing
    • Enterprise Content Type
    • Web Analytics Service Application (A tool which gives information of the site usage, site traffic, search being used and many more)
  • Search (Entry level: Search Server 2010 Express, Infrastructure , High-End: FAST Search)
    • Searches both structured(SQL Server) and un-structured(LOB Systems) data
    • Improved User Experience
      • Relevance Ranking
      • Core Search Web Part Experience
      • Save a search
      • Alert
      • More expressive queries
    • People and Expertise Search/Social Search
      • Address book-style name lookup (name, title, name of organization)
      • Phonetically similar words (Synonymy matching)
      • Wildcard search (Ex: Robert, Bob)
      • FAST search for thumbnails, image
    • Deployment and manageability
    • Extensibility (Now with SharePoint 2010 OOTB search web parts can be extended by inheriting the existing web parts)
  • Insights
    • Excel Services (Provides Excel Calculation Services and Excel Rendering Services on the Server)
      • JSDOM (Java Script Object Model)
      • Rest API
      • Data Source: SharePoint Lists, Analysis Services, SQL Server
    • Performance Point Services (Enterprise CAL)
      • Score cards and Dashboards
      • Decomposition Tree (slicing and dicing)
      • Data Source: SharePoint Lists, Analysis Services, SQL Server, Excel Services, Visio Services
    • Visio Service
      • Data Sources: SharePoint Lists, Analysis Services, SQL Server, Excel Service
    • Chart Web Parts & Status Indicators  (3 Dimensional Graphs)
    • Access Services
  • Composites (BCS)- External Content Types and External Lists
    • Enhanced end-user tools for automation & design (BCS applications can be created using either SharePoint Designer or VS 2010. VS 2010 will be used for creating custom external content types.)
    • Read-Write interaction with LOB data (In SharePoint 2007 there is no way i could write data back to LOB systems like SAP, Siebel, CRM etc but now in SharePoint 2010 it is possible)
    • Fast and secure solution deployment

As this is my first blog i would appreciate if you guys come back with your feedback. Post in your suggestions or corrections and will make the changes accordingly.