Business Connectivity Services improved a lot in SharePoint 2013 mainly with the support for OData as a supported external data source. Other improvements included the support of external events and Alerts within a SharePoint External List, as well as the ability to deploy an External Content Type as an app.
One of the drawbacks though has been caused by the dropping of the Design view within SharePoint Designer 2013. In SharePoint 2010, you could easily customise the Business Data List web part, or customise views within an External List in order to provide conditional formatting, or even fix some of the data formatting such as the Date/Time or Currency formatting of values coming from the external data source. All of this was done using SharePoint Designer. Granted, it wasn’t the greatest method since modifying the views again using the browser would reset all of the changes you had made anyway.
Lightning Tools released its Data Viewer Web Part for SharePoint 2013 back in April this year to allow users to build views within a web part using just a simple dialog though the SharePoint browser interface. You could select the columns that you wish to see, apply filters, sorting, Data formatting, and conditional formatting. Version 1.0 of the Data Viewer Web Part provided connectivity to Oracle, Microsoft SQL, and SharePoint List data sources. Version 2.0 has been released today offering further support for OData, Business Connectivity External Content Types, and XML files.
So what does this mean for you? Well, if you want to build your own views and apply formatting for External Data, you can now do so without the need to write any code or open SharePoint Designer. Your views won’t be lost, and you can link multiple Data Viewer Web Parts together providing the equivalent of the Business Data List/Business Data Related List web parts even if you only have SharePoint Foundations!
The below screenshot shows how you can configure the Data Viewer Web Part to connect to multiple external data sources.
Once you have selected SharePoint External Data Source Provider from the many data sources that you can connect to, you’ll simply click the next tab ‘Data Source’ to continue.
Using the Data Source tab, simply choose the External Content Type that you wish to connect to, then set your Finder Method, Creator Method, Updater Method, and Deleter Methods. You can use our Meta Man tool to help you create these methods for all kinds of external data types without having to author any code. This will be touched on later within this blog post.
Clicking the Column tab, will allow you to select the columns that you wish to display, and also apply filters to your external data. Filters across columns using And and Or’s is also possible using the Advanced Filtering option.
Finally, you can build your view by setting column aliases, set column widths, set the column alignment, and apply colour formatting to the row and values.
Conditional Formatting allows you to apply formatting that is based on the values returned from your data source, for example you could highlight all customers from a particular region.
The result is a very powerful display of external data allowing to you to Create, Update and Delete data via Business Connectivity Services.
So, how easy is it to build the external content type in the first place? Well, using Meta Man you can connect to almost any external data source and generate the BDC model file. SharePoint Designer 2013 will only allow you to create an External Content Type for Microsoft SQL, .NET assemblies or WCF services. The .NET Assemblies and WCF services will have to be coded manually.
Meta Man can connect to Oracle, Access, MySQL, OData, Informix, DB2, Dbase and many more. The below screenshot from Meta Man shows how to connect to multiple external data sources.
To create the Methods required for the CRUD functionality, simply select the methods you want your External Content Type to have.
Finally, build your associations between multiple ECT’s and you can show related data using the Data Viewer Web Part in SharePoint.
You can download both the Data Viewer Web Part and Meta Man as a fully functional trial from: http://lightningtools.com/trial-download/