Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

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

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

SharePointers,

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 achieve 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.

License

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

Share

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 http://www.abinjaik.com

Comments and Discussions

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