CodeProject, Sharepoint, Technical Posts

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) need 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 acheive this by doing  a formatting from 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 fails 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 dont 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 display 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 its a number which need to formatted like “#,###”. So here we need apply the XSLT function, “format-number“.

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

For the date formatting we can use like this

 <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 :  By  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.

1 thought on “Formatting Date, Number Data on BCS OOB Business Data List Webpart”

  1. That has been very helpful and works. However, since I cannot create grouped entries with a business data List Webpart, I created an external list connecting to a database. Now I want to edit the formatting of some numbers and tables (right aligning that sort of thing.) Looking at the xls in SharePoint designer 2013, I feel a bit out of league. Would you have any pointers how to achieve simple formatting (decimal Points, decimal delimiter and alignement within tabs there. Thank you for your help.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s