Welcome back.  We are now on Part 4 of our Business Data Catalog (BDC) series.  If you are just joining us, here is what’s already been covered:

In Part 1, I covered the purpose and technical architecture of the BDC.  A quick look at the BDC in action was also introduced.

In Part 2, I covered the Application Definition File (ADF) in depth and how to develop one to connect to a back-end database.

In Part 3, I continued the discussion of the ADF, but looked at how to develop an ADF to connect to Web Services.

In this Part 4, which is much less technical, the focus is two primary ways to consume BDC data through the UI.  This will include Business Data Web Parts and incorporating Business Data into SharePoint lists.

Business Data Web Parts

Business Data Web Parts are the most common way to surface BDC data to end-users.  Using them, users can filter and sort the data to quickly find the content needed.  Their flexibility and ease of use empowers users to perform many tasks that would otherwise require custom development.  In MOSS Enterprise edition, there are five Web Parts and we will cover each in this article:  Business Data List, Business Data Item, Business Data Related List, Business Data Actions, and Business Data Item Builder.  Each of these Web Parts will only provide a read-only view of the external data, so don’t plan on making any changes through them.  There are ways of updating the external data, but we will wait until Part 8 to get into this.

Business Data List Overview

The Business Data List is the most powerful and most commonly used Business Data Web Part.  At its heart, it is a Data View Web Part and is used to display a sheet of columns and rows (or items).  The items displayed are based on filter criteria that the user enters.  Here is a simple example where we find all customers located in the WA (Washington) Region.

Figure 1: Business Data List Web Part

These customers that you see are defined in a SQL Database, and the information is pulled in real time.  This is a powerful way to expose your line-of-business data within a centralized SharePoint interface.

Let’s quickly walk through how I got here.  As with any Web Part, you start off by editing a Web Page (Figure 2) and adding a Web Part into one of the Web Part zones (Figure 3).

Figure 2: Editing the Page from the Site Actions Menu

Figure 3: Adding a Web Part to a Zone

After clicking Add a Web Part, you will be presented with all the Web Parts that are available for that site collection.  In the Business Data category, you should see the five Business Data Web Parts.

Figure 4: Business Data Web Parts

After you select the Business Data List Web Part and click the Add button, you will be presented with this next screen, prompting you to now configure the Web Part.

Figure 5: Default display after adding a Business Data List Web Part to a zone

After you click the Open the tool pane link, you are presented with all the configuration properties for the Business Data List Web Part.  The required setting is that you must select the Type from the catalog as shown here.

Figure 6: Specifying the type

The Type refers to the entity in one of the ADFs that have been loaded.  The entity must also have a finder method defined.  You can either enter in the name directly and click the Check Types icon .  Or, you can select the Browse icon to see what is available.  (Security Note: Only those applications and types that you have Select in Clients and Execute permission on will be shown.)  In the image shown below, one of the four types can be selected.

Figure 7: Browsing applications and types stored in the catalog

In the first screen shot above (Figure 1), you saw a list of customers.  To get this, I selected the Customers type from SynergyData (the name of the BDC Application).  Initially, this will return all of the columns that are defined in the Finder method within the ADF as shown below.  (Note: I also exited Edit Mode to return to the regular view).

Figure 8: Business Data List Ready for Action

At this point, the Web Part is ready for filtering and sorting operations.  To filter, you first choose one of the Filter choices from the drop down list shown on the top left as shown here:

Figure 9

What is shown are the filter options that are defined by the FilterDescriptors as covered in Part 2.  Here is what is defined for this Customers entity in the ADF:

Figure 10

If the type of filter is a Wildcard, you will have several filter options that you can select in the second drop down list as shown here:

Figure 11

If you’ve specified Comparison, you only have one choice:

Figure 12

You can add additional criteria by clicking the Add button and adding another filter.  These are all processed as an “And” operation, meaning that only items that match all criteria are shown.  To sort, as with any SharePoint view, you simply click on any of the columns headings.

You can also filter by using the column headings.  In this example, I start off by searching for all customers in the SP region.  This initially returns six items.  I then filtered the display to only show those in Sao Paulo.  The advantage is that you can still perform some filter operations even though the column is not defined as a filter descriptor.  Here is the result:

Figure 13: Column Filtering on City

What if you wanted to just filter columns?  For example, perhaps you only need to show a handful of columns for each customer.  This is easy and is done by editing the default view for the Web Part.  To do this, you can go back into edit mode for the page and click the Edit View link as shown here:

Figure 14: Editing Business Data List View

This will then take you to a screen where you can adjust the view in a number of ways.  One of the choices is to select the columns as their position.  As you can see below, this works just like a regular view for a SharePoint list.

Figure 15: Selecting columns to display

From this same screen, you also have some other useful features.  For example, you can “hard code” a specific filter so that only certain items display.  For example, perhaps each region has its own SharePoint page, and on that page, you only want to display the customers in that region.  You could configure the settings as shown here and prevent the user from changing the filter:

Figure 16: Only show customers in the SP Region

Here is the result:

Figure 17: Customers in the SP Region (and user cannot change the filter)

While editing the view, you have many more options such as the default sort and additional filter options.  I’ll leave these to your own study.

Business Data List Advanced Settings

Let’s turn our attention to more advanced capabilities.  Since the Business Data List Web Part is a type of Data View Web Part, you have a number of settings such as customizing the XSL or Parameters.

The XSL (eXtensible Stylesheet Language) is an XML-based transformation language that converts XML that is returned from BDC into HTML that is displayed for the Web Part.  This XSL is generated automatically when you select a catalog type such as customers.  While you can edit this XSL directly inside a dialog box for the Web Part, it is much easier to copy and paste this into a file and edit within Visual Studio, SharePoint Designer or your favorite XSL editor.  Keep in mind that the XSL contains not only the HTML results, it also contains the HTML for the selection criteria, the Actions toolbar, and the column headers.

A simple XSL change would be to enable alternating line styles.  The result looks like this:

Figure 18: Modified XSL to highlight alternating items

To enable this, I simply inserted the <xsl:if> condition you see here highlighted in this code snippet:

Figure 19: XSL

When defining the XSL, you can either link to an XSL stylesheet (located within a document library) or define the XSL for this specific Web Part instance.  Linking the XSL would be best for identical Web Parts that you have in different locations.  Since the XSL is generated for the specific columns, a shared XSL file cannot be used different Business Data Lists with different types (entities).  Having a linked XSL sheet is also helpful to ensure that changes made to the Web Part will not erase your custom XSL code.

For those not too comfortable with XSL, you have some control over the Web Part using SharePoint Designer (SPD).  Like the changes to the view we saw above, SPD also allows you to define filtering and sorting behavior.  One advantage with it, however, is that you can define filters based on variables such as the query string.

To help explain, let’s adapt the Region example we just covered in Figure 17 above where we hard coded the filter for the SP Region.  In this case, let’s pull that Region from the query string instead which would give us a more flexible design.

This section assumes you have a basic understanding of editing pages in SPD.  For a brief overview, see my friend Ishai Sagi’s introduction here.  Once in SPD, open up the page for the web site where the Web Part lives.  We click on the Web Part and access the Common Data View Tasks by clicking on the tiny icon as highlighted here in red:

Figure 20: Customizing Web Part in SPD

Once you click the icon, it will expand the Common Data View Tasks dialog as shown here:

Figure 21: Common Data View Tasks Dialog

From this point, click the Parameters link.  Click the New Parameter button and fill in the contents as shown below.  This will create a new parameter named Region that will be pulled from the query string.

Figure 22: Adding a New Parameter

Now that the Region parameter is created, we can create the filter that is based on its value.  Return to the main Common Data View Tasks dialog and this time select Filter.  Create a new filter by as you see here.  Note: The [Region] value is the parameter that you just created.

Figure 23: Adding a New Filter Based on the Parameter

Save all of your work in SPD and refresh the page in the browser.  If you do nothing else, you’ll find that your Business Data View Web Part does not return any items.  This is because the Web Part is expecting a Region query string value.  Edit the URL in the address bar, and add the query string and value after your page.  For example:

http://WebApp/teamsite/default.aspx?Region=SP

Now run a new query using the Web Part.  You should find that all the item results are automatically filtered to only the SP region.  If you prefer, you can supply a default value for the parameter when you create it as shown in Figure 22 above.

Important Note: When editing the Web Part using SPD, it will change the current XSL and Parameter settings.  So, be careful if you will be customizing the XSL manually and also through SPD as it will overwrite your changes.

Business Data Item Web Part

Now that we have thoroughly covered the Business Data List Web Part, the remaining Web Parts are much easier to learn.  The next one on our list is the Business Data Item.  This Web Part is intended to just show you the column values for a single item that is returned from BDC.  It is often used in conjunction when you connect Web Parts as you’ll see shortly.  Before we go there, let’s first look at a simple example.

Here you see the contents of one customer:

Figure 24: Business Data Item Web Part

To configure the Business Data Item Web Part, first set the Type from the catalog picker.  Then specify the item by searching for it.  Here is how it the configuration looks:

Figure 25: Configuring the Business Data Item

While this is easy to do, it’s not too practical to hard code in a single item.  Instead, let’s see how you can connect this to our Business Data List Web Part.  This will allow you to select a customer from the list and then display the customer’s details.  To start, add a Business Data List and Business Data Item Web Part on the same page.  They can be in different zones but must be on the same page.  You must also make sure they are both set to the same Type (such as Customers).  From here, you need to establish a Web Part connection between them. You can do it as shown here:

Figure 26: Connecting Business Data List to Business Data Item

This will send the selected item from the Customers List (on the left) to the Customers Item (on the right).  Once these are connected, you get the display as shown below.  Notice that selecting The Cracker Box shows this customer in the Business Data Item Web Part on the right.

Figure 27: Result of Web Part Connection

Business Data Related List Web Part

The Business Data Related List Web Part is used when you want to pull related items using an association defined in the ADF.  For example, perhaps we want pull the sick days a certain employee has taken.  Or we want to list the orders for a selected customer.  Let’s see how this latter example would work.  To start, let’s add a Business Data List and Business Data Related List Web Part to our page.  As we have been doing, we configure the Business Data List to use the Customer Type in the catalog.  We then point the Business Data Related List to the Orders Type.  Here’s how the Business Data Related List looks when configuring it:

Figure 28: Configuring the Business Data Related List

Notice that you must specify the relationship on the Orders Type that you want to use.  This choice is provided to us since it was defined in the ADF as you can see here:

Figure 29: Association between Customers and Orders in the ADF

Now that the Web Parts are configured, we need to establish a connection between them.  Once that is done, we get the following result:

Figure 30: Result of Connecting Business Data List and Business Data Related List

Of course, if you prefer, you can have the selected customer drive both the Orders List and the single customer in the Business Data Item.  Here’s how it would then look:

Figure 31: Connecting Three Web Parts Together

Business Data Actions

Actions are a common way of displaying or editing a single item, and we will cover them in detail within Part 8.  To give you a basic overview, let’s look at a sample action that is defined in the ADF.  Here is one that is defined for our Customers entity:

Figure 32: Action Defined for Customer Entity in ADF

Notice that the action has a name (in the figure above it is View Profile) and contains one or more ActionParameter elements.  This column (or columns) is often the Identifier for the entity.  This means it is used to uniquely identify this item.  For our customer’s entity, the CustomerID will be provided as a query string variable to a page called dbo.Customers.aspx.  How this is processed is covered in the Business Data Item Builder Web Part a bit later.

Now that you understand the basics of actions, the Business Data Actions Web Part simply lists all of the actions defined for a single item.  Here is how the configuration for the Web Part looks:

Figure 33: Configuring the Business Data Actions Web Part

Notice that you configure it much like the Business Data Item Web Part.  The only difference is you can specify which actions to display and how should they be displayed (i.e. the Style).  The Style options are Bulleted List, List, and Tool bar.  Once you have it configured (and connected to a Business Data List Web Part), here is how the results look with a Bulleted List:

Figure 34: Connecting the Business Data List and Business Data Actions

When you click on the View Profile link, it takes you to the Url defined for this action which is http://spserver:22222/ssp/admin/Content/dbo.Customers.aspx?CustomerID=LAZYK.  Incidentally, you can also access these actions by clicking the View Profile context menu as shown here:

Figure 35: Accessing the Actions from the Business Data List

As you can see, this is a flexible way of sending a single item to any web page, whether this is in SharePoint or an external web site.  We’ll look at other possibilities with Actions in Part 8.

Business Data Item Builder

The Business Data Item Builder is a special, invisible Web Part that is responsible for extracting the value from a query string variable and delivering it to a Business Data Item Web Part.  By invisible, I mean it is only visible in edit mode; when in normal operation, it is hidden.

When adding this Web Part to a page, there is no configuration on it other than connecting it to a Business Data Item Web Part.  The way it works is by extracting a variable from the query string (for example CustomerID) and sending it to the Business Data Item Web Part.  It knows what variable to extract by reading the ActionParameter defined in the ADF.

A good way of understanding how the Business Data Item Builder Web Part works is by looking at one of the profile pages.  A profile page, which I will cover in Part 8, is a page that typically displays a single item in some way.  Profile pages can be created automatically when you import the ADF.  For an automatically created profile page, this is comprised of a Business Data Item Builder and a Business Data Item Web Part.

To demonstrate, when clicking the View Profile link shown in Figure 35 above takes us to the auto-generated profile page as shown below.  Notice you only see one Business Data Item Web Part.

Figure 36: Profile for a Single Customer

If we edit the page, however, you’ll notice the Business Data Item Builder.  This web part is automatically connected to the Business Data Item Web Part.

Figure 37: Editing the Profile Page for Customers

That is the only purpose for the Business Data Item Builder, so you will probably not find yourself using it all that often.

With that, this wraps up our discussion on the out of the box Web Parts for the Business Data Catalog.  Let’s turn our attention to the second (and much shorter) half of this article.

Incorporating Business Data within Lists

While the Business Data Web Parts are useful, sometimes you just need to embed external data right within a SharePoint list or library.  For example, perhaps you already have a contact list in SharePoint for all your customers, and you are maintaining this separately from your CRM system.  A better solution would be to add Business Data columns to your contact list and pull in some of this data from CRM.  This means you would only maintain the customer entry in CRM, and these changes are pulled into SharePoint.

Another advantage is that you can see information from multiple sources all merged together.  Let’s say that a list of the products your company sells is stored within a SharePoint picture library.  You can add a Business Data column to the library which can pull the current product name and inventory level from an external ERP database.  Now you can see the product’s picture, the product name, and its inventory in one consolidated view.

When you add a Business Data column to a list, SharePoint will pull the data from the external system and store a cached copy of it in the list.  This is quite different from Web Parts where you are always pulling the real-time value from the external system.  The advantages of caching the data in the list are faster performance and the flexibility of having the data stored right in SharePoint.  The drawbacks are the that the additional columns of data will increase the size of the list (and content database).  Also, for volatile data that changes often, the cached data will quickly get stale.  As you might guess, the common-sense rule of thumb is to only pull data that does not need real-time status.  However, as you will see, it is easy to refresh the cache.

Just like the Business Data Web Parts, you should consider the cached data in the list as read only, and SharePoint does not give you an interface to change it.

Now that you have a basic idea how this works, let’s take a look at the picture library example that pulls the current product name and inventory value from a database.  For this demonstration, I already have a Products entity defined within an ADF.  I have created a picture library that has three product pictures added.  The All Pictures view looks like this:

Figure 38: Initial View of the Pictures Library

From the Settings menu, let’s add a Business Data column. The column named will be called Product Name as you can see here:

Figure 39: Adding a Business Data column called Product Name

When you add a Business Data column, you need to specify the Type, just as you would with the Web Parts.  Below you can see I have selected the Products Type.  I have also added two columns as you can see here

Figure 40: Selecting the Columns from the Products Type

Notice that you can add as other columns if you like.  I have added the ProductName (the main column in the drop down list) and UnitsInStock which gives us the current inventory value.  After we add these two columns, here is how the All Pictures view now looks:

Figure 41: All Pictures View after Adding Business Data Column

This is nice, but you can see that it doesn’t automatically pick up the values from the database.  When you add a Business Data column to a list, you need to map up each item in the list to the matching item in the database.  This is a 1:1 mapping.  Here is how you could manually do this using the UI.  Go to the edit properties for an item, and you’ll see the Business Data column as you see here:

Figure 42: Matching up Product Name Item

In the text box, you can type in a unique product name and click the Check Names icon .  Or you can also click the Browse icon and search for it.  Here is how the browse works:

Figure 43: Finding Matching Row

In the figure above, we are editing the flow regulator picture, so we search for this product by name.  This just executes a search on the Finder method and returns all the products which have this name.  For this product, there’s only one with this name, so we select it.  We are then returned to the edit item page and we accept the changes.  We have now mapped this one product’s picture to its matching database row.  I won’t show you, but I also mapped the other two pictures as well.  When returning to the All Pictures View, you can see that the two columns’ values were pulled from the database and stored in the list:

Figure 44: All Pictures View Now Showing External Data

Remember these values are cached in the list, so when the inventory changes, this will not be automatically reflected in the list.  Without writing code, there is no automated way to refresh this.  It’s very easy for a user to refresh the list, and you do this by clicking the refresh icon listed next to the Product Name column heading.  After clicking refresh, you are greeted with this confirmation page:

Figure 45: Updating Cached Business Data in List

In this case, updating three items is minor—if you had thousands (or more) items, it could take a while!  Security Note: If a user only has read permissions to the list, they will still see the refresh icon but will not be able to refresh its data.

Another drawback to this that you might notice is that it can be quite tedious manually mapping up each item in the list to each row in the database.  You may be able to automate some of this through code as I discuss in this blog article.

At this point, you have seen how to add Business Data columns to a SharePoint list.  Now that these are cached within the list, the data stored behaves almost like regular columns.  You can filter, sort, group, all the usual things.  Remember, though, you cannot edit through the list and have the change stored back in the database.  In other words, the synchronization is not two way.  In fact, the columns that BDC adds to the list are read only.

Wrap Up

Great job on making it all the way to the end.  You didn’t just skip down here did you?  :)  I know this was a long article, but hopefully you were able to go through it quickly, and it gives you a strong grasp on everything you need to know with Business Data Web Parts and incorporating Business Data within your SharePoint lists.

Coming up next in Part 5, we’ll take a close look at how the search engine can crawl BDC data so that your searches encompass your business data as well.  Until then…