Conditional formatting of totals in a view

Applying conditional formatting for a list view is not hard at all using SharePoint Designer. Most of the samples on the web shows how to conditionally format rows or individual cells, but we can format total values either.

In this post I will continue to work on my former example about moving totals to the bottom of the view.

The goal is to apply a red background for the sum of actual numbers if it is below the sum of planned values, and for the maximal value of finished dates if it is over the maximum of due dates.

First, select the total you want to format, then from Conditional Formatting choose Format Selection.

Start the advanced condition editor through the Advanced… button, and apply the following XPath expression:

sum($Rows/@ActualNumber) < sum($Rows/@PlannedNumber)

Click OK, then set the red background by clicking Set Stlye, finally click OK once more.

SPD generates the following, quite complex code for this simple conditional formatting:

1. <xsl:template match="FieldRef[@Name='ActualNumber']" mode="aggregate" ddwrt:dvt_mode="body" ddwrt:ghost="">
2.   <xsl:param name="Rows" select="."/>
3.   <xsl:param name="GroupLevel" select="1"/>
4.   <td class="ms-vb2">
5.     <xsl:variable name="fieldName" select="@Name"/>
6.     <xsl:if test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]">
7.       <xsl:variable name="title">
8.         <xsl:choose>
9.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='COUNT'">
10.             <xsl:value-of select="'Count'"/>
11.           </xsl:when>
12.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
13.             <xsl:value-of select="'Sum'"/>
14.           </xsl:when>
15.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='AVG'">
16.             <xsl:value-of select="'Average'"/>
17.           </xsl:when>
18.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MAX'">
19.             <xsl:value-of select="'Maximum'"/>
20.           </xsl:when>
21.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MIN'">
22.             <xsl:value-of select="'Minimum'"/>
23.           </xsl:when>
24.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='STDEV'">
25.             <xsl:value-of select="'Std Deviation'"/>
26.           </xsl:when>
27.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='VAR'">
28.             <xsl:value-of select="'Variance'"/>
29.           </xsl:when>
30.           <xsl:otherwise>
31.             <xsl:value-of select="'Count'"/>
32.           </xsl:otherwise>
33.         </xsl:choose>
34.       </xsl:variable>
35.       <xsl:variable name="aggregateLevel">
36.         <xsl:choose>
37.           <xsl:when test="$GroupLevel = 1">.agg</xsl:when>
38.           <xsl:when test="$GroupLevel = 2">.agg2</xsl:when>
39.           <xsl:otherwise/>
40.         </xsl:choose>
41.       </xsl:variable>
42.       <xsl:variable name="aggregateName">
43.         <xsl:choose>
44.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='COUNT'">
45.             <xsl:value-of select="concat(@Name, '.COUNT', $aggregateLevel)"/>
46.           </xsl:when>
47.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
48.             <xsl:value-of select="concat(@Name, '.SUM', $aggregateLevel)"/>
49.           </xsl:when>
50.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='AVG'">
51.             <xsl:value-of select="concat(@Name, '.AVG', $aggregateLevel)"/>
52.           </xsl:when>
53.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MAX'">
54.             <xsl:value-of select="concat(@Name, '.MAX', $aggregateLevel)"/>
55.           </xsl:when>
56.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MIN'">
57.             <xsl:value-of select="concat(@Name, '.MIN', $aggregateLevel)"/>
58.           </xsl:when>
59.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='STDEV'">
60.             <xsl:value-of select="concat(@Name, '.STDEV', $aggregateLevel)"/>
61.           </xsl:when>
62.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='VAR'">
63.             <xsl:value-of select="concat(@Name, '.VAR', $aggregateLevel)"/>
64.           </xsl:when>
65.           <xsl:otherwise>
66.             <xsl:value-of select="concat(@Name, '.COUNT', $aggregateLevel)"/>
67.           </xsl:otherwise>
68.         </xsl:choose>
69.       </xsl:variable>
70.       <xsl:if test="not($title='')">
71.         <nobr>
72.           <b>
73.             <span>
74.               <xsl:attribute name="style">
75.                 <xsl:if test="sum($Rows/@ActualNumber) &lt; sum($Rows/@PlannedNumber)" ddwrt:cf_explicit="1">background-color: #FF0000;</xsl:if>
76.               </xsl:attribute>
77.               <xsl:value-of select ="$title"/>=
78.               <xsl:value-of select="$Rows/@*[name()=$aggregateName]" />
79.             </span>
80.           </b>
81.         </nobr>
82.       </xsl:if>
83.     </xsl:if>
84.   </td>
85. </xsl:template>

Fortunately we can make it a lot simpler, just should analyze the variables and conditions.

In our case the value of the fieldName variable is the name of the field, stored at the Name attribute, that is ActualNumber (it should be, as we applied our template with the filter FieldRef[@Name=’ActualNumber’]).

<xsl:variable name="fieldName" select="@Name"/>

To get the values used during the XSL transformations, we should apply some specific XSL templates, as I’ve done in my former post.

You can get the XML result returned by the query if you apply the following template:

1. <xsl:template match="/">
2.   <xmp>
3.     <xsl:copy-of select="*"/>
4.   </xmp>
5. </xsl:template>

This is the resulting XML. Note, that it contains the aggregated values in each rows, that is pretty redundant, but provides an easy way to access the totals:

1. <dsQueryResponse ViewStyleID="" BaseViewID="1" TemplateType="100" RowLimit="30">
2.   <Rows>
3.     <Row ID="1" PermMask="0x7fffffffffffffff" Attachments="0" Title="Item 1" FileLeafRef="1_.000" FileLeafRef.Name="1_" FileLeafRef.Suffix="000" FSObjType="0" Created_x0020_Date="0;#2011-08-31 22:57:36" Created_x0020_Date.ifnew="" FileRef="/Lists/TotalList/1_.000" FileRef.urlencode="%2FLists%2FTotalList%2F1%5F%2E000" FileRef.urlencodeasurl="/Lists/TotalList/1_.000" File_x0020_Type="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapall="icgen.gif||" HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapico="icgen.gif" ContentTypeId="0x0100AAC7CF7AEC7754468D8B7EBC03DED857" PlannedNumber="12" PlannedNumber.="12.0000000000000" ActualNumber="34" ActualNumber.="34.0000000000000" DueDate="8/18/2011" FinishedDate="9/3/2011" PlannedNumber.SUM="175" DueDate.MAX="9/2/2011" FinishedDate.MAX="9/3/2011" ActualNumber.SUM="145"></Row>
4.     <Row ID="2" PermMask="0x7fffffffffffffff" Attachments="0" Title="Item 2" FileLeafRef="2_.000" FileLeafRef.Name="2_" FileLeafRef.Suffix="000" FSObjType="0" Created_x0020_Date="0;#2011-08-31 22:58:07" Created_x0020_Date.ifnew="" FileRef="/Lists/TotalList/2_.000" FileRef.urlencode="%2FLists%2FTotalList%2F2%5F%2E000" FileRef.urlencodeasurl="/Lists/TotalList/2_.000" File_x0020_Type="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapall="icgen.gif||" HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapico="icgen.gif" ContentTypeId="0x0100AAC7CF7AEC7754468D8B7EBC03DED857" PlannedNumber="32" PlannedNumber.="32.0000000000000" ActualNumber="24" ActualNumber.="24.0000000000000" DueDate="9/2/2011" FinishedDate="8/25/2011" PlannedNumber.SUM="175" DueDate.MAX="9/2/2011" FinishedDate.MAX="9/3/2011" ActualNumber.SUM="145"></Row>
5.     <Row ID="3" PermMask="0x7fffffffffffffff" Attachments="0" Title="Item 3" FileLeafRef="3_.000" FileLeafRef.Name="3_" FileLeafRef.Suffix="000" FSObjType="0" Created_x0020_Date="0;#2011-08-31 22:58:27" Created_x0020_Date.ifnew="" FileRef="/Lists/TotalList/3_.000" FileRef.urlencode="%2FLists%2FTotalList%2F3%5F%2E000" FileRef.urlencodeasurl="/Lists/TotalList/3_.000" File_x0020_Type="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapall="icgen.gif||" HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon="" HTML_x0020_File_x0020_Type.File_x0020_Type.mapico="icgen.gif" ContentTypeId="0x0100AAC7CF7AEC7754468D8B7EBC03DED857" PlannedNumber="131" PlannedNumber.="131.000000000000" ActualNumber="87" ActualNumber.="87.0000000000000" DueDate="8/13/2011" FinishedDate="8/19/2011" PlannedNumber.SUM="175" DueDate.MAX="9/2/2011" FinishedDate.MAX="9/3/2011" ActualNumber.SUM="145"></Row>
6.   </Rows>
7. </dsQueryResponse>

There are a lot of references for $XmlDefinition, we can check its value by applying this template:

1. <xsl:template match="/">
2.   <xmp>
3.     <xsl:copy-of select="$XmlDefinition"/>
4.   </xmp>
5. </xsl:template>

And it is the output:

1. <View Name="{288E815A-F55D-49DC-BF6C-06F46C5A4719}" DefaultView="TRUE" MobileView="TRUE" MobileDefaultView="TRUE" Type="HTML" DisplayName="All Items" Url="/Lists/TotalList/AllItems.aspx" Level="1" BaseViewID="1" ContentTypeID="0x" ImageUrl="/_layouts/images/generic.png">
2.   <Query>
3.     <OrderBy>
4.       <FieldRef Name="ID"></FieldRef>
5.     </OrderBy>
6.   </Query>
7.   <ViewFields>
8.     <FieldRef Name="Attachments" Type="Attachments" FieldType="Attachments" DisplayName="Attachments" ID="67df98f4-9dec-48ff-a553-29bece9c5bf4"></FieldRef>
9.     <FieldRef Name="LinkTitle" Type="Computed" FieldType="Computed" DisplayName="Title" ID="82642ec8-ef9b-478f-acf9-31f7d45fbc31" ClassInfo="Menu" ListItemMenu="TRUE" ListItemMenuAllowed="Required" LinkToItemAllowed="Prohibited"></FieldRef>
10.     <FieldRef Name="PlannedNumber" Type="Number" FieldType="Number" DisplayName="PlannedNumber" ID="02ee2591-a1e4-4e18-b6d9-fe17eeca6758"></FieldRef>
11.     <FieldRef Name="ActualNumber" Type="Number" FieldType="Number" DisplayName="ActualNumber" ID="4cc7c15e-c05a-48fb-a95a-9e4b4e850928"></FieldRef>
12.     <FieldRef Name="DueDate" Type="DateTime" FieldType="DateTime" DisplayName="DueDate" ID="3f90a631-89dd-45c1-959a-e519c5d1b787"></FieldRef>
13.     <FieldRef Name="FinishedDate" Type="DateTime" FieldType="DateTime" DisplayName="FinishedDate" ID="df22e8d1-6220-410c-8ce9-d820ff471673"></FieldRef>
14.   </ViewFields>
15.   <RowLimit Paged="TRUE">30</RowLimit>
16.   <Aggregations Value="On">
17.     <FieldRef Name="PlannedNumber" Type="SUM"></FieldRef>
18.     <FieldRef Name="DueDate" Type="MAX"></FieldRef>
19.     <FieldRef Name="FinishedDate" Type="MAX"></FieldRef>
20.     <FieldRef Name="ActualNumber" Type="SUM"></FieldRef>
21.   </Aggregations>
22.   <Toolbar Type="Standard"></Toolbar>
23.   <List Direction="none" TemplateType="100" title="TotalList" description="" basetype="0" RootFolder="" version="4" name="{BBD560B6-5ED0-495D-AB88-AA1600584DD1}" moderatedlist="0" SendToLocationName="" SendToLocationUrl="" WriteSecurity="1" EnableMinorVersions="0" VersioningEnabled="0" ForceCheckout="0" WorkflowsAssociated="0" DefaultItemOpen="1" RecycleBinEnabled="1" ExternalDataList="0" OfficialFileNames="" EnableSyndication="1" enablecontenttypes="0"></List>
24. </View>

As you can see this is the XML definition for the view. Note, that the Aggregations node contains the type of totals per field in the Type attribute.

Based on these XMLs we can get the values for the variables used in the XSL. First, let’s check the title variable.

1. <xsl:variable name="title">
2.   <xsl:choose>
3.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='COUNT'">
4.       <xsl:value-of select="'Count'"/>
5.     </xsl:when>
6.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
7.       <xsl:value-of select="'Sum'"/>
8.     </xsl:when>
9.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='AVG'">
10.       <xsl:value-of select="'Average'"/>
11.     </xsl:when>
12.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MAX'">
13.       <xsl:value-of select="'Maximum'"/>
14.     </xsl:when>
15.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MIN'">
16.       <xsl:value-of select="'Minimum'"/>
17.     </xsl:when>
18.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='STDEV'">
19.       <xsl:value-of select="'Std Deviation'"/>
20.     </xsl:when>
21.     <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='VAR'">
22.       <xsl:value-of select="'Variance'"/>
23.     </xsl:when>
24.     <xsl:otherwise>
25.       <xsl:value-of select="'Count'"/>
26.     </xsl:otherwise>
27.   </xsl:choose>
28. </xsl:variable>

Since for the ActualNumber field the Type attribute is “SUM” the following condition will match:

1. <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
2.   <xsl:value-of select="'Sum'"/>
3. </xsl:when>

It means the value of the title is ‘Sum’.

1. <xsl:variable name="aggregateLevel">
2.   <xsl:choose>
3.     <xsl:when test="$GroupLevel = 1">.agg</xsl:when>
4.     <xsl:when test="$GroupLevel = 2">.agg2</xsl:when>
5.     <xsl:otherwise/>
6.   </xsl:choose>
7. </xsl:variable>

Value of the $GroupLevel variable is 0 (there is no grouping in our view) as we can check using this template:

1. <xsl:template match="FieldRef[@Name='ActualNumber']" mode="aggregate" ddwrt:dvt_mode="body" ddwrt:ghost="">
2.   <xsl:param name="Rows" select="."/>
3.   <xsl:param name="GroupLevel" select="1"/>
4.   <xmp>
5.     <xsl:copy-of select="$GroupLevel"/>
6.   </xmp>
7. </xsl:template>

So the value of the aggregateLevel variable is “”, thanks to the empty otherwise element.

From the following condition:

1. <xsl:variable name="aggregateName">
2.         <xsl:choose>
3.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='COUNT'">
4.             <xsl:value-of select="concat(@Name, '.COUNT', $aggregateLevel)"/>
5.           </xsl:when>
6.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
7.             <xsl:value-of select="concat(@Name, '.SUM', $aggregateLevel)"/>
8.           </xsl:when>
9.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='AVG'">
10.             <xsl:value-of select="concat(@Name, '.AVG', $aggregateLevel)"/>
11.           </xsl:when>
12.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MAX'">
13.             <xsl:value-of select="concat(@Name, '.MAX', $aggregateLevel)"/>
14.           </xsl:when>
15.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='MIN'">
16.             <xsl:value-of select="concat(@Name, '.MIN', $aggregateLevel)"/>
17.           </xsl:when>
18.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='STDEV'">
19.             <xsl:value-of select="concat(@Name, '.STDEV', $aggregateLevel)"/>
20.           </xsl:when>
21.           <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='VAR'">
22.             <xsl:value-of select="concat(@Name, '.VAR', $aggregateLevel)"/>
23.           </xsl:when>
24.           <xsl:otherwise>
25.             <xsl:value-of select="concat(@Name, '.COUNT', $aggregateLevel)"/>
26.           </xsl:otherwise>
27.         </xsl:choose>
28.       </xsl:variable>

the next will match:

1. <xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
2.   <xsl:value-of select="concat(@Name, '.SUM', $aggregateLevel)"/>
3. </xsl:when>

So the value of aggregateName will be ActualNumber.SUM.

Using the values determined above we can simplify the original XSL like this:

1. <xsl:template match="FieldRef[@Name='ActualNumber']" mode="aggregate" ddwrt:dvt_mode="body" ddwrt:ghost="">
2.   <xsl:param name="Rows" select="."/>  
3.   <td class="ms-vb2">
4.         <nobr>
5.           <b>
6.             <span>
7.               <xsl:attribute name="style">
8.                 <xsl:if test="sum($Rows/@ActualNumber) &lt; sum($Rows/@PlannedNumber)" ddwrt:cf_explicit="1">background-color: #FF0000;</xsl:if>
9.               </xsl:attribute>
10.               Sum=<xsl:value-of select="$Rows/@ActualNumber.SUM" />
11.             </span>
12.           </b>
13.         </nobr>
14.   </td>
15. </xsl:template>

We can also use the following condition for the same result:

$Rows/@ActualNumber.SUM &lt; $Rows/@PlannedNumber.SUM

BTW, the aggregated values are displayed in SPD as well:

Even from the basic condition editor, after selecting More Fields…

Note: If you would like to overwrite the title for the total, simply replace “Sum=” in the XSL template with the new value, like “Total:”.

Having the simplified version, it is quite easy to create the required conditional formatting for the date totals:

1. <xsl:template match="FieldRef[@Name='FinishedDate']" mode="aggregate" ddwrt:dvt_mode="body" ddwrt:ghost="">
2.   <xsl:param name="Rows" select="."/>
3.   <td class="ms-vb2">
4.     <nobr>
5.       <b>
6.         <span>
7.           <xsl:attribute name="style">
8.             <xsl:if test="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Rows/@FinishedDate.MAX))) &gt; ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Rows/@DueDate.MAX)))" ddwrt:cf_explicit="1">background-color: #FF0000;</xsl:if>
9.           </xsl:attribute>
10.           Maximum=<xsl:value-of select="$Rows/@FinishedDate.MAX" />
11.         </span>
12.       </b>
13.     </nobr>
14.   </td>
15. </xsl:template>

The following image illustrates the result of the conditional formatting of the aggregated values: