Formatting Date, Number Data on BCS OOB Business Data List Webpart

Recently I came across an interesting issue/requirement with our Business Data List WebPart. The issue is "Data fetched from an External datasource via BCS (External Content Type) needs to be displayed on OOB Business DataList Webpart with proper formatting and sorting". Here, by formatting, it means that numbers should be displayed like XXX,XXX or X,XXX and date should display in MM/DD/YYYY format without time part.

From the first look of it, we can easily achieve this by doing a formatting from the database side. Means for a date data, it can be done like:

SELECT CONVERT(varchar(10),GETDATE(),3) + ' ' + CONVERT(varchar(10),GETDATE(),8)

and for numbers, it can be done like:

CONVERT(varchar(50), CAST(1112 AS money), 1)

Both the above approaches will work perfectly to solve the "formatting" requirement, but fails for "sorting". Because in both cases, data is converted to type "string" so sorting will fail to work.

The same issue will occur if we try to solve it in any middle tier like WCF or .NET BCS Provider.

In the above scenario, to accomplish the requirement of sorting & formatting, we don't require to "CONVERT" the data from its base type to "string". Let the data (date or number) come to SharePoint in its base type (DateTime or INT, DECIMAL, etc). From the place where it displays, we can change its formatting even if it is an Out-Of-Box Business Data List Webpart".

Yes. We can do it with the help normal "XSLT" functions WITHOUT opening the SharePoint Designer.

Follow the below mentioned steps to crack it out:

• Configure the OOB Business Data List Webpart with our External Content Type (ECT) and View. This is a regular step to display External Data. After this step, we can see the data listing without the formatting.
• In this step, we need to take the existing XSL styles of the data listing webpart. For this, we need to do the "Edit Webpart" and from the Webpart properties toolbox, find the "XSL Editor" button. Click on it, it will pop up the XSL style. Copy the whole XSL and paste it to a Visual Studio instance (or any other good editor).
• Find the XSL parameter which we need to format. Assume it's a number which needs to be formatted like "#,###". So here, we need to apply the XSLT function, "format-number".
  XML

  <xsl:value-of select="format-number(@AmountUSD, '#,###')" />

  For the date formatting, we can use like this:

  XML

  <xsl:value-of select="ddwrt:FormatDateTime(@ActivityDate, 1033, 'MM/dd/yyyy')" />
  

• Take the edited XSL & paste it back to the XSL Editor box on the Webpart properties toolbox. Save the XSL and Press OK on the webpart toolbox. You can see the formatted data.

Note: Using this approach, it will perfectly format listing data on the BCS Out of the box Business Data list web part without doing any custom scripts or code, but the data on the filter drop downs will not get formatted.