We have had many inquiries about how you could use external data or external content types in SharePoint with Business Connectivity Services (BCS). Here is an example of the potential gain of using external data through BCS and how you can use it with BrightWork.

Summary

BCS is a SharePoint service that allows you to connect to pieces of data like SQL content databases. Once connected to these data sources you can then create SharePoint lists from this data.

In this blog, we will attempt to explain a use for SharePoint BCS inside BrightWork with a real-world example. The example is a software system used by all employees for time tracking, which is external to SharePoint. For the purposes of this example, we will simply say this system records effort in hours in one column and this is recorded against a project code column in the other.

Aside from this system, you use SharePoint and BrightWork to track other project management progress and success. What if you could bring the project code and effort data into SharePoint and BrightWork lists so that you can start to take advantage of both systems? Well, you can, and here is how.

1. Planning & Connection

BCS allows you to generate external content types or external lists inside SharePoint. SharePoint Designer is the tool you use to create these external list connections. I will not go into too much detail of how to create the connection itself.

First of all, creating a BCS external list has the potential to become quite complicated. The action of setting up BCS inside of SharePoint designer is not complicated, but issues do seem to arise around identifying and accessing the data you’re trying to connect to.

Let’s take the time tracking software example. It writes employee effort data to a particular database. Some software can make it almost impossible to identify where that particular piece of data is stored and what its internal name is. It can be very difficult to find this out without first getting this information from the software supplier or doing your own research.

TIP: Some software tools like CRM and others allow you to create dynamic spreadsheets to its data in Microsoft Excel. If this is an option for you, it may just help you identify the entities and databases you need to reference when creating the BCS connection. Open the dynamic spreadsheet in Excel and click on the “data” tab and then choose “exiting connections”. Then look at it properties of the connection and it may help you pinpoint where you need to report from through BCS.

Now you know where to get the data. When you try to connect to the database with BCS through SharePoint designer, you may start coming across issues to do with authentication and security. Your account will need permissions in SharePoint central administration to create BCS content but also your account may need very specific SQL access against the database your attempting to connect to.

Tip: Bring your SharePoint and SQL administrators in on this project early on so that you can plan the connection and reduce some of the possible delays. The following link in principle should have all the information you need from concept to completion of a connection.

Tip: If you get error messages use Google or Bing to help you. Chances are someone else has had this issue before you.

Tip: When creating these external lists I recommend that you house them on the root of your site collection.

2. Using the External Data

Congratulations! You have a working external list inside of SharePoint which includes your data. Now all you have is a list but how do you use the data within it in a useful manner.

Back to our example then. Your external list now contains two or three columns from your timesheet software, one being the project code and the other being the effort recorded in hours.

Where would you like to see this information? Let’s say you have a requirement to reference your project code and the amount of effort on each Project site inside of BrightWork.

You would like this information on your Project statement so that when a project manager creates a Project he or she can then select the project code on the project statement and this automatically pulls in the effort information.

3. Add an external column to an existing list

So now you know where you want the data, now let’s add it. Navigate to your Project site and choose All site content from the settings dropdown. Then click on Project Statement. Now click along the top ribbon “List” and then click on “List settings”. Now choose ‘add a column’. Then choose External Data.

Then select your external content type e.g. timesheet. In the next select box choose your Project Code. The next two available options are of personal choice. You do however want to add an additional field. In this example, you want the Effort column. When you are ready choose OK.

What does this mean? Well, next time your project manager edits or references the project statement he or she will see an option to select the project code. Once they choose the appropriate code, the effort data which is tied to that project code will be automatically pulled in. Also as data changes in the external source e.g. timesheet software, so too will the data in this column. Cool hey! Here is a link that goes into some more detail on this.

4. Make some minor adjustments

While inside of list settings you may want to change the order of items and how they are presented in form and list views. Choose BrightWork Column visibility settings to help you manage the visibility of your new column in forms. Read more here. Change your column ordering by using the option Column Ordering at the bottom of your list of columns inside of list settings.

*Note if your list contains content types you will not see the column ordering option here. You need to first choose the content type at the top of the settings page first and then you can manage the column ordering of that type. You may also want to make some changes to any custom list views at the bottom of the list settings page.

Congratulations-  you have now attached external data to a list on your project site! This could be all you want for now but continue on if you want to start reporting on this data also.

5. Report on your new data

Now you can add your new data columns to your project summaries report. That way you can use BrightWork to see across projects but also use it to report on not just the data that’s within SharePoint but the data that’s being pulled into SharePoint from external sources.

In the example in the screenshot, here is a project summary returning external content from Microsoft Dynamics CRM. To do this, you must create a custom report for your project summaries report using the BrightWork Reporter Library.

This will consist of three primary actions.
1. Copy an existing report and in this case, you want to copy your All Project Summaries Report. Read more here.
2. Add your new external column to this report. Read more here.

Tip: Identify the internal column name using BrightWork column visibility settings.

3. Return to your original Project Summary report web part and change the report it is reporting on from the server gallery to the BrightWork Reporter Library and the new report you just created.

Tip: All customers can access in-depth training materials on this process. Contact support today if you don’t already have access.

Tip: While you are this far, you may even start thinking about the possibility of custom metrics and even scorecards.

Conclusion

BCS is not for everyone as it has large overheads during its setup stages. It is, however, an incredible tool that helps you consolidate data and reduce the need for redundant data. It also has some real practical uses in the Project Management industry.