Using Business Connectivity Services and Visual Studio Add-ins to Create PowerPoint Charts

Article
10/20/2016

Summary: Business Connectivity Services (BCS) in SharePoint 2010 provides access to data that is stored in external systems outside of SharePoint 2010. Visual Studio Tools for Office (VSTO) creates solutions that extend the capabilities of Microsoft PowerPoint 2010. This article shows how to use VSTO to insert or update a chart in PowerPoint 2010 that is created by using data delivered by BCS. The result is a PowerPoint 2010 chart that you can easily update with the most current data from the external system.

Published: April 2011

Provided by: Bob McClellan

Contents

Overview
Creating the External Content Type
Creating and Deploying a Data Solution for the ECT
Explanation of the VSTO Add-In for PowerPoint
Testing the Add-In and Updating the Chart
Conclusion
About the Author
Additional Resources

Download code

See video

Overview

The basis of this technical article is a scenario in which a sales manager must create a monthly PowerPoint 2010 presentation to report sales figures by territory. In the past, the manager retrieved the numbers from a standardized report, and then typed them into an Excel 2010 worksheet that was connected to the PowerPoint chart. Although the process is not time consuming, it is error prone. The sales figures are at least six figures each and the manager must take care to match each territory with the correct number. Although the manager must update several charts in the presentation, this article discusses only one such chart.

Before the manager can easily update the presentation directly from the data, you must complete the following steps:

Create an External Content Type (ECT) in SharePoint 2010 that can access the data that the manager needs.
Create a data solution package that makes the ECT available from a Visual Studio Tools for Office (VSTO) project.
Deploy the data solution to a development environment and create the VSTO solution for PowerPoint.
Deploy the data solution and the VSTO solution to the manager's computer.
Test the solution by creating and updating the PowerPoint chart.

Before you begin, make sure that you have the following:

Access to SharePoint 2010 Central Administration, including the Business Data Connectivity (BDC) service.
Access to SharePoint Designer.
A SharePoint 2010 web application that has a site collection. For more information, see Create a Site Collection (SharePoint Server 2010).
Access to Microsoft SQL Server.

Note

You can use the same instance of SQL Server that is used by the computer running SharePoint Server 2010.
A sample database for SQL Server and permissions to modify the tables and data. (The examples in this article use AdventureWorks. For information on how to install this sample database, see AdventureWorks 2008 R2.)
Microsoft Visual Studio 2010 Professional.
Microsoft PowerPoint 2010.
Microsoft Excel 2010.

A virtual machine that meets all these requirements, 2010 Information Worker Demonstration and Evaluation Virtual Machine (SP1), is available for download from Microsoft. If you use that virtual machine, you can follow the steps in this article exactly. You need only the "A" machine.

The examples in this article are more meaningful if you have a general understanding of simple SQL queries and views. In addition, a general understanding of XML is useful because as you work through the examples, you look at the XML that is used to define BDC models.

Creating the External Content Type

In this section, you create the External Content Type (ECT) by using SharePoint Designer. The ECT defines what data is available from an external system and how it can be used (for example, read only or read/write). To create the ECT, you must complete the following tasks:

Verify that the SharePoint web application has a connection to the Business Connectivity Service.
Configure the Business Data Connectivity Service.
Create the ECT using the SalesTerritory table.
Create an external list from the ECT.
Test the ECT by using the external list.

The first step is to verify that the web application has a connection to the Business Data Connectivity Service. If it does not, you must add that connection.

To verify the Web application connections

Start SharePoint 2010 Central Administration.
Under the Application Management heading, click Manage web applications.
Select the web application that you plan to use for these examples (for example, Intranet).
Click Service Connections on the ribbon.

Figure 1. Web Applications ribbon
Verify that Business Data Connectivity Service is selected. If it is not selected, select it, and then click OK. If you cannot select it, use the drop-down list at the top to change from default to custom first.

Figure 2. Web Applications connections dialog box

Now configure the Business Data Connectivity Service. You must set the permissions to allow access to the data from your account.

To configure the Business Data Connectivity service

Start SharePoint 2010 Central Administration.
Under the Application Management heading, click Manage service applications.
In the Name column, click Business Data Connectivity Service.

Figure 3. Manage Service Applications list
On the ribbon, click Set Metadata Store Permissions in the Permissions group.
If you do not see the account that you want to use in the dialog box, type the account name in the text box next to the Add button.
Click Add.
In the bottom list, check all the check boxes (Edit, Execute, Selectable In Clients and Set Permissions).
Select the Propagate permissions to all BDC Models, External Systems and External Content Types in the BDC Metadata Store box.
Click OK.

Figure 4. Setting permissions for Business Data Connectivity

Next, you create an external content type (ECT) for the SalesTerritory table from the AdventureWorks database. You can create the ECT in any SharePoint site, but I suggest using intranet.contoso.com. You connect the ECT to an SQL Server as an external system, and then define the basic operations needed to read and update the ECT. You can create all these operations very quickly by using SharePoint Designer.

To create the External Content Type

Using your web browser, open the SharePoint site where you want to create the ECT (for example, http://intranet.contoso.com). Choose a site where you can create an external list to test the ECT. On the Site Actions drop-down list for the SharePoint site, click Edit in SharePoint Designer.
In the Navigation pane, click External Content Types.
In the New group on the ribbon, click External Content Type.

Figure 5. Creating a new External Content Type
In the External Content Type Information group, next to Name, click New external content type. Type BCSSales and then tab.

Figure 6. Summary view of an External Content Type
Next to External System, click Click here to discover external data sources and...
Click the Add Connection button.
In the External Data Source Type Selection dialog box, choose SQL Server in the drop-down list.
Click OK.
In the SQL Server Connection dialog box, type localhost (or the appropriate server name) in the Database Server box.

Figure 7. SQL Server Connection dialog box
Type AdventureWorks in the Database Name box.
Click OK.
Click the expansion symbol next to AdventureWorks.
Click the expansion symbol next to Tables.
Right-click SalesTerritory and then click Create All Operations.

Figure 8. Data Source Explorer for an External Content Type
Click the Finish button at the bottom of the All Operations dialog box.
Save the External Content Type (click the disk icon above the File tab). A progress dialog box appears. It might take a couple of minutes to save the ECT.

Although you do not use the Create, Update and Delete operations for this example, it is quickest to create all the operations. You could also limit the fields available from the external system. By default, SharePoint Designer includes all the fields in the table. Since the table has a primary key defined, SharePoint Designer uses that as the identifier for the ECT by default. As you can see, SharePoint Designer makes it easy to create the ECT quickly, but also allows for specific customizations when needed.

The final task is to create an external list to test the ECT.

To create and test an external list for salespeople

On the ribbon, click Create Lists & Form.
In the List name text box, type Territories, and then click OK. It might take a minute or two for this process to complete.
Open a web browser for the site where you created the external list. You can use the same browser that you used to open SharePoint Designer, but you must refresh it. The list name, Territories, is in the navigation panel for the site. Click it to see a list of all the territories and their sales figures.

Figure 9. External List Using an External Content Type

That completes the creation and the testing of the ECT.

Creating and Deploying a Data Solution for the ECT

There is more than one way to deploy a data solution for an ECT. In this example, you create a data solution package by using the BCS Packaging Tool and the BCS Artifact Generator. You must download these tools from Microsoft (they are not included with the virtual machine). To create and deploy a solution you must complete the following tasks:

Generate a BDC Model for the ECT from SharePoint Designer.
Use the Artifact Generator to create a subscription file for the ECT.
Use the Packaging Tool to create a data solution.
Execute the data solution.

To create and deploy a data solution for the SalesTerritory ECT

In SharePoint Designer, click External Content Types in the Navigation pane.
In the list of external content types, select BCSSales. To select, click anywhere on the line except the name, which opens the summary view for the ECT.
On the ribbon, click Export BDC Model.
A dialog box prompts you for a BDC Model Name. Type BCSSales and then click OK.
The next dialog box is a standard File Save dialog box. Save the model in a new, empty folder. I suggest that you create a folder in the My Documents folder with a simple name like Package and then a subfolder in Package named Source.
Type Metadata as the File name and then click Save.

Important

You must name this file Metadata.
Start the BCS Artifact Generator.
Type BCSPowerPointAddin for the Solution Name.
Click the Import model... button, browse to the Metadata file that you exported, and then click Open. The model is imported and checked. Click Next.

Figure 10. BCS Artifact Generator
Click Generate artifacts to create the subscription. The subscription file is created in a different folder. Copy or move the subscription file to the Source folder with the Metadata file. You might need to use the Open Artifact Folder link to find the generated file. Click Finish.
Start the BCS Packaging Tool.
Type BCSPowerPointAddin for the Solution Name.
Choose Data Solution for Office Add-In as the Solution Type.
For the Source Folder Path, use the Browse button to find the folder where you saved the BDC Model and created the subscription.
For the Output Folder Path, use the Browse button to find an empty folder for the data solution. I suggest that you create a subfolder in Package (created above) named Output.
Click the Package button to generate the package.
Click the Deploy button to deploy the solution. Follow the installation instructions. You should get a message that verifies a successful installation.

Figure 11. BCS Packaging Tool

For this example, you deployed the solution on the same machine. Note that if you install the created package on other machines, those machines could then access the ECT data through the SharePoint server.

Explanation of the VSTO Add-In for PowerPoint

This section describes the C# program that is included with this article. The section does not describe how to create the program from scratch and does not show all of the code.

If you are not interested in the details of the C# code, proceed to the next section about testing and updating the chart.

The add-in is not very sophisticated, but it can handle most ECTs that you can define in SharePoint without changing the code. There are three main parts to the code. First is the part that adds a new group and button to the PowerPoint ribbon. Second is the part that creates and shows a task pane to get the information about which ECT to use for the chart and which data elements from the ECT to use for the series or X values of the chart. Third is the part that inserts or updates the chart in PowerPoint.

Extending the ribbon

The ribbon.xml file contains the definition for what additional elements appear in the PowerPoint ribbon. If you created this yourself, you could use the ribbon designer instead of writing XML code, but the results would be the same.

<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="https://schemas.microsoft.com/office/2006/01/customui"
    onLoad="Ribbon_Load">
  <ribbon>
    <tabs>
      <tab id="tabBCS" label="BCS">
        <group id="grpBCS" label="BCS">
          <button id="btnBCS"
        size="large"
        imageMso="MeetingsWorkspace"
        enabled="true"
        visible="true"
         
        label="Add External Data to Chart" onAction="OnClick"/>

        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

This defines a single tab with a single group named BCS that contains a single button labeled Add External Data to Chart. There are two links to the C# code. When the ribbon is first loaded, the Ribbon_Load callback method is called. When the button is clicked, the OnClick callback method is called. You can find both of these methods in ribbon.cs. Ribbon_Load is a single line that sets a member variable to the ribbon that just loaded. OnClick creates and shows the task pane.

public void OnClick(Office.IRibbonControl ctrl)
{
  CustomTaskPane taskpane;
  if (TaskPaneLookup.ContainsKey(
        Globals.ThisAddIn.Application.ActivePresentation))
    taskpane = TaskPaneLookup[
        Globals.ThisAddIn.Application.ActivePresentation];
  else
  {
    taskpane = Globals.ThisAddIn.CustomTaskPanes.Add(
      new BCSChartInfo(), "ChartInfo");
    TaskPaneLookup[Globals.ThisAddIn.Application.ActivePresentation] =
      taskpane;
    taskpane.VisibleChanged += new 
      EventHandler(taskpane_VisibleChanged);
    taskpane.Visible = true;
    taskpane.Width = 350;
    BCSChartInfo ChartInfo = taskpane.Control as BCSChartInfo;
    ChartInfo.m_InsertChart += Globals.ThisAddIn.OnInsertChart;
    ChartInfo.m_UpdateChart += Globals.ThisAddIn.OnUpdateChart;
    ChartInfo.PopulateControls();
  }
}

This code references the object, BCSChartInfo, which is the custom task pane that you use to choose the ECT and data to use for the chart. The next section discusses how the task pane accesses the ECT information.

The custom task pane

The code for the custom task pane is contained in the BCSChartInfo files (.cs, .designer.cs and .resx). The most interesting of those files in terms of understanding how it uses BCS is BCSChartInfo.cs. Most of this code is fairly easy to understand, but I will explain each method and show a few key pieces of code from that file.

The btnInsert_Click method processes the button in the task pane that inserts a new slide with a chart. It finds the chosen ECT, known as an Entity in code, calls the GetChartDataObject method to get the appropriate data, and then raises the event that actually inserts the chart.

The GetChartDataObject method, shown below, finds the selected entity (ECT) and then finds the appropriate data for the chosen series value and fills out that data in the ChartDataObject object. The object is then used to actually create the chart, as the next section shows. The task pane enables you to choose multiple X values for each series value. These are all set in the ChartDataObject by looping through the data from the entity.

private ChartDataEventArgs GetChartDataObject(IEntity entity)
{
    ChartDataObject chartObject = new ChartDataObject();
    chartObject.Data = EntityDataLookup[entity];
    chartObject.Entity = ectList.SelectedItem as IEntity;
    chartObject.SeriesName = seriesList.SelectedItem as string;
    chartObject.XValues = new string[chkCategories.CheckedItems.Count];

    int counter = 0;
    foreach (string item in chkCategories.CheckedItems)
    {
        chartObject.XValues[counter] = item;
        counter++;
    }
    ChartDataEventArgs args = new ChartDataEventArgs();
    args.ChartDataObject = chartObject;
    return args;
}

The PopulateControls method sets up the possible values in the task pane. It searches through all available entities (ECTs), and that list is set for the first drop-down list selection. The data available from that entity is also saved so that the other task pane drop-down lists can be quickly filled out when a particular ECT is selected. The following are the key statements that retrieve the external data.

InstanceEnumerator = entity.FindFiltered(
    entity.GetDefaultFinderFilters(),
    entity.GetMethodInstances(MethodInstanceType.Finder)[0].Value.Name,
    entity.GetLobSystem().GetLobSystemInstances()[0].Value,
    OperationMode.Online);
System.Data.DataTable dt = 
    Catalog.Helper.CreateDataTable(InstanceEnumerator);

The first statement requests a finder method for the entity/ECT. This is the Read List operation that you defined in SharePoint Designer. The method is executed and the result is stored in the InstanceEnumerator variable. That result is then used to create a DataTable object that is easier to use to access the resulting data.

The comboBox1_SelectedIndexChanged method updates the series and data category choices based on the ECT that is selected.

The btnUpdate_Click method is nearly identical to the insert method except that it raises an event that attempts to update the selected chart.

Insert or update the PowerPoint chart

The Update and Insert methods are defined in ThisAddIn.cs. Both of these methods use the Microsoft Office Document Object Model objects to do the work. You might notice that the Update method only updates a selected chart. That is a simple way to make sure that the Update is updating the correct chart. The Update method also calls the AssignExternalDataToChart method to set the updated data. The data from the ECT has already been retrieved into the ChartDataObject object as shown in the previous section.

Testing the Add-In and Updating the Chart

Locate the Visual Studio solution in the first folder of the sample code. Open that solution in Visual Studio, then press F5 to build the solution and start it. Then you can insert the chart.

To insert a PowerPoint chart by using the add-in

Click the BCS tab.
Click the Add External Data to Chart button on the ribbon.
A ChartInfo task pane appears on the right. Select BCSSales as the External Content Type.
Select Name as the series name.
Select SalesYTD and SalesLastYear as the categories.
Click the Insert Chart button in the task pane. Wait a minute or two for the slide and chart to be inserted.
Save the presentation and close PowerPoint.

Figure 12. Inserted chart in PowerPoint

Notice that this example always creates a line chart. You could change the code, but it usually isn't necessary. For example, you could insert another chart that used only the SalesYTD category and then convert it to a bar chart.

Figure 13. Converted to a bar chart

Converted to a bar chart

The last part of the test is to update the data and then update the chart to match. The following SQL UPDATE statement triggers changes to the SalesYTD value for the Canada territory. You can execute this by using the Microsoft SQL Server Management Studio.

UPDATE d SET UnitPriceDiscount = 0.12
FROM Sales.SalesOrderDetail d JOIN Sales.SalesOrderHeader h
  ON d.SalesOrderID = h.SalesOrderID
WHERE h.TerritoryID = 6 AND d.UnitPriceDiscount = 0

To update a PowerPoint chart by using the add-in

Start PowerPoint and load the presentation that you saved earlier.
Click the slide that contains the chart, and then click the chart to select it.
Click the BCS tab.
Click the Add External Data to Chart button.
A ChartInfo task pane appears on the right. Select BCSSales as the External Content Type.
Select Name as the series name.
Check SalesYTD and SalesLastYear as the categories.
Click the Update Existing Chart button in the task pane. Wait a minute or two for the chart to update. (Only the line for Canada will differ.)

Figure 14. Updated chart in PowerPoint

Updated chart in PowerPoint

After you deploy the data solution and this add-in to the sales manager's computer, the manager can update the presentation chart with just a few clicks.

Conclusion

As mentioned earlier, you can use the same add-in for other ECTs. You only need to create and deploy a Data Solution for each ECT that you want to use, as described earlier.

You could easily enhance the code to add more options to the task pane. For example, you could add the type of chart (bar, line, pie, and so on.). You can create multiple slides by using the same or different ECTs, and you can update them by selecting the chart and choosing the correct options for the update.

The sample in this technical article shows how even a simple VSTO add-in that uses BCS generically can offer powerful and reusable functionality.

About the Author

Bob McClellan has been developing applications, mostly for database systems, for almost thirty years. He recently worked on the Power Tools for Open XML, which are a series of Windows PowerShell cmdlets for manipulating Open XML documents. Bob has extensive programming experience in a variety of computer languages, database systems, and development environments.

Additional Resources

For more information, see the following resources: