Walkthrough: Consuming LightSwitch Services in Excel Using PowerPivot

  • Article

 

For the latest documentation on Visual Studio 2017, see Visual Studio 2017 Documentation.

By following this walkthrough, you can use Microsoft Excel 2010 to analyze data from a Visual Studio LightSwitch application. First, you'll create a feed for that data through the OpenData Protocol (OData), which is how LightSwitch applications that are based on the web expose their data. You'll then open the data in PowerPivot, which is an add-in for Excel 2010 and one of many applications that can parse OData information.

Prerequisites

To complete this walkthrough, you must install the following products:

In addition, the walkthrough uses the Vision Clinic sample application, which you can download from the MSDN Samples Gallery or create by completing the steps in Walkthrough: Creating the Vision Clinic Application. As an alternative, you can use your own LightSwitch application by substituting your application name for Vision Clinic and your data source name for PrescriptionContoso where they appear.

To expose data through an OData feed

  1. In Solution Explorer, open the shortcut menu for Properties and choose Open.

    The Application Designer opens.

  2. On the Application Type tab, in the Client section, choose the Web option button.

    The data from both the ApplicationData and PrescriptionContoso data sources will be exposed as OData feeds.

  3. Choose the F5 key to run the application.

  4. In the web browser, copy the first part of the URL from the Address Bar. It should look like https://localhost:#####/, where ##### is a numeric value.

To access OData data from PowerPivot

  1. In Excel 2010, on the menu bar, choose PowerPivot, PowerPivot Window.

    The PowerPivot for Excel window opens.

  2. On the menu bar, choose Get External Data, From Data Feeds.

    The Table Import Wizard opens.

  3. In the Friendly connection name text box, enter Vision Clinic Products.

  4. In the Data Feed URL text box, enter the URL that you copied in the previous procedure, and append PrescriptionContoso.svc to it. It should look like https://localhost:#####/PrescriptionContoso.svc, where ##### is a numeric value.

    Tip

    You can choose the Test Connection button to make sure that you entered the correct URL.

  5. Choose the Next button.

  6. On the Select Tables and Views page, select the Products check box.

  7. Choose the Finish button, wait for the data to be imported, and then choose the Close button.

    The data appears in the PowerPivot window, and you can create PivotTable and PivotChart reports from the data.

Next Steps

For more information about PowerPivot, open Help by choosing the F1 key in the PowerPivot window.

See Also

LightSwitch as a Data Source
Exposing LightSwitch Application Data