WHAT YOU WILL LEARN IN THIS CHAPTER:
How to configure a BCS Application
How to use BCS with SharePoint 2010
How to work with BCS as a SharePoint administrator
How to develop custom solutions for BCS
Business Connectivity Services (BCS) is all about using SharePoint to connect the isolated silos of data in your company. What kind of data exists in any given organization? There are payroll and personnel systems, patient databases in healthcare, customer and vendor databases, and sales and inventory systems. The list goes on and on. These different types of databases can be seen as separate islands of information, but with SharePoint 2010 and the BCS, they can all be brought into one interface. External content types (ECTs) are new in SharePoint 2010, and they allow for the exposure of data from external systems, such as databases and Web Services, into SharePoint. There is a new type of list called an external list, which has the appearance of a regular SharePoint list, but is really a web interface directly to the external data. Compared to the read-only nature of the SharePoint 2007 Business Data Catalog, the new BCS not only allows viewing of the external data, but also provides the ability to insert, update, and delete data. All of this can be done out-of-box, in an easy user interface, without having to write any custom code! Figure 15-1 shows a high-level diagram of BCS, featuring some ways that SharePoint interfaces with external data sources.
The ability to have one point of interaction with various business systems within SharePoint and Office applications has the potential to drastically increase work efficiency. Instead of having disparate systems and various logins, business users will be able to go to a single place to access these systems, and that is SharePoint.
When creating and configuring a BCS application, there are several factors to take into consideration before beginning. The main factor, and the most complicated one, is security. Once the decision has been made regarding which method or combination of authentication methods will be used, the easy part is the creation of the external content type. Creating an application sounds like something that only developers would do, but in the case of the BCS, that is not necessarily true. In this section, we will go over a high-level list of some security and authentication options, and then show examples of how to go about creating an external content type and external list from scratch.
When working with Business Connectivity Services, the security options are quite varied and complex. The concepts of database security and authentication are out of the scope of this book, but here is a high-level list of several levels to consider when setting up user access to the BCS data:
Database security: Grant end users permission directly on the databases that they will be using in the ECTs.
External content type permissions: In the Service Application in Central Administration, permissions can be granted per ECT, as shown in Figure 15-2.
Operations: When the ECT is created, a database table is selected, and the operations are created. These are referred to as CRUD (create, read, update, delete). There is the ability to create all of these operations in an ECT, but it is not required. Pick and choose specific operations that you want to allow, if necessary.
SharePoint list permissions: This does not apply to the ECT, only to the external list. Use permissions on one of these lists in the same manner as with any other SharePoint list or library.
Impersonation: When creating an ECT, and the database connection is being added, there is a prompt called SQL Server Connection, as shown in Figure 15-3. Instead of giving the end users direct access to the database, an impersonation can be created and used, via an Application ID from the Secure Store Service Application.
BCS Identity: There is one more, lesser-known method of authentication. Using the BCS Identity allows the connection to Revert To Self, which means that it uses the BCS service identity to connect to the data. With this method, secure store target applications need not be created.
Of these multiple levels, the strictest level of security will always apply. For example, if users do not have access to the external content type, and they are given "Full Control" SharePoint permissions, they will still not have any access to that list. For more information about BCS security and authentication options, refer this TechNet site: http://technet.microsoft.com/en-us/library/ee661743.aspx.
Warning
When logged-in users have direct access to the database that is used in the ECT, their credentials need to be passed all the way from their Windows login to SharePoint, and then from SharePoint to the back-end system where the BCS data lives. This is referred to as a double-hop, since the identity has to be passed twice. If the organization is going to grant users access to the back-end data and use this method, then NTLM authentication in SharePoint will not be sufficient, and Kerberos will have to be implemented.
External content types (ECTs) are the cornerstone of Business Connectivity Services. Think of each ECT as equivalent to a table in a database. Several ECTs can be created to represent multiple tables in the database, and tables can even be associated with each other, which represents the same concept as table relationships or joins. After you've read this chapter and have seen examples of how ECTs are used within SharePoint, the business reasons for utilizing the new BCS will become apparent.
Note
SharePoint Designer 2010 is a free desktop application and can be obtained from Microsoft's download site here: http://bit.ly/SPD2010-32. Chapter 5 showed how to use SharePoint Designer to create custom workflows with no code.
TRY IT OUT: Creating an External Content Type
In the demonstrations in this chapter, we will be using an example set of SQL data that can be downloaded from Microsoft's site here:
http://bit.ly/ContosoBIdemo
This database example is for the retail industry, and is called Contoso_Retail_DW. The download page contains the instructions for downloading, extracting, and installing the database on a SQL Server. In this Try It Out, you will learn how to create an external content type in SharePoint Designer.
Open SharePoint Designer 2010, and open your SharePoint site.
Click External Content Types in the list of Site Objects on the left side of the screen. If you have access to any other ECTs that have been created in the farm, you will see them here.
At the top of the screen, in the New section of the Ribbon, click the External Content Type button.
Click the Name, and change it from New External Content Type to Sales, as shown in Figure 15-4.
Click the link that says "Click here to discover external data sources and define operations," which is shown at the bottom of Figure 15-4.
Click the Add Connection button, as shown in Figure 15-5.
For the Data Source Type, choose SQL Server, and click OK.
In the SQL Server Connection box, type the name of your SQL server and the name of the example database that you preinstalled, which is Contoso_Retail_DW, as Database Name. Leave the authentication option as Connect with User's Identity, and click OK.
Click the plus icon next to the name of the data source, to expand it so that all of the tables are displayed. If there is an error called Cannot Complete Refresh, click OK. The tables that have unsupported data types are not going to be utilized in these examples, so the error is irrelevant. The expanded list of tables is shown in Figure 15-6.
Right-click on the table called FactSales, and click Create All Operations.
The Operations Properties screen explains what all of the different operations are. Click Next.
The Parameters configuration screen is shown in Figure 15-7. This interface is where you have the ability to granularly define which fields from the table will be pulled into SharePoint. By default, all fields have a check next to them, but you can uncheck any that are not needed. The Errors and Warnings section at the bottom is helpful in letting you know what issues need to be addressed before moving on to the next step. We will address each of the errors as we proceed.
This is the first error: "No fields have been selected to be shown in the external item picker control. By default, all fields will be displayed in the external item picker dialog, which may not be the user experience you want. Select a small subset of elements that best describes an item and then select the 'Show in Picker' checkbox for each one."
What does this mean to you? By default, every single column in the FactSales table will be available in the external item picker, which may not be the outcome you desire. There is a checkbox on the right side of the screen in Figure 15-7, called Show In Picker. For each data source element that you select on the left, you can check this checkbox to indicate if you would like to use it in SharePoint. In this demonstration, check Show In Picker next to the following fields: StoreKey, UnitCost, SalesQuantity, and TotalCost. We will cover more information about the external item picker later on in the section called "Using the External Data Column."
The rest of the warnings are the same and are related to the currency fields: "The data source element UnitCost is mapped to the field UnitCost, the scale of this data source element cannot be determined from the data source and the decimal digits property for this data source element is configured to 4. There will be data loss if the data from the external system exceeds this specification."
What does this mean? If these fields contain more than four decimal places in the backend system, SharePoint won't recognize those. In this example, you are not worried about the number of decimal places and can ignore those warnings.
The names of the columns aren't really friendly names, so you have the ability to change the display name of each column. Click the name of the SalesKey field on the left, and in the Display Name box on the right, change it to Sales Key (with a space in between). Do the same thing for each of the other fields, by changing each display name to two separate words where needed. This change is simply cosmetic.
Click on the last field on the left, called UpdateDate. Check the Timestamp Field box on the right. Checking this box signifies that this field represents the last time the item was modified, which is significant when performing searches.
Click Next.
The Filter Parameters Configuration screen has a warning that says: "It is strongly recommended to add a filter of type 'Limit' for this operation type. Without a limit filter, this operation may result in large result sets."
This is the screen where you configure any needed filters on the data. For example, you could filter out sales from a store that is no longer open, or customers who are no longer active. The only filter that we will focus on in this example is the limit filter. This is important because if the data is brought into SharePoint with thousands of items in the list with filter, it could severely impact the performance of the server. You will create a limit filter of 100 items, which simply means that only 100 items will be pulled from the database at a time.
On the right side in the Properties, next to the word Filter, click the blue link that says (Click to Add). This screen is shown in Figure 15-8.
On the Filter Configuration screen shown in Figure 15-9, pick the Limit as the Filter Type, and SalesKey as the Filter Field. Click OK.
In the Properties section on the right, enter 100 as the Default Value, as seen in Figure 15-10.
Click Finish.
Click Save at the top-left corner of SharePoint Designer.
The external content type has now been created. This will be used in many of the other examples in this chapter.
Warning
The person who created the external content type in SharePoint Designer still may not necessarily have access to it. Work with your SharePoint administrator to set up the permissions correctly in the Business Data Connectivity Service application. After the ECT has been created, the Summary view will look something like Figure 15-11.
Notice that there is a Permissions section on the right but no interface to modify them. The administrator who has access to Central Administration can carry out the following steps, in order to modify the list of permissions:
In Central Administration, click Manage service applications.
Click Business Data Connectivity Service.
The Set Metadata Store Permissions button at the top can be used to set up the default permissions that will exist each time a new ECT is created. This can be determined by the administrator.
Click the checkbox next to the Sales item in the list, and click the Set Object Permissions button in the Ribbon.
This screen is where the permissions can be set up for the Sales external content type that you created.
Click the View drop-down box in the Ribbon, and choose External Systems. Check the permissions to the back-end data source Contoso_Retail_DW, and modify them if necessary.
Once the ECT has been created, the next step is to create an External List, which is a SharePoint list that directly interfaces to the back-end database data. This list data is not stored in SharePoint but remains in the external system, with SharePoint as the front-end web interface to the live data. Although external lists have the familiar look and feel of any regular SharePoint list, there are a few things that are not included, such as versioning, check in/out, workflows, and content types. Not only are these lists wonderful because they have that familiar user interface, but also, as you will learn later in this chapter, this business data can even be taken offline.
When an external list is created, its columns come from the external content type, and cannot be modified from within the list settings. Views can be created, in order to display the desired columns, but the columns themselves cannot be changed, added, or removed. There is a one-to-one relationship between an external content type and its external list.
TRY IT OUT: Creating an External List
In this Try It Out, you will learn the process to create an external list from the Sales ECT that was created in the last example. This external list of sales can then be used in several different places in SharePoint. Follow these steps to create an external list.
Open your site in SharePoint Designer, and click External Content Types on the left side of the screen.
Click the name of the Sales ECT, which will display its summary view.
In the Ribbon, click the Create Lists & Form button, as shown in Figure 15-12.
As shown in Figure 15-13, type a list name of Sales, enter an optional description, leave the rest of the defaults, and click OK.
Notice that there is a checkbox option to create an InfoPath form. As you learned in Chapter 12, InfoPath can now be used as the interface to any SharePoint list. This includes external lists, and this decision does not need to be made immediately. The InfoPath form can be created later, if desired, simply by clicking the Create Lists & Form button again.
The External Lists section on the right side of the page now contains one item, called Sales. Click that item, and click the Go To List button in the Ribbon.
Take a look at the Sales list information page, and then click the Preview in Browser button in the Ribbon.
See that all of the columns in the SQL database are now displayed as a SharePoint list!
Explore the new list by clicking on any item to look at the detail view of it, edit an item, and sort or filter. New views can also be created, just as with any list.
A profile page is a page in SharePoint that displays detailed information about a single item in a database table. It is simply an item detail page. This web part page can be customized by changing the theme and even adding other web parts such as a company logo. The profile page is not related to the external list that has been created. These pages are typically used with the Business Data web parts, which will be covered later in this chapter.
Before the first profile page can be created, several criteria must exist, as follows:
A SharePoint site must be created by an administrator as the dedicated location for all profile pages.
The administrator must grant at least Design permissions to anyone who will be creating or updating profile pages.
Business users who will be viewing the business data must be granted read permission on this site.
In Central Administration, in the Business Connectivity Service application, the administrator sets the URL of the dedicated site that has been created. To do this, in the Ribbon, click the Configure button in the Profile Pages section, check the box to Enable Profile Page Creation, paste the URL, and click OK.
Once the above criteria have been met, profile pages may be created for any ECT. Open any ECT in SharePoint Designer, and click the Create Profile Page in the Ribbon, as shown in Figure 15-14. Note that an external list for the ECT is not required in order to use a profile page.
Profile pages are also important if the BCS data will be searchable. Searching BCS data will be covered later in this chapter; just keep in mind that when BCS results are discovered, the search results page will include the link to the profile page of the found item. Figure 15-15 shows an example of a profile page for the "Stores" external content type.
In the BCS, associations can be created in order to join two tables by a common field. This concept can be likened to creating relationships in Microsoft Access and other database programs. Use associations when a field in one external table needs to look up (join) to a field in another table.
The interface to create associations can be found on the Operations Design View of any ECT. For an association to be created, more than one ECT needs to exist. First of all, define the names of the fields to be associated with each other. For example, the Sales table has a field called Store Key, which can be associated with the Store Key field in the database's table called Stores. There can be many items in the Sales table related to any one item in the Stores table. With this relationship, each sale that takes place in the company can be linked with a specific store. When associations have been created, this allows for very rich and interactive BCS web part interfaces to be created. Figure 15-16 shows an example of two related (associated) tables.
After the association has been created, generate a profile page for the Stores ECT. This new profile page not only will contain details about one store record but also will automatically include a connected web part with the list of sales transactions that have occurred for that store.
Business Data Actions can be created for each ECT and are best described as different links that can be clicked from within the ECT. These are actions that can be carried out in relation to the currently displayed ECT detail item. For example, create business data actions in order to link directly to the edit page for an item, or to navigate to a page that contains web parts that are filtered by certain query string parameters.
Business Data Actions are only created by an administrator from within the ECT in Central Administration. In the Business Data Connectivity Service application, click the name of any ECT to see its detailed information page. This configuration screen contains the list of existing actions, and the Ribbon has buttons that allow for the creation, modification, and deletion of any of them. Figure 15-17 displays the default View Profile action that exists in an ECT.
The following is a list of the fields to fill out when creating a new action:
Action Name: This is the text that is displayed on the Action button.
URL: The URL will always include a query string parameter, just like the "Example" text that is displayed in the URL box.
URL Parameters: Since the URL will need to include a query string, the URL parameters must be defined in order to match each parameter with a field in the data source. In this example, the SalesKey is the parameter, so when "
SalesKey=a value" is included in the URL, the number is equivalent to a specific sales item. Where the brackets {0} are inserted into the URL will be the location of the SalesKey.Icon: This is the icon that will represent this action, and it will be displayed on the button next to the action name. Use a URL to an image. The example includes a URL pointing to a location on the SharePoint server.
Default action: Check this box to indicate if this should be the default in the list of Actions.
Once external content types and external lists have been created, there are several different ways that they can be utilized within SharePoint. BCS data can be taken offline using Outlook or SharePoint Workspace, and interactive interfaces to the data can be created using the BCS web parts. There are many possibilities for the creation of custom business solutions, all without needing to write any code.
There are certain types of SharePoint lists that can be connected to Outlook and taken offline. These lists are Contacts, Tasks, Calendars, and Discussion Boards. The ECT that was created at the beginning of this chapter was created as the default Generic List, which is the equivalent of a custom list in SharePoint, and cannot be connected to Outlook. Now, with Business Connectivity Services, each ECT can be created as one of the following list types:
Generic List: Custom list, cannot be connected to Outlook
Appointment: Can be connected as an Outlook Calendar
Contact: Can be connected as Outlook Contacts
Task: Can be connected as Outlook Tasks
Post: Can be connected as a Discussion Board in Outlook 2010
The ability for business users to work on database data offline is quite powerful. Think about systems such as those used in customer relationship management (CRM) and the ability to use those contact lists or filtered subsets of them in daily activities within Outlook.
TRY IT OUT: Connect to BCS Data in Outlook
In this Try It Out, you will learn how to create an ECT as a SharePoint list of contacts, and then connect it to Outlook. When ECTs are created as an Office Item Type other than Generic List, there is an interface to map the database information to fields that exist for that item type. In this example, an ECT will be created from a list of stores in SQL, and table fields will be mapped to pertinent contact fields. When the list of the company's stores is readily accessible to business users, they will always have the current updated information at their fingertips and will not have to rely on lists sent around in email, or spreadsheets that are occasionally updated.
Open SharePoint Designer 2010, and open your SharePoint site.
Click External Content Types in the list of Site Objects on the left side of the screen.
At the top of the screen, in the New section of the Ribbon, click the External Content Type button.
Click the Name, and change it from New External Content Type to Stores.
Select the Office Item Type of Contacts, as shown in Figure 15-18, and leave the default Offline Sync for external list as Enabled. When a list is enabled for offline sync, it can then be used within Outlook and SharePoint Workspace and taken offline.
Click the link that says "Click here to discover external data sources and define operations."
Since an ECT was already created in the first Try It Out, the Contoso_Retail_DW database will already be displayed in the Data Source Explorer, with the list of tables already expanded.
Right-click on the table called DimStore, and click Create All Operations.
The Operations Properties screen explains what all of the different operations are. Click Next.
This Parameters Configuration interface is where you granularly map table fields to SharePoint contact fields. Uncheck the box called Data Source Elements at the top of the screen, so that all of the element checkboxes are unchecked. You will only add the necessary ones, as indicated in the following table. This table also lists the field to select in the Office Property drop-down box for each field.
FIELD NAME
OFFICE PROPERTY
OTHER CHECKBOXES
StoreKey
Custom Property
Map To IdentifierRead Only
SalesTerritoryKey
Custom Property
StoreName
Full Name
Show in Picker
StoreDescription
File As
Status
Office Location
OpenDate
Creation Time
StateProvinceName
Business Address State
Show in Picker
CityName
Business Address City
Show in Picker
ZipCode
Business Address Postal Code
Show in Picker
StorePhone
Business Telephone Number
Show in Picker
StoreFax
Business Fax Number
Show in Picker
AddressLine2
BusinessAddressStreet
UpdateDate
Custom Property
Timestamp Field
After all of the fields have been configured as indicated in the preceding table, there will be no more errors or warnings. Click Next.
Click Add Filter Parameter.
Click the (Click to Add) link on the right.
Change the Filter Type to Limit, and click OK.
For the Default Value, type 100, and click Finish.
Click Save at the top left.
Click the Create Lists and Form button on the Ribbon.
Click the Create Profile Page button on the Ribbon.
Click the Go To List button on the Ribbon, and then click the F12 key to open the list in the browser.
Now that the list has been created as a SharePoint contact list, it can be connected to Outlook. In the browser, click the List tab in the Ribbon, and click the Connect To Outlook button.
The first time you connect an external list to Outlook, an Outlook add-in called the Business Connectivity Services add-in will be installed. If a screen called the Microsoft Office Customization Installer pops up, click the Install button.
The Stores database table is now a contacts list in Outlook! There will be a new section in the Navigation Pane on the left side of Outlook, called SharePoint External Lists, with the new contact list listed. Figure 15-19 shows the Stores as seen in Outlook.
Earlier in this chapter, a high-level explanation of associations was given. In this section, you will learn how to create them, and why. This section contains more information about the context in which associations are created, and the effects of existing associations on web parts.
TRY IT OUT: Creating an Association
In this Try It Out, you will learn how to create a new association between two ECTs. In previous demonstrations, you have conveniently created two content types that happen to have a common "lookup" field. You have a Stores table, and a Sales table, with a common field called StoreKey. For each record in the Stores table, there are multiple records in the Sales table. Follow these steps in order to create an association with the Stores ECT from within the Sales ECT.
In SharePoint Designer, open your site, go to the list of External Content Types, and click to open the Sales ECT.
Click the Operations Design View button on the Ribbon.
This is the screen where CRUD (create, read, update, and delete) operations can be performed, allowing data to be added, edited, or removed. Right-click the FactSales table in the Data Source Explorer, and choose New Association, as shown in Figure 15-20.
Click the Browse button, select the name of the Stores ECT, and click OK.
The section in the middle of the Association Properties page that is used to map the identifiers is already filled in with the matching field in this case, which is StoreKey. Click Next.
On the Input Parameters Configuration screen, click on the StoreKey field on the left, and check the Map to Identifier checkbox, as shown in Figure 15-21. Click Next.
Add a limit filter, using the same steps as in steps 20 through 22 when the ECT was created. Click Next.
The Report Parameter Configuration can be left with the defaults. Optionally, select the name of each data source element to change the display name or configure other options. Click Finish.
Click Save.
Click the Create Profile Page button on the Ribbon. If you're overwriting an existing profile page, it's okay to click Yes at the prompt.
Now that the association has been created, it will not be apparent until the BCS web parts are used, especially when looking at the Business Data item. When a specific store detail is viewed, all of that store's sales are automatically displayed on the profile page along with the store. This profile page is also seen when the BCS data is displayed in SharePoint search results. You will get to see the result of the association creation in the section entitled "Using the BCS Web Parts."
SharePoint Workspace 2010 is part of the Office Suite of applications and is used to take SharePoint data offline. Business users who travel frequently or remote workers who have less reliable network connections will find this tool invaluable. Not only can SharePoint lists and libraries be taken offline and changes synchronized when you're back on the network, but now the BCS allows you to take external lists offline to work on them from wherever you are.
SharePoint custom lists, and external lists that are created as Generic Lists, cannot be taken offline in Outlook, but they can be taken offline and synchronized using SharePoint Workspace. Figure 15-22 shows an external list as viewed from within SharePoint Workspace.
There are several out-of-the-box web parts that can be used in order to display data from ECTs. Inherently, these web parts are not related to the external lists at all, and are only associated with ECTs. In other words, externals lists do not need to exist in order to make use of these web parts.
Use this web part to display a list of data from a single ECT. The information is shown as a table on the page. The web part settings allow for the selection of an existing ECT in the Type box.
There is a drop-down box called View, where an existing view may be selected. Since these web parts are not related to external lists, any views that have been created at the list level will not be shown here. This list of "views" is really the list of Read List Operations that have been defined in the ECT.
TRY IT OUT: Using the Business Data List Web Part
In this Try It Out, you will learn how to use the Business Data List web part. Not only will you practice inserting it on the page, but you will also learn about the capabilities of the View drop-down box, for filtering information. First, you will walk through adding this web part to a page, and then you will add a new "view" in the ECT. Once this new "view" (read list operation) has been created, it will be available as an option in the web part properties.
Pick a page on your SharePoint site with which to test these web parts. Click Site Actions and choose Edit Page.
Click to insert a web part on the page. In the list of web part categories on the left, click Business Data. Click the Business Data web part, and click the Add button.
Note
For more information and instructions on how to add a web part to a page, please refer to Chapter 7.
Now that the empty Business Data List web part has been added to the page, click the link to Open the tool pane.
You will use the Stores ECT that you created earlier in this chapter. There are two buttons next to the Type box in the Web Part tool pane, as shown in Figure 15-23. Type the name, or pick the Stores ECT from the list.
At the bottom of the web part tool pane, click OK.
Notice that by default, the web part is extremely wide, as it displays all of the columns from the ECT. Click the Edit View button at the top right of the web part, as shown in Figure 15-24, so that the list of displayed columns may be modified.
On the Edit View screen, deselect several columns in order to make the web part more narrow, and click the Title radio button next to Store Name. Click OK.
The next step is to add a new read list operation to the ECT. We want people to be able to quickly filter by a full or partial city name. Open the site in SharePoint Designer, and click External Content Types on the left.
Click to open the Stores ECT.
In the Data Source Explorer pane, right-click the DimStore table, and click New Read List Operation.
Enter Field Filter as the Operation Name and the Operation Display Name. Click Next.
Click the Add Filter Parameter button.
As the Data Source Element, choose CityName from the drop-down box. Click the (Click to Add) link next to Filter.
On the Filter Configuration box, enter City as the Filter Name in the box called New Filter, and the Filter Type is Wildcard, as seen in Figure 15-25. Click OK.
In the Properties pane on the left, change the Default Value to <<Null>>.
Click the Add a Filter Parameter button.
Click the (Click to Add) link next to Filter. Change the Filter Type to Limit and click OK.
Type 100 as the default value. The Filter Parameters Configuration screen is shown in Figure 15-26.
Click Next.
Configure the Data Source Elements in the same manner as in step 10 of the earlier Try It Out, "Connect to BCS Data in Outlook." Click Finish.
Click Save at the top left, to save the ECT.
In the browser, look at the page where the Business Data List web part was added. Refresh the page, and then open up the Web Part tool pane. In the View drop-down box, notice that the new view, called Field Filter, is now an option. Select Field Filter and click OK.
The web part now has a new option at the top, which allows for a query to be done on the city name, as shown in Figure 15-27.
Try out this City filter by using different operations in the drop-down box, to see the way that the results are retrieved. If multiple fields need to be added in addition to City, you would repeat steps 13 through 16 for each additional field. Also, remember the BCS association that you created? Click the name of any store in the list to see its profile page, and notice that all the store's associated sales are listed on the page.
This web part displays a single item from an ECT. The Web Part tool pane has settings to select the external content type and then optionally pick a specific list item. There is also a button to pick the fields that need to be displayed, as well as a button to select actions. The Item field does not have to be filled in, because the item to be displayed can be passed to this web part using a web part connection. Web part connections were covered in detail in Chapter 7.
TRY IT OUT: The Business Data Item Web Part
In this Try It Out, you add the Business Data Item web part to the same page as the Business Data List web part. A web part connection will then be created between the two web parts, so that when an item in the list is clicked, the detailed information about that item will be displayed in the Business Data Item web part.
On the page where you added the Business Data List, click to add a new web part, and insert the Business Data Item web part on the same page.
In the Business Data Item Web Part tool pane, select Stores in the Type box. Leave the Item box blank.
Under Fields, click the Choose button. Take a look at the list of fields, and see that you can deselect items and rearrange them vertically. Click OK.
The next step is to create the web part connection. With the page in edit mode, click the drop-down arrow at the top-right corner of the Stores List web part. Click Connections
Each item in the Stores list will now have a new connection filter icon next to it. In Figure 15-28, the list has been filtered to show a subset of stores, and then the connection (double arrow) icon next to the Contoso Baytown Store was clicked. The Business Data Item web part now displays the details of that store on the page. Of course, the Title of any item in the store list could be clicked to see its profile page, but with this web part connection method, you do not have to navigate away from the current page.
This web part displays a list of the actions associated with a specific external content type. It is simply a view of the links to the different actions for the ECT that is specified in the web part properties.
This web part performs a filter using data that exists in an ECT. In the Web Part tool pane, pick an ECT. Optionally, pick a default record in the table. This web part is then meant to be connected to another web part on the page, to pass filters or parameters to it. The Business Data Connectivity Filter has no use if not connected to other web parts, using web part connections. This web part can be used to pass business data to web parts such as those that display SharePoint list or library data; it does not need to be used in conjunction with other Business Data web parts.
The Business Data Item Builder is used on a page in order to pass query string information from the URL to the web parts on the page. This web part automatically exists on all BCS profile pages. This web part is not seen unless the page is in edit mode, as it is only used to pass parameters and has no user interface. For example, once the profile page for the Stores ECT has been created, and an item name is clicked in a BCS web part, the URL for the profile page will have a query string. In this case, it is "StoreKey = 4". The StoreKey is the unique identifier in the table. The Business Data Item Builder sees this unique StoreKey and can pass it to other web parts on the page through web part connections.
When associations have been created between external content types, they are considered "related lists." When the Business Data Related List web part is inserted on a page, it allows you to pick a list that is related to a BCS ECT that has been added to the same web part page. For example, if a page already contains a Business Data list of stores, and a Business Data Related List is inserted on the page, the only option for the source data will be the Sales ECT, since it is the only one that is related to Stores. A web part connection still needs to be created between the sales and stores web parts, to pass the filter information.
Chart web parts are new in SharePoint 2010 and were covered extensively in Chapter 7. One of the data source options that can be used is Connect to Business Data Catalog, which allows an ECT to be selected. Once the source has been selected, configure the X and Y axis and customize the way the chart appears visually. This web part was covered in detail with screenshots in Chapter 7.
The external data column is a type of column that can be added to any list or library. Once external content types have been set up, create lookups to their data by adding external data columns.
TRY IT OUT: Creating an External Data Column
Using the Stores ECT that you created in the Try It Out called "Creating an External Content Type," you will learn how to create an external data column in a document library. The scenario is a library full of company policies. Each policy can be associated with a specific company store. Once the document library has been created, you will create the lookup to the ECT.
On your SharePoint site, click Site Actions, and choose New Document Library.
In the Name box, type "Policies." Click Create.
Click the Add document button, and add a couple of example documents that can pose as company policies.
On the Library tab in the Ribbon, click the Create Column button.
Use the following table in order to fill out the fields on the Create Column screen, and then click OK.
DATA
Column Name
Store
Type of information
External Data
Description
Pick a company store
External Content Type
Stores (Contoso_Retail_DW)
Select the Field to be shown
StoreName
Display the actions menu
Yes (checked)
Link this column to default action
Yes (checked)
Show each of these additional fields:
CityName, StateProvinceName, StorePhone
Add to default view
Yes (checked)
Hover over the name of the first document in the Policies library, and click Edit Properties.
In addition to the default fields like the file name and title, the new Store field will be displayed, as shown in Figure 15-29.
Type the name of a store, or use the item picker button on the far right in order to search for a store. Once any store has been selected, click to Save the document properties.
Notice that four of the Store table columns are shown in the document library and the row of the document you just edited displays all of the extra information about that store. Not only is the store name displayed, but the city, state, and phone number associated with that store are shown in their columns. These external data columns show live data, so that when the external system changes, the document library will automatically show that current information.
Earlier in this chapter, in the Try It Out titled "Creating an External Content Type," step 13 mentioned the checkbox for "Show in Picker." You can select this checkbox for each column of data. When you use an external column, this column is filled in for the list items, and data is looked up in the ECT. The interface for the selection of data is similar to that of the people picker control. In Figure 15-30, the field is being filled out with "Seattle"as the search term to find the correct store in the list. This interface is what is referred to as the picker, and was used in step 8 above if you clicked the item picker button.
External data columns that have been created in libraries can be utilized within Office applications such as Word and Excel. The Document Information Panel and Quick Parts were first introduced in SharePoint 2007, and they still have very powerful capabilities when it comes to Office integration with SharePoint. In this section, you will learn about these points of integration, the differences, and how they can be utilized in business examples.
The Document Information Panel is a small panel that displays a document library's metadata inside of the associated Office application. All columns associated with a library are editable in this panel. Just as with any other columns, business data is also surfaced inside of the Document Information Panel.
TRY IT OUT: BCS Data in Microsoft Word
In this Try It Out, you will continue with the concept of a company policy library. You added the external data column to the library in the last Try It Out, so this example will involve working with this external data from within Word 2010. If you have not uploaded a Word document to the Policies library, do so before following these steps:
The Document Information Panel is not necessarily going to be displayed by default, inside of the files in the library, so the first step is to set the library up to always display it. In the Library tab of the Policies library, click Library Settings.
In the General Settings section, click Advanced Settings.
Set Allow management of content types to Yes, and click OK.
Still on the Library Settings screen, in the Content Types section, click the name of the Document content type.
The Content Type Information screen is shown in Figure 15-31. Click Document Information Panel settings.
Check the box labeled Always show Document Information Panel on document open and initial save for this content type. Click OK.
Click Policies in the breadcrumb trail at the top of the screen, to navigate back to the main screen of the library.
Click the Add Document button in the library, in order to upload a new document to the library. Browse to a document, double-click to select it, and upload it.
A screen containing editable document properties will be displayed. In the Store box, select the name of any store. Click Save.
Click the drop-down box on the name of the new document in the library, and select Edit in Microsoft Word. If an information message pops up, click OK.
Take a look at the Document Information Panel, shown in Figure 15-32. The store information fields are displayed in it.
Now that you've seen how the external data is displayed inside a Word document, the next step is to try out the Quick Parts in Word. The Document Information Panel is limited to a read-only interface, and the information does not follow the document when it is printed or emailed out.
In the example of company policies, companies typically use a standard template, such as one with company colors or a logo. You will now learn how to quickly customize the library's document template and insert the external business data into the document.
On the Library tab in the Policies library, click Library Settings.
In the Content Types section, click the name of the Document content type.
Click Advanced Settings, and in the Document Template section, click the link that says (Edit Template).
A blank Word document is displayed. This is the template that can be customized with company colors and logo. In this simple example, we will simply insert some business data into the document. At the top of the document, type the word Store: and then a tab.
On the Insert tab in the Ribbon, click the Quick Parts button, as shown in Figure 15-33.
Select Document Property, and a list of all of the document's fields will fly out to the side. Notice that all of the newly added Store columns are in the list. Click Store.
On the next line in the document, type City: and a tab. Insert the Quick Part called Store:CityName.
Repeat step 7 for the store's state and phone number fields, so that the document looks like Figure 15-34.
Close the document template and click Save.
On the Advanced Settings screen, click OK.
Navigate back to the main screen of the document library, and on the Documents tab, click the New Document button.
In the gray Quick Part next to the word Store, use the External Data Picker to select the name of a store.
As soon as a store has been selected, see that the other three fields are automatically filled in with the extra data about that store. At this point, the business user would then fill in the rest of the company policy text in the body of the document, give it a title, and save it. The information in the Quick Parts will always be live data from the external system as long as this file is in the library. The document can be emailed outside of the company, and the Quick Parts will retain their information, but they will not be dynamically connected to the database anymore. Note that other fields from the document, such as the title and date, can also be inserted as Quick Parts. Figure 15-35 shows the final result of the policy document.
Note
Note that in Office 2007, the document information panel will display the BCS data, but those fields are not editable from within that interface. Office 2010 will allow for both viewing and editing of these fields from within the application.
For those who have administrator access in Central Administration, there are some additional interfaces to the BCS to note. Within the Search Service Application, there is a way to index BCS data. Also, in the User Profile Service Application, BCS data can be added as a secondary user profile data source. This section touches on topics that are pertinent to SharePoint Administrators. For information that is targeted to administrators, there is a book called Professional SharePoint 2010 Administration by Todd Klindt, Shane Young, and Steve Caravajal (Wrox, 2010) that goes into much more detail.
When performing searches within SharePoint, there are several types of data that can be searched for. In addition to SharePoint sites, websites, file shares, and Exchange public folders, ECTs can be searched. This is simply another type of content source that the administrator can create in the search administration area. Once an ECT database has been added as a content source and crawled, relevant results will be included on the search results page. Just as with SharePoint results, the BCS data will be security trimmed, so that users will only see data results that they have access to.
In the search service application in Central Administration, look at the list of content sources. When a new content source is created, you can choose Line of Business Data as the content source type. This will allow for the selection of either all business data, or particular databases. Once the BCS source has been added and crawled, its items will be returned as part of regular search results. Figure 15-36 shows the creation of a new BCS content source.
In situations where user profile information is stored in multiple systems and databases, it can all be brought together in SharePoint using the BCS. For example, an organization may store basic directory information about users in Active Directory. Then, their personnel information such as salary and performance information may be stored in a database that is external to SharePoint and Active Directory. As long as there is a field that exists that is unique to each user and is common between Active Directory and the other system, a new BCS connection can be created in order to bring in each user's associated information from that external source.
From within the User Profile Service Application's Configure Synchronization Connections page, once the main LDAP connection (such as Active Directory) has been created, BCS can be created as a secondary connection. When a new synchronization connection is created, pick the desired external content type. As shown in Figure 15-37, the next choice is whether the relationship is one-to-one, or one-to-many between Active Directory and the ECT. It is important that the field called Return items identified by this profile property contain a field that will match items in the two systems together.
Custom Business Connectivity Services solutions can be developed using Visual Studio 2010. Custom development is not just for programmers, though. Custom BCS Solutions can be created as XSLT List View web parts in SharePoint Designer. These were previously referred to as Data View web parts. These custom web parts allow you to granularly format the way the information looks on the page, adding conditional formatting, grouping, sorting, and filtering.
Open your SharePoint site and external list in SharePoint Designer 2010. In the views section at the top right, click the New button to create a new XSLT list view. When the cursor is placed inside the table content in the middle of the page, a new section will appear in the contextual Ribbon at the top, called List View Tools. This section of the Ribbon contains four tabs that contain all of the buttons that are needed for configuring the view: Options, Design, Web Part, and Table.
Business Connectivity Services in SharePoint 2010 are the key to utilizing data from various data silos in your company. Take the personnel system, sales database, and patient accounting system, and bring them all into a single familiar interface, which is SharePoint. ECTs with external lists are web interfaces into the live back-end data. Once the proper security and authentication methods have been implemented, BCS gives you wonderful tools to make the organization much more streamlined and efficient. Use the Business Data web parts, external data columns, and even workflows in order to incorporate these typically separated islands of data into all of the familiar SharePoint web part pages and interfaces that the business users are accustomed to.
The following table contains a list of Internet references. These sites can be used in order to obtain more detailed information about BCS topics.
SITE NAME | URL |
|---|---|
BCS Resource Center |
|
BCS Team Blog |
|
BCS Information on MSDN |
|