Before you create a dashboard, plan the layout first so you end up with a more effective dashboard. If you don’t plan well first, you might run out of space, or the Web Parts won’t quite fill the width of the Web Part zone. Before you create the dashboard, consider designing a wireframe, also known as a page schematic or blueprint, showing the layout of the dashboard, as shown in Figure 6-2. Using the wireframe, you can gather the business requirements and obtain approval for the dashboard from the business users who will be using it. You can also decide which Web Part Page template to use to create your dashboard page.

A wireframe consists of a drawing displaying the layout of the navigation, breadcrumbs, charts, Web Parts, and Web Part zones. You may want to provide the wireframe design to a designer capable of providing a custom layout within SharePoint, so that you can add Web Parts required by the business to the layout.

Creating a wireframe enables you to design a SharePoint Web Part page with Web Part zones that fit well with the Web Parts displaying the business-critical information. Microsoft Visio is a great tool to use to design the wireframe, as it provides a number of graphic stencils for wireframe creation. Other useful wireframe creation tools are available from websites such as www.wireframe.com.

Figure 6-2. Before you create a dashboard, design a wireframe to show its layout.

The Enterprise edition of SharePoint Server 2010 includes PerformancePoint Service (PPS), which allows you to analyze data in SharePoint through the use of dashboards, scorecards, reports, and key performance indicator Key Performance Indicators (KPIs). PPS is a service application that requires configuration.

Once you have configured the PPS service application, you can create a Business Intelligence Center, a special type of SharePoint site containing libraries for storing dashboards, data connections, and content. You can configure connections to SharePoint lists, SQL Server Analysis Services, SQL tables, and Excel Services. Upon creating a Business Intelligence Center site, you can launch a client application called PerformancePoint Dashboard Designer from the Data Connection Library. Figure 6-3 shows how to create a new PerformancePoint data source connection, which will open the PerformancePoint Dashboard Designer.

Figure 6-3. Use the PerformancePoint Dashboard Designer to create a new dashboard.

The PerformancePoint Dashboard Designer enables you to build very advanced dashboards. Although you can connect SQL tables as a data connection in a PerformancePoint dashboard, you cannot select an external content type (ECT) as a data source for your connection. It stands to reason that since you can create an external list from an ECT, and external lists act like SharePoint lists, that you can configure as data connections in PerformancePoint, you can configure an external list as a data connection in PerformancePoint. If you try this, however, you will receive an error message, since the data itself doesn’t actually reside within SharePoint. Figure 6-4 shows that PerformancePoint can’t be used to connect to external lists.

Figure 6-4. Attemping to connect to an external list through PerformancePoint Dashboard Designer doesn’t work.

Using PerformancePoint isn’t the only way to create dashboards in SharePoint. Whether you use SharePoint Foundation 2010 or SharePoint Server 2010, you can easily create dashboards that display external data.

You can create a Web Part page using your browser or SharePoint Designer, and then add Web Parts to the page to create your dashboard. If you have the Enterprise edition of SharePoint Server, you can use a report library to create dashboards. Pages created in a report library already contain Excel Services Web Parts and KPI Web Parts, which you would otherwise need to add manually to a page. These Web Parts are useful on a dashboard, and many organizations use them on their webpages.

You can create a report library using the following steps:

Figure 6-6. A Web Part page with a status list displays in the browser.

The report dashboard consists of two Excel Web Access Web Parts, a List Web Part that displays the contents of the status list, an Apply Filters Button Web Part, a Related Information Summary Links Web Part, a Summary Web Part, and a Contact Details Web Part. Notice that no Business Data Web Parts are used to display data stored in external systems; this is the subject of the next section. The Excel Web Access Web Part and status list are explained later in this chapter.

You can use the Business Data List Web Part to display multiple rows of data from an external source, and you can easily configure it using the Web Parts properties. While you can further modify this Web Part within SharePoint Designer 2010, you can configure it using the browser to a stage where it displays data. Using the Edit View option within the Web Part, you can configure the order of the columns, filters, sorting, pagination, and item limits.

Upon adding the Business Data List Web Part to your page, click the down arrow to the far right of the Web Part title and then click Edit Web Part to open the Business Data List tool pane. You use the Business Data List tool pane to configure the Type property to select the external system where the data you want to present is stored. The Type property is referring to the ECT.

The lookup icon allows you to display the list of available ECTs. The list of ECTs is security trimmed, so it is possible that even though you can view external data within another Business Data List Web Part, you may not have permission to configure the same external data yourself.

Once you have configured the Type property, at the bottom of the tool pane, click Apply. The data within the Web Part itself displays. Notice that the title of the Web Part has changed to match the title of the ECT, as shown in Figure 6-7.

Figure 6-7. The Business Data List Web Part tool pane shows the ECT selected.

Other properties for Business Data List Web Part are explained later in this section. Figure 6-8 displays a Business Data List Web Part that is configured but not formatted.

Figure 6-8. The Business Data List Web Part is configured to display the vSalesPersonSalesByFiscalYears view from the Adventure Works database.

Edit View

While the Web Part page is in Edit Page mode, the Business Data List Web Part contains an Edit View link in the top-right corner. Clicking Edit View allows you to configure some basic view options, such as sorting and filtering, as well as other options you cannot set through the browser. For example, the sales figures in Figure 6-8 are not displayed in currency format. In fact, it is strange that the sales figures are displayed to more than two decimal points. The Edit View option does not allow you to format values, but you can format values either when you create the ECT using Visual Studio or when you modify the view using SharePoint Designer.

The Edit View page is divided into six sections:

• Items to Retrieve When you edit the view of your Business Data List Web Part in the browser, you will first notice that you can choose which items are displayed. This option is dependent upon creating filters when configuring the ECT. The default option is to retrieve all items from the external data source. However, when you create filter methods in the ECT, you can choose to allow the user to set the filter value or you can set a filter value yourself, which the user can modify if required. In Figure 6-9, these options are not available, since filter methods were not created in the ECT.

  

  Figure 6-9. The Items to Retrieve options are unavailable within the Edit View page of a Business Data List Web Part if filter methods were not created in the ECT.

• Item Limit When an external data source contains thousands of rows of data, you can use a filter method so that the ECT returns only a subset of the external data. You can also set the item limit for the ECT, which is especially useful when configuring a dashboard. For example, you may want to show only the top 10 performing sales representatives, instead of all 200 sales representatives.

• Columns This section allows you to select which columns you want to display, set the order of the columns from left to right, and set one of the columns as the Title column. The Title column displays a drop-down list when you hover over it and allows actions to be triggered against the item selected. For example, an action could be a search for a salesperson within the SharePoint search center. Figure 6-10 shows how you can figure the view of the Business Data List Web Part within the browser.

  

  Figure 6-10. You can configure the columns to display and the column order within the Edit View options of a Business Data List Web Part.

• Sort This section allows you to sort by up to two columns in either ascending or descending order. For example, if you want to show the salesperson with the most overall sales for the past couple of years first, configure the view to show the most recent sales figures in descending order, and then sort by the previous year in descending order, as shown in Figure 6-11.

  

  Figure 6-11. Set the Sort options within the Business Data List Web Part Edit View page.

• Filter Although you may have already configured filters when you created the ECT, it is possible to hard-code the filter values for the view of the Business Data List Web Part. When you set these filters, users cannot change them using the filter toolbar within the Business Data List Web Part.

• Pages The final section is for pagination. Here you can simply set the number of items you want to display on each page, as shown in Figure 6-12.

Figure 6-12. You can configure the filter and pagination options within the Edit View of the Business Data List Web Part.

Formatting Data

After you configure the view of the Web Part, the data is presented close to your requirements, but the lack of formatting may annoy users. You can address this issue by modifying the XSL of the Web Part.

Note

The Extensible Stylesheet Language (XSL) family of languages, including Extensible Stylesheet Language for Transformations (XSLT), is coding language for styling Extensible Markup Language (XML). XSLT provides similar results to that of a Cascading Style Sheet (CSS) file for HTML. You can use XSL within most SharePoint Web Parts to provide styling, branding, positioning, and general look-and-feel customizations to present the contents of the Web Part. You can also embed JavaScript in many cases within the XSL to provide custom functionality.

In the Business Data List Web Part tool pane, you can find the XSL Editor button in the Data View Properties section. When you click the button, a Text Editor – Webpage Dialog box appears, with the XSLT equivalent of the setting you configured in the Edit View page. The dialog box does not offer a good user experience for modifying XSL. However, you can copy and paste the XSL into SharePoint Designer 2010 or Visual Studio and use these tools to amend the code. It is certainly recommended that you back up the XSLT code before making any changes. Figure 6-13 shows the XSL editor built into the Web Part.

Figure 6-13. Modify the XSLT using the XSL Editor dialog box for the Business Data List Web Part.

If you used the Business Data List Web Part to display data from the Adventure Works database, then your currency column displays more than two decimal places. Using Visual Studio, locate the XSLT code that contains the column name. In the example for this book, the 2003 sales data has a column name of @_x0032x003, and the XSLT code is as follows:

<xsl:value-of select="@_x0032_003/>

To format the sales data to two decimal places, the code should be:

<xsl:value-of select="format-number(@_x0032_003, '$###0.00')" />

Copy the XSLT back into the Text Editor – Webpage Dialog box for the Business Data List Web Part, which results in the 2003 column being formatted as shown in Figure 6-14.

Figure 6-14. You can format the column 2003 values as currency within the Business Data List Web Part using SharePoint Designer 2010.

If you are changing the XSLT, make sure to keep a copy of your modified XSLT. When you use the Edit View menu and make even subtle changes, the XSLT for the Web Part is regenerated and will wipe out any changes you made manually using the XSL Editor dialog box. If you add another column to the Web Part, such as the 2002 column, again you will lose the formatting on the 2003 column. It is good practice to store your XSLT in a separate file, upload that file to a document library, and then type the URL of the file in the XSL Link text box in the Miscellaneous section of the Business Data List Web Part tool pane.

Visual Studio does not provide a WYSIWYG experience when editing SharePoint data, so you need some understanding of XSL to format the business data. SharePoint Designer does have a WYSIWYG design view, though, so you can use SharePoint Designer to achieve some formatting changes without writing any XSL.

Using SharePoint Designer to Format Data

SharePoint Designer 2010 is an excellent tool for editing the Business Data Web Parts. You can use it to apply conditional formatting and implement KPIs. To modify the Web Part in SharePoint Designer 2010, follow these steps:

1. Click Site Actions and then click Edit in SharePoint Designer.

2. You can view all the files, reports, and dashboards stored in the report library by clicking All Files in the Navigation pane. When you select your dashboard, the Settings page appears.

3. In the Customization area, click Edit File to open the page in normal edit mode.

4. The Business Data List Web Part displays with sample data and the filter toolbar. From here, you can use the Common xsl:value-of Tasks dialog box to amend the number format, as shown in Figure 6-15.

In addition to formatting the columns, you can insert three images in place of the sales values for 2002. The images will be traffic light indicators—red, yellow, and green—which you will configure to display based upon conditional formatting.

Prior to formatting the page, upload the three traffic light images into a document library. In SharePoint 2010, a good place to upload images that represent data and are not uploaded by team members is the Site Assets Library, if you are on a team site. You can then use the Insert tab to insert the three traffic light images into a single row.

Figure 6-15. You can format the currency columns using SharePoint Designer 2010.

Alternatively, you can copy and paste the three traffic light images into a single row. SharePoint Designer then automatically displays a copy of the images in each row. Within a single row, click one of the traffic light images—for example, the green image—and then on the Options tab in the Data View Tools tab set, click Conditional Formatting, and then click Show Content. The Conditional Criteria dialog box appears, where you can set the conditional formatting to show the image only if a certain condition is met. For example, when the value in the 2004 sales column is greater than the value in the 2003 sales column, the green traffic light indicator displays. You can then repeat this procedure for the red image, and set the condition that when the 2004 sales column is less than 2003 sales, the red traffic light indicator displays. In addition to displaying images, you can apply formatting changes to the font or to the background color of each row. Figure 6-16 illustrates adding the images and applying conditional formatting to show the graphics.

You can implement many other formatting changes using SharePoint Designer. For example, you can display alternate rows with different background colors and change the page font to your organization’s official font. SharePoint Designer is a great place to experiment, as long as you are in a development environment. If you do not have a development environment, then experiment in a separate site collection, and if that is not possible, create a separate page on the production site.

Figure 6-16. You can create KPIs using conditional formatting in SharePoint Designer 2010.

Figure 6-17 shows the Business Data List Web Part within the browser once you save the changes you made using SharePoint Designer.

Figure 6-17. The completed Business Data List Web Part displays formatting and KPIs.

Now that this Web Part is on the dashboard page, users can modify the filters themselves to select the information they want to see. For example, a user may want to show only the salespeople within a specific region, as shown in Figure 6-18.

Figure 6-18. Users can filter the completed Business Data List Web Part for a specific region.

The Business Data List Web Part is useful when you require a read-only view of the business data. It enables you to format, filter, and sort the data easily through either the Edit View option or SharePoint Designer customization.

The Business Data Item Web Part was designed to show read-only data. This Web Part, unlike the Business Data List Web Part, shows just one row at a time.

You can configure the Business Data Item Web Part to display a specific row in columnar format by providing it a unique value within a column marked as the identifier. For example, using the Adventure Works database, you may provide this Web Part a value of 11001, which is the identifier for Eugene Huang within the Sales.vIndividualCustomer table. You do not want to continue reconfiguring the Web Part each time you want to view a different customer’s details, so you will likely use this Web Part to dynamically provide the row that you are looking for. The identifier value can be passed to the Business Data Item Web Part via a Web Part connection. You can connect the Business Data Item Web Part to a Business Data List Web Part, or from any other Business Data Web Part, including the Business Data Item Builder. The Business Data Item Builder Web Part takes a value from the URL parameter and passes it to the consuming Business Data Item Web Part, allowing you to open detailed information on another page, in the same way the profile page works for business data.

The Business Data Item Web Part shows detailed information for one particular row. Displaying a number of rows, such as CustomerName, Address, Address2, Town, City, State, Zip, Phone, Fax, and Contact Name, in a Business Data List Web Part uses too much real estate on the page. To overcome the space limitations, you can display just CustomerName and City. Then when the user clicks CustomerName, the other columns appear within the Business Data Item Web Part, as shown in Figure 6-19, where the Business Data Item Web Part is connected to the Business Data List Web Part.

Figure 6-19. The Business Data Item Web Part can consume a connection from the Business Data List Web Part to show a specific row of data.

You can easily configure the properties for the Business Data Item Web Part. As with the Business Data List Web Part, you choose the ECT that you want to display. You can then optionally enter the identifier value for the item you want to display, as shown in Figure 6-20.

Figure 6-20. Enter the identifier value for the item you want to display in the property pane of the Business Data Item Web Part.

After you enter an ECT for the Business Data Item Web Part in the Type text box, click OK to close the tool pane. The page is now in edit mode, which allows you to configure the Business Data Item Web Part to consume a connection. You can set the connection either from the providing Web Part or from the consuming Web Part—the approach you choose doesn’t make any difference. Figure 6-21 shows how to configure the Web Part connection from the providing Business Data List Web Part.

Figure 6-21. Configure the connection between the Business Data List Web Part and the Business Data Item Web Part to provide a summary and detailed view of the data.

You also use the Business Data Item Web Part on a profile page, which you create either in SharePoint Designer 2010 or within the SharePoint Central Administration website in the ECT settings. The profile page displays when you click the search result or when the action is clicked from one of the BDC Web Parts. Typically when you search for a document or list item, clicking the hyperlink associated with the item results in the item opening. This cannot happen with external data, so the summary information is shown in the search results, but when the hyperlink is clicked, the identifier for the returned item is passed via the URL to the profile page for that ECT. The profile pages look a bit basic, but you can modify them through XSL or SharePoint Designer 2010. Figure 6-22 shows a default profile page with the Business Data Item Web Part.

Figure 6-22. The profile page containing a Business Data Item Web Part is displayed.

If you edit a profile page, you will notice that the normally hidden Business Data Item Builder Web Part is displayed. The purpose of the Business Data Item Builder Web Part is to take the parameter from the URL passed via the custom action or the search results page, and pass it via a Web Part connection to the Business Data Item Web Part (as shown in Figure 6-23).

Figure 6-23. A profile page in edit mode shows the Business Data Item Builder Web Part.

You can also modify the Business Data Item Builder Web Part using XSL. Although you cannot modify the Web Part in SharePoint Designer 2010, you can modify the page itself.

The Business Data Related List Web Part is very similar to the Business Data List Web Part in that it displays multiple rows of data. However, it is designed to show data related to data displayed from another external content type (ECT). The ECT is related to the second ECT with an association, which you need to create when defining the ECT.

When you design a relational database, you will often join two or more tables using a primary key field in one table that points to a foreign key field in the second table. The table with the primary key is often referred to as the “one” side and the related table as the “many” side in a one-to-many relationship. An example of this type of relationship is that one customer can place many orders.

Once an association exists, you can use the Business Data List Web Part to display the data from the one side of the relationship (the providing ECT). You can then configure the Business Data Related List Web Part to show the content from the many side (the receiving ECT).

Within the Web Part properties of the Business Data Related List Web Part, after choosing the correct type, select the relationship that you want to use. It is possible to have more than one relationship—for example, customers may be related to orders as well as contacts.

You can customize how you view the data in the same way as with the Business Data List Web Part. Figure 6-24 shows the configuration of the Business Data Related List Web Part.

Figure 6-24. The properties of the Business Data Related List Web Part, with the Type and Relationship fields populated to display items from an ECT.

Once you set the Web Part properties and click OK, the Business Data Related List Web Part displays a message indicating that you must use Web Part connections to connect the two Web Parts. As with the Business Data Item Web Part, you can provide or consume the connection from either Web Part. Figure 6-25 shows the connected Business Data List and Related List Web Parts.

Figure 6-25. The Business Data List Web Part and Business Data Related List Web Part are about to be connected.

Once you’ve connected the Web Parts, an icon appears next to each row in the Business Data List Web Part. Clicking the icon fires the connection to the Related List Web Part, which in turn filters the results based on the provided value. Figure 6-26 shows how to configure the connection between the two Web Parts.

Figure 6-26. The Business Data Related Web Part consumes the connection from the Business Data List Web Part.

Once the connection has been created between the Business Data List and Business Data Related List Web Part, you are able to see the related items shown within the two Web Parts (see Figure 6-27).

Figure 6-27. The Business Data List Web Part and the Related List Web Part are connected and displaying related data.

It is worth noting that you can achieve similar functionality using standard Web Part connections in SharePoint Designer between two Data View Web Parts or two Business Data List Web Parts. Building the connection within SharePoint Designer offers some advantages, such as the ability to join Web Parts across two pages within the same team site and also build connections to filter data based upon two columns that are not strictly primary and foreign keys. SharePoint Designer has some limitations when building associations between ECTs, mainly around a table that contains a composite key. A composite key is where a unique row is identified by two columns as a combination and not one unique column. SharePoint Designer requires that each table use a single column as the unique identifier to build an association.

The Business Data Connectivity Filter Web Part is especially useful when you build a dashboard that displays data from an external source. You may already be familiar with some of the other out-of-the-box filter Web Parts, such as the Current User Filter Web Part. You can add the Current User Filter Web Part to a page, and then connect to a List or Content Query Web Part to filter the results based on the current user. For example, you can only display tasks where the assigned-to user is equal to the logged-on user.

The Business Data Connectivity Filter Web Part works in a similar way. The idea is that you add the Business Connectivity Filter Web to the Web Part page and map it to a specific ECT. You then choose a Value column, such as Period, that stores the year’s quarter, such as Q1, Q2, Q3, or Q4. The value from the Period column is passed to other connected Business Data Connectivity Web Parts on the same page, allowing the user to select a period such as Q1 or Q2 and see all the data for the connected Web Parts for just that period. Using the Business Data Connectivity Filter Web Part becomes an easy way to filter all the data on the same Web Part page without requiring reconfiguration of the way the other Web Parts display the data. You select the value you want to pass to the connected Web Parts from a single dialog list that shows the data from the ECT.

You can set the Filter Name, which is a value that populates the Web Part Connections menu, and then select the ECT that contains the data you want to use as the filter. Figure 6-28 shows the Web Part with a view of Sales Person by Fiscal Year. The Value Column is the column that, when selected, contains the value sent to the connected Web Parts, where it will be used to filter their contents. You can also select a Description Column. For example, SalesPersonID contains only unique numbers, and a user visiting the page would not know who the salesperson was by looking at the number, which is the employee ID. The FullName column stores the name of the salesperson, so if you select the Full-Name column in the Description Column drop-down list, the FullName value displays instead of the SalesPersonID. A user will then know the name of the salesperson the user wants to see information about, and when the user selects the FullName of that salesperson, the data from the value column, the SalesPersonID, is sent to the consuming Web Part.

The purpose of configuring the Web Part is to provide dashboard readers the ability to initially see the performance of all salespeople as a default, but then to enable users to select a particular salesperson to see that salesperson’s individual performance. The dashboard is then an interactive page, as the data is dynamically redisplayed depending on the interaction of a user with the page.

Now that you have configured the Web Part, check that you are able to select the required data before connecting the Web Part to other Web Parts on the page. Many of the Business Data Connectivity Filter Web Parts properties appear dimmed because they are unavailable to change once the Web Part is connected to others.

To test that the Search dialog box is returning data, click the Select External Item(s) icon () within the Business Data Connectivity Filter Web Part.

Figure 6-28. The Business Data Connectivity Filter Web Part properties are configured to pass the SalesPersonID to other consuming Web Parts on the same Web Part page.

The Search dialog box appears, and you are able to select a row. You should then see the Description Column displaying a value within the Business Data Connectivity Filter Web Part, as shown in Figure 6-29.

Figure 6-29. The Business Data Connectivity Filter Web Part search dialog box displays, allowing you to select items.

The final task is to connect the Business Data Connectivity Filter Web Part to other Web Parts on the same page. You can get strange results when configuring the connectivity from the Business Data Connectivity Web Part. A good practice is always to consume the connection from the Web Part that is to be filtered. That way, the behavior appears to be consistent.

To consume the connection, edit the Web Part page and click the shortcut menu of the consuming Web Part. In this example, you are consuming the connection from a Business Data List Web Part. Figure 6-30 shows how to configure the Web Part to consume the connection. You can do this for each Web Part to be filtered, including those that are also providing a connection.

Figure 6-30. Configure the Business Data List Web Part to consume a connection from the Business Data Filter Web Part.

After configuring the connection, a dialog box appears that asks you to map the column that contains the filter value, such as SalesPersonID, which in turn matches the SalesPersonID value being sent from the Business Data Connectivity Filter Web Part, as shown in Figure 6-31.

Figure 6-31. Set the Consumer Field value within the configuration of the Business Data Connectivity Filter Web Part connection.

The Business Data Connectivity Filter Web Part now filters the results within the connected Web Parts based upon the filter value selected.

Each ECT you create can have custom actions associated with it. The custom actions consist of a URL and a parameter, allowing an action to be run against a particular row. An example of an action is the View Profile link. If you create a profile page for an ECT, the View Profile link is automatically created as an action for the ECT. The profile page consists of a Business Data Item Web Part showing by default all the columns and values for a selected row. The row is selected when you trigger the custom action.

It is possible to create other custom actions. For example, consider a Customer ECT and how the user might get information they need about a customer, such as conducting an Internet search for more information about that customer. Opening a browser window, navigating to a search engine, and then typing the company name that you wish to search on is time-consuming. Using a custom action, you can search for the customer in a new browser window by configuring a custom action to pass through the Company Name column of the selected row of data. The user may search for the customer name using a website such as LinkedIn, Google, or Bing. These actions can be built using a URL. For example, the URL for a Bing search is similar to the following:

http://www.bing.com/search?q=Adventure+Works&qs=n&form=QBLH&filt=all&pq=adventure+works&sc=8-
15&sp=-1&sk=

This example is specifically searching for “Adventure Works.” But what if you don’t know yet which company name will be searched? In that instance, you don’t use specific words in the URL, but instead use parameters. The URL will look similar to the following:

www.bing.com/Search?{0}

The {0} is a placeholder for the column value. When you create the custom action, you choose the column value, such as CompanyName, that will replace the {0} at runtime against the selected item.

Actions can be triggered from most of the Business Data Web Parts. The Business Data List, Related List, and Business Data Item Web Parts contain an Actions drop-down list. You can also display the actions within the Business Data Actions Web Part, as shown in Figure 6-32, which displays two custom actions: View Profile and Search on Bing.

Figure 6-32. The configured Business Data Actions Web Part displays two actions: View Profile and Search on Bing.

You can configure the Business Data Actions Web Part to show the actions for a specific ECT, and then a specific row via an identifier. However, you do not have to hardcode an identifier value for a specific row in the properties of the Web Part, as this will not be known to you at the time of configuring the Web Part. Again, you can use Web Part connections to join the Web Part to consume the custom actions for another Web Part to select the row. That way, when you click an item in a Web Part, such as a Business Data List Web Part, the custom actions are ready to run against the selected item.

In addition to being able to choose the style formatting for the display of your custom actions, you can customize which actions will display. For example, perhaps you don’t want all actions to display, if they aren’t appropriate. Figure 6-33 shows the configuration of the Business Data Actions Web Part.

Figure 6-33. Configure the Business Data Custom Actions Web Part to show the actions for an ECT.

The Business Data Custom Actions Web Part is a great addition to any business data dashboard, as it allows you to better arrange the dashboard. For example, you can hide the Actions menu from all other BDC Web Parts to obtain more real estate on the page and keep the actions neatly configured in one area.

As you probably realize from the name of this Web Part, it is not a BCS-specific Business Data Web Part, but you can use it with BCS. The Chart Web Part allows you to create most types of charts, including pie, bar, line, and column charts for many different data sources. The data sources include:

A SharePoint dashboard is likely to present business data where the data source definitions are not limited to those stored in the BCS metadata store, but come from other data sources as well. A chart allows you to display the data in a manner that is sometimes more readable on a dashboard than displaying the raw data within a table or grid. In this section, you will learn how to configure the Chart Web Part for data source definitions stored in the BCS metadata store.

The Chart Web Part displays sample data in a 2D column chart when you first add it to the Web Part page. You configure the Web Part not through its tool pane like the other Web Parts described in this chapter, but through wizards triggered from the two links at the top of the Web Part, as shown in Figure 6-34.

Figure 6-34. This Chart Web Part displays sample data.

To configure the Chart Web Part, follow these steps:

To change the chart’s look and feel, click the Data & Appearance link at the top of the Web Part, as shown in Figure 6-38, to display the Data Connection & Chart Appearance Wizard page, where you click Customize Your Chart.

You can customize the look and feel of the chart, including changing the chart type, setting a title and legend, and configuring the size of the chart in pixels. The chart tends to be small by default. Changing the default size from 300 pixels to around 900 pixels wide makes a big difference in the readability of the chart. Figure 6-39 shows some of the chart types you can choose from.

Figure 6-38. Display the configured Chart Web Part prior to changing the Data & Appearance settings to provide a better visual display.

Figure 6-39. Select the chart type in the Data & Appearance settings.

After you complete the wizard, you can plot the Adventure Works Sales by Fiscal Year data in a chart as well as set it to display as a Business Data List Web Part within the dashboard, as shown in Figure 6-40.

Figure 6-40. The completed Fiscal Year by Sales Region chart shows sales-related data from the Adventure Works database.

Excel Web Access is an extremely powerful service within SharePoint Server 2010. Excel Web Access allows Excel workbooks to be published to a report library, which is essentially a special type of SharePoint document library. The report—an Excel workbook—can then be displayed using the browser, allowing information workers to view workbooks. The data in the workbooks can be changed by a user through the Excel Web Access Web Part by modifying certain parameters or values. Changing the values and parameters within the Web Part can provide updated calculations. For example, the Excel Web Access Web Part may display part of a workbook for mortgage calculations. Exposing the parameters for the borrowed amount or interest will affect the monthly payment.

The Excel Web Access Web Part allows you to display objects from a Microsoft Excel workbook stored within a report library. These objects could include a chart or data contained within a range or formatted as a table. An organization’s business data is often stored within spreadsheets and not just in relational databases that can be consumed through BCS. The Excel Web Access Web Part therefore deserves a mention in this chapter, as it is likely to contribute to your business dashboards.

An Excel workbook is created either from within a report library or from the Excel client application and then published to the Reports Library. Using the Excel Web Access Web Part, you can select the workbook and the object you want to display within the Web Part.

Figure 6-41 shows a sample Excel workbook containing a table of sales-related data and an Excel chart. The workbook is stored within a report library.

You configure the Excel Web Access Web Part using its tool pane, which allows you to browse to the workbook and then type the name of the object you want to display. Figure 6-42 displays the properties of the Excel Web Access Web Part.

You can make many refinements to the Excel Web Access Web Part, including configuring the Web Part title, setting the type of toolbar, and choosing the options available on the toolbar. Figure 6-43 shows the configured Excel Web Access Web Part.

Figure 6-41. This sample Excel Web Access report contains sales-related data and an Excel chart.

Figure 6-42. You can configure the Excel Web Access Web Part to display a chart from an Excel workbook.

Figure 6-43. The configured Excel Web Access Web Part displays the chart from an Excel workbook.

The Excel Web Access Web Part can also consume filters from other Web Parts on the page, such as the Text Filter Web Part. This allows different objects to be displayed based upon the filter value.

Most dashboards are likely to display key performance indicators (KPIs) of some sort that show how well different areas of the organization are performing. KPIs usually consist of a target and an actual value against a business goal, such as revenue per quarter. Traffic light indicators provide a visual cue of whether or not a particular goal was met. For example, assuming the revenue for the quarter was $50,000, but the target was $40,000, a green traffic light would display, indicating that the quarter revenue equaled or exceeded the target value.

SharePoint Server provides a Status List template that enables you to define your goals and targets within a SharePoint list. The Status List contains the definition and can dynamically obtain the actual value against a target from multiple data sources:

Notice that BCS is not in the list of data sources. However, if your data resides in a Microsoft SQL Server database, then in place of using BCS, you could use SQL Server Analysis Services as a data source for your indicators.

A common misconception is that you can base a KPI on data from a BCS external list instead of using an external content type (ECT). Unfortunately, this is not achievable, as the data presented in an external list does not reside within SharePoint. Figure 6-44 displays the error message that appears if you try to create a new status indicator definition with an external list.

Figure 6-44. When you attempt to configure a status indicator against an external list, you’ll get an error message letting you know this isn’t possible.

If you do create a status indicator against one of the other data sources, once you have created the list item, you are able to display the KPI itself within the Status List Web Part. The Status List Web Part displays the description, goal, actual value, and status indicator, as shown in Figure 6-45.

Figure 6-45. The configured Status Indicator List Web Part displays indicators against sales-related data.

The Status Indicator Details Web Part is virtually the same as the Status Indicator List Web Part, except that it focuses on just one of the indicators instead of providing a list of them. Within the tool pane of the Indicator Web Part, specify the location of the status list along with the indicator you want to display, as shown in Figure 6-46.

Figure 6-46. The configured Status Indicator Details Web Part displays a KPI in detail.

Because BCS is not one of the available data sources for the status indicators, you can use the Data View Web Part in SharePoint Designer or customize a Business Data List Web Part to show KPIs, as described earlier in this chapter.

The Visio Web Access Web Part is the last Web Part that appears in the business data category. The Visio Web Access Web Part is similar to Excel Web Access Web Part, in that you can display documents within the browser rather than in the client application. Visio drawings are another method of storing data diagrams and are therefore considered business data, albeit a type not associated with BCS.