Click here to Skip to main content
13,044,705 members (52,741 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


Posted 14 Mar 2014

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

, 14 Mar 2014
Rate this:
Please Sign up or sign in to vote.
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".
    <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: 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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

abin jaik
Web Developer
India India
I am Abin Jaik Antony, working as a Software Developer.My technology background area extends to MOSS 2007,Microsoft BI,.NET Framework etc.
Visit my blog for more details

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 14 Mar 2014
Article Copyright 2014 by abin jaik
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid