Creating Limit Filters in SharePoint Designer 2010

In this walk through we are going to show you how to create Limit Filters for your Business Connectivity Services External Content Types in SharePoint Designer 2010. These filters will let limit the amount of records being returned by your back end data source which will not only improve performance, but also make your users lives easier as they have less data to work with and process.

One important thing to note is that a Limit Filter on it’s own will just limit the items returned, this means that without another filter type you can only access a subset of your data. For example if you want to limit the amount of Products returned by a query to 100, you would add a Limit Filter and add another such as a Wildcard Filter, this will mean you will get a maximum of 100 Products which match the Wildcard filter returned. For information on how to add a Comparison or Wildcard Filter check out our blog post here: Creating Comparison and Wildcard filters for BCS in SharePoint Designer 2010


  • SharePoint Designer 2010
  • A Data Source – We will be using the ‘AdventureWorks’ Products table

How to..

  1. Open SharePoint Designer
  2. Connect to your SharePoint Site
  3. Click on ‘External Content Types’ from the Site Objects list

    View External Content Types

  4. Click ‘External Content Type’ from the ‘New’ section of the ribbon
  5. Give your External Content Type a meaning full name – You just need to click on the existing text to edit

    Give your External Content Type a good name  –> External Content Type with the name set

  6. Click the link in the External Content Type Operations section to ‘discover external data sources and define operations’

    Add a new connection

  7. Click ‘Add Connection’ and choose ’SQL Server’
  8. Enter your connection credentials and click OK

    Enter the connection details

  9. Expand the tree view to show the ‘Products’ Table

    Expand to see your tables

  10. Right Click on the Table and choose ‘New Read List Operation’
  11. Change the ‘Operation Name’ and ‘Operation Display Name’ to “All Products and Information”, Click Next
  12. You will see that SharePoint Designer gives you a warning letting you know you should add a Limit Filer

    SharePoint Designer warns you about creating a Limit Filter

  13. On the ‘Filter Parameters Configuration’ screen click ‘Add Filter Parameter’

    Add a Filter Parameter  

  14. When the properties section display’s change the ‘Data Source Element’ to ProductID and click on ‘(Click to Add)’

     Map the Data Source Element

  15. Enter the Filter Information as the following. We are going to create a Limit Filter on the Product ID. A Limit Filter lets you choose the number of rows you want returned from the database

     Create Limit Filter

  16. Click ‘OK’
  17. Change the Default Value to 100 – 100 rows will be returned from the Database

    Set the default value

  18. *As per the note at the top of the page, normally you would add another Filter Type here*
  19. Click Finish
  20. Click ‘Save’

    Save the External Content Type

  21. The ECT will be saved to the BDC Metadata Store

    External Content Type will be saved to BDC Metadata Store

  22. Once the document has been saved we can navigate to our SharePoint Site
  23. Add a ‘Business Data List’ Web Part to your page

    Add Business Data List Web Part

  24. Click on the icon to bring up the ECT Picker
  25. Select the AdventureWorks Products ECT
  26. Click ‘OK’
  27. The web part will display the Product information, if you page through the results you will notice that it will only go to item 100.

    Paging of data is limited