I create a new external list via a new external content type SQL .

I retreive my data and i can display it.

BDC Service allows us to view 2000 items .

1st Issue:

I can filtered through a filter to limit 2000. However I'd like to display the 2000 last entry in the SQL database and I can not. It takes me the first 2,000 entries .

Is it possible to view the last 2000 entries in the SQL database ?

Edit: This problem came from my SQL base. SharePoint Designer Filter do what i want know.

2nd Problem:

He retrieves a System.Decimal type column , my values ​​are only integers, but It displays a value with ten decimal 0 ( 20.000000000000000000 ) .

Is it possible to limit the number of 0 it displays ?

Thank you in advance, EGuidez

2 Answers 2


Solution for 2nd Problem:

I guess your database table column type as decimal(18, 20). If you correct it into decimal(18, 0) then you will not get decimal value in suffix.

Solution in SharePoint

By default external list have Read List.aspx view.

By editing view in SharePoint Designer you can control the decimal value.

Step 1: Open the list in SharePoint designer

Step 2: click the view/create new view and open the view page in Advanced Edit mode.

Step 3: Select the column you need to control and click formula ribbon button under Filter, Sort & Group tab.

Step 4: Add formula substring-before($thisNode/@*[name()=current()/@Name],'.')

After above update you will found following XSLT code <xsl:value-of select="substring-before($thisNode/@*[name()=current()/@Name],'.')"/> in code view.

Screen shot Update:

Open External List in designer and click on Read List.aspx view page see below

enter image description here

Next view the page in Design View or Split as shown below

enter image description here

Now select your column by click the TD of your column as shown in image then click the formula as shown in picture.

  • I selected : <FieldRef Name="Quantity"/> in my <view/> But i can't see Filter,Sort & Group Tab ... I'm in advanced edit mode
    – EGuidez
    Aug 19, 2015 at 8:12
  • You Correct in Advanced edit mode you not allowed to access Filter, Sort & Group Tab. Select the field in design mode and then try it out Aug 19, 2015 at 10:37
  • I took a screen. I can't see Filter,Sort & Group tab. Maybe i don't understand ... I haven't activated "Advanced Edit mode" in my screenshot.
    – EGuidez
    Aug 19, 2015 at 12:41
  • Ok fine now see the updated answer above. Aug 19, 2015 at 13:21
  • Ok, we have a problem. Description of the change.SharePoint Designer 2010 has three views for editing HTML and ASPX pages: Code view, Design view, and Split view. Design view and Split view are removed from SharePoint Designer 2013.
    – EGuidez
    Aug 19, 2015 at 13:39

In my BDC Model,

I added "cast([Quantity] as int) as" before [Quantity] : enter image description here

And it display correctly Quantity as an int: enter image description here

Problem Solved :D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.