XSLT Variables and Sum with Salary and null values

I had a lot of issues attempting to get an XSLT data View Webpart to sum a nodeset of a number column that is set to a salary format and contained empty (null) values.  The following is also a description of how i have come to really enjoy creating XSL variables <xsl:variable> to create what I wish to display first.  The variables are absolutely necessary when you wish to do sums or counts on nodesets that are subsets of the entire rowset, such as splitting up your display into groups of dashboard values due to a status or a category field.

I like to strip out any templates and variables I do not need.  I also rename the template names so they are descriptive; especially if I need to add more templates later.

  <xsl:call-template name="ROMDashboard"/>
 </xsl:template>

 <xsl:template name="ROMDashboard">
  <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
  <xsl:variable name="RowCount" select="count($Rows)"/>
  <xsl:variable name="RowsCapturedOnTask" select="$Rows[normalize-space(@StatusFlag)='Captured / On Task']"/>
  <xsl:variable name="RowsInProgress" select="$Rows[normalize-space(@StatusFlag)='In Process']"/>
  <xsl:variable name="RowsCancelledTerminated" select="$Rows[normalize-space(@StatusFlag)='Cancelled / Terminated']"/>
  <xsl:variable name="CountCapturedOnTask" select="count($RowsCapturedOnTask)"/>
  <xsl:variable name="CountInProgress" select="count($RowsInProgress)"/>
  <xsl:variable name="CountCancelledTerminated" select="count($RowsCancelledTerminated)"/>
  <xsl:variable name="PercentCapturedOnTask" select="$CountCapturedOnTask div $RowCount"/>
  <xsl:variable name="PercentInProgress" select="$CountInProgress div $RowCount"/>
  <xsl:variable name="PercentCancelledTerminated" select="$CountCancelledTerminated div $RowCount"/>
  <xsl:variable name="SumCapturedOnTask" select="sum($RowsCapturedOnTask/@Estimated_x0020_Value.[string-length(.) &gt; 0])" />
  <xsl:variable name="SumInProgress" select="sum($RowsInProgress/@Estimated_x0020_Value.[string-length(.) &gt; 0])" />
  <xsl:variable name="SumCancelledTerminated" select="sum($RowsCancelledTerminated/@Estimated_x0020_Value.[string-length(.) &gt; 0])" />


<!-- This is the end of the variable section.  Note how I use cascading variables to build my nodesets and reuse them later to further perform analysis -->


<!--  The following is the output section, which is pretty standard and easy to follow because all of the tricky formulas are used in the variables and the output is styled using css, the css follows at the end  -->

  <div class="BannerBG" style="padding:0px">
   <table width="100%">
    <tr>
     <td colspan="3" style="text-align:center;" valign="top">
      <span><img src="/combat/customersolutions/code/icons/dollar-icon_48.png" alt="" class="style1"/></span>
      <span class="BannerText">ROM Summary</span>
     </td>
    </tr>
    <tr valign="top">
     <td class="Center SectionHeadingCell" width="33.3%">
      <span class="SectionHeadingText">Captured / On Task</span>
     </td>
     <td class="Center SectionHeadingCell" width="33.3%">
      <span class="SectionHeadingText">In Process</span>
     </td>
     <td class="Center SectionHeadingCell" width="33.3%">
      <span class="SectionHeadingText">Cancelled / Terminated</span>
     </td>
      <xsl:text xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" ddwrt:nbsp-preserve="yes" disable-output-escaping="yes">&amp;nbsp;</xsl:text>
     </tr>
    <tr>
     <td class="Center">
      <span class="DataNumberText">
       <xsl:value-of select="$CountCapturedOnTask"/>
      </span>
      <span class="PercentNumberText">
       (<xsl:value-of select="format-number($PercentCapturedOnTask, '#,##0%;-#,##0%')"/>)
      </span>
      <span class="ValueText">
       <xsl:value-of select="format-number($SumCapturedOnTask, '$#,##0;-$#,##0')" />
      </span>
     </td>
     <td class="Center">
      <span class="DataNumberText">
       <xsl:value-of select="$CountInProgress"/>
      </span>
      <span class="PercentNumberText">
       (<xsl:value-of select="format-number($PercentInProgress, '#,##0%;-#,##0%')"/>)
      </span>
      <span class="ValueText">
       <xsl:value-of select="format-number($SumInProgress, '$#,##0;-$#,##0')" />
      </span>
     </td>
     <td class="Center">
      <span class="DataNumberText">
       <xsl:value-of select="$CountCancelledTerminated"/>
      </span>
      <span class="PercentNumberText">
       (<xsl:value-of select="format-number($PercentCancelledTerminated, '#,##0%;-#,##0%')"/>)
      </span>
      <span class="ValueText">
       <xsl:value-of select="format-number($SumCancelledTerminated, '$#,##0;-$#,##0')" />
      </span>
     </td>
    </tr>
   </table>
  </div>
 </xsl:template>

CSS:
(This should be placed in a .css file and attached as a link (preferred) or used inline in a style section on the page)

/* DASHBOARD */
.Center {
 text-align:center;
}
.DashboardTitleText {
 color:teal;
 font:normal normal 28px calibri;
}
.SectionHeadingCell {
 vertical-align:middle;
 background-color:#212121;
 border:1px #212121 solid;
}
.SectionHeadingText {
 color:#99CCFF;
 font:normal normal 20px calibri;
 padding:5px 0px 5px 5px;
}
.DataNumberText {
 color:#DDDDDD;
 font:normal normal 36px calibri;
}
.PercentNumberText {
 color:#666666;
 font:normal normal 26px calibri;
 padding-left:15px;
}
.ValueText {
 color:#008400;
 font:normal normal 26px calibri;
 padding-left:15px;
}
.ContentBG {
 background-color:#F8F8F8;
}

Comments

Popular posts from this blog

SharePoint Designer 2013 Approval Workflow with Comments

Change SharePoint server hostname and Web Application Names

The Timer Service Failed to Recycle