Correctly displaying Currency within an External List (using BCS)

The Problem

If you are attempting to display a currency field in a SharePoint external list, one that is defined as 'money' datatype in sql server, you will loose the ability to display cents for those records that do not have cent values.  So, for example, this will display correctly:

124.55

This will not:

124.00

The first is displayed as:

$124.55

The second:

$124

The second value drops the two 0's that follow the dollar amount.  Our clients are used to seeing currency with the cents included, even when they are 0, so we have to handle this specifically within the markup that makes up the business list.

The Solution

Open the page that has the external list embedded in SharePoint designer, edit mode.

Locate the markup that defines the row view fields (not the header fields, they are similar).  This markup is identified by this tag:

<xsl:template name="dvt_1.rowview">
Locate the markup that displays your currency fields, something like:

<td class="ms-vb">
        $<xsl:value-of select="@Gross_Pay" />
</td>

Update the select field to include the following formatting information:

$<xsl:value-of select="format-number(@Gross_Pay, '#,###,###.00')" />

Save your changes.  Update other currency fields if you have multiples.  This will display the currency in the correct format, the cents will show up if they have a value, if they are blank, they will be replaced with two 00's.