header image troyscott.ca - powered by jigsaw

Modifying the View of an External Sharepoint List

by Troy Scott


If you have been following along in the last few posts,  I have created an External Content Type called BcsSalesForecast with the following operations:  

  • Read List
  • Read Item
  • Update

Then an External List was created called Sales Forecast 2010.  Up to now the main focus of the discussion has been around creating the External Content Type and the store procedures which are used as its data source.  However, if you currently view the list below its presentation as a lot to be desired.  First off, its sorted by ID and there are over 700 records with 30 per page which means that it could take awhile to find the record you are looking for.  

In order to make the list more “useable” it is possible to modify the view.   Before the view is modified a minor update is required to the data.  The Sales Forecast 2010 list is a weekly forecast application for 2010 and provides the previous years as a starting point (the data is actually from Adventure Works for 2008).    Ideally it would be nice to sort the data by week but I did not include a sort key as part of the original design.   But this can fixed by running the following UPDATE statement against the dbo.WeeklySalesForecast table:

This will  add a leading 0 to all weeks that are less then week 10 which will ensure the sorting by Week will work.  Now you can navigate to the Sales Forecast 2010 list and click on the List tab:


Next  click Modify View but do not click on Modify in SharePoint Designer (Advanced).  For this scenario SharePoint 2010 Designer is not required.  The changes to the list can be performed in the browser.  Change the First Sort Column from ID to WeekNumber:


Next set the First Group By Column to SalesRegion and the Second Group By Column to ProductCategory:


Change the number of items to display per page from 30 to 300 :


Hide the SalesRegion and ProductCategory since these are redundant because they are being used as Groups. Secondly the ID field can be hidden because it is just required as the unique identifier for the row:


(Note: the position of the columns may be different than what you see when you “Modify” the view)

Click OK to save the changes to the view and now look at the List:


As you can see, a few simple modifications to the list can make a significant difference in terms of the lists usability.