…so now that we have a custom SQL view developed and that data is surfaced in the form of an External Content Type, let’s add it to our SharePoint site.
Navigate to a SharePoint site, select the option to View All Site Content, and create a new External List. If you plan to modify this list using SharePoint Designer, you’ll need to provision the list on any site but the main PWA site.
The list will appear as follows:
Add a grouping by Project Name, and you get the following…
Formatting the External List
To get fancy, you can even add conditional formatting to the External List. To do so, open the list in SharePoint Designer. Click on one of the cells in the column you would like to format, and choose the option to apply Conditional Formatting.
Add the appropriate options, hit Save, refresh the page in the browser, and you should see something like this….
Search around the Web, and you should be able to find all sorts of blog postings about how to add icons instead of simple conditional formatting.
Removing the HTML Tags
One thing you may note is that some of the text fields have HTML tags. This is because the fields are stored as rich text fields and then surfaced as plain text.
There’re probably a couple ways to fix this, including using both SQL and SharePoint Designer functionality. Until I figure those out however, I simply went to the Project Site and converted the fields in the SharePoint Risk list from rich text to plain text.
That solved the issue and doesn’t appear to throw errors on publish. Note that you will have to republish the Risk InfoPath form if you make any changes to the fields.
…and there you have it, a more or less out of the box way to create an aggregated Risk list. With a little practice, it shouldn’t take more than a couple of hours to implement.