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, "
<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.