Walkthrough: Consuming LightSwitch Services in Excel Using PowerPivot
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.
To complete this walkthrough, you must install the following products:
Visual Studio Professional 2012
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
In Solution Explorer, open the shortcut menu for Properties and choose Open.
The Application Designer opens.
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.
Choose the F5 key to run the application.
In the web browser, copy the first part of the URL from the Address Bar. It should look like http://localhost:#####/, where ##### is a numeric value.
To access OData data from PowerPivot
In Excel 2010, on the menu bar, choose PowerPivot, PowerPivot Window.
The PowerPivot for Excel window opens.
On the menu bar, choose Get External Data, From Data Feeds.
The Table Import Wizard opens.
In the Friendly connection name text box, enter Vision Clinic Products.
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 http://localhost:#####/PrescriptionContoso.svc, where ##### is a numeric value.
You can choose the Test Connection button to make sure that you entered the correct URL.
Choose the Next button.
On the Select Tables and Views page, select the Products check box.
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.