Second Life of a Hungarian SharePoint Geek

September 14, 2011

Conditional formatting of totals in a view

Filed under: Conditional formatting, SP 2010, Totals, Views — Tags: , , , — Peter Holpar @ 23:32

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.

image

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

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

image

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:

image

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

image

image

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:

image

Advertisements

1 Comment »

  1. Peter,
    Thanks a lot for this useful post! You are the Merlin of Sharepoint!

    Comment by Roman — September 19, 2011 @ 09:02


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: