When your hosting your Lightswitch application in the cloud in Windows Azure you may have noticed that the button for exporting your data to MS Excel has been greyed out for security reasons. A good thing, because your data is secured and safe! For reporting your data Windows Azure offers you a solution using a report-server for displaying reports to users and designing them on site, using Visual Studio. Of course adding a report-server to an Azure subscription will cost some extra money and when your client is on a tight budget alternative ways for reporting are welcome !

So recently, when I had to present a CRM-business solution to a client, I chose a different approach at less costs using PowerPivot for Excel.

PowerPivot is an  free add-in for Microsoft Excel 2010, which offers you great new possibilities for making pivot-tables, based on more than one table of an relational database as SQLServer 2012 or SQLAzure ! More information you'll find here: PowerPivot for Excel 2010. Personally I find Pivot tables one of the most flexibel and user-friendly ways of analyzing and presenting data and using this in combination with a database gives a lot of new possibilties ! So let's'go ahead and show you the "how to .."

First download the PoverPivot from the above mentioned website and choose the right combination with your Excel 2010 version ( 32-bits or 64 bits) ! Install the add-in-software and start up Excel 2010: you should see this screen:

The extra PowerPivot tab, added in your Excel document with on the left side the PowerPivot button for opening the seperate PowerPivot-window.

This example is based on my test-LightSwitch application, hosted in the cloud on Windows Azure, which features a small CRM-information system ( Customer Relationship Management). The application has a few test-data in it and consists of customer, offers and orders information. The data is stored on a SQL Azure database. Here are a few screenshots, which will give you an impression of the datä:

(1) we have three companies, two of them are customers and one is a propect (2) . In the next screen you'll see the orders, placed by Company A., which in total they reflect the number of 30 items (see Order Detail Quantity).

And in the last view below you'll see the orderslist (1) of all companies (A and B) together, summing up to 180 items (2):

Notice that on all three screens the standard Excell-export-button is not present in Windows Azure! That's where our PowerPivot Excel approach will come in handy ! So let's move on, back to Excel ! So click on the PowerPivot-button on the left of your Excel-worksheet to open the PowerPivot screen. Next, click on the button "Uit Database" ("From database")

 Select from the dropdown-list the first option "Uit SQL Server" "("From SQL Server")

In the next screen you're asked to name your connection and provide the login-credentials for your SQLAzure database and click "Volgende" ("Next")

In the next screen select the first option for being presented with a list of the tables of your SQL Azure database:

In this example I've selected the following tables: Customers, Customer Status, Orders and Products:

Click on "Voltooien" ("Finish") and the import of your data will start and when finished the green "OK" mark will show up. Click on "Sluiten" ("Close")

In the PowerPivot-window the earlier selected tables are now shown as seperate sheets (1); when you're connecting to a standard SQL-server database you can now proceed with (2) the making of your PivotTable (click on "Draaitabel"  / "PivotTable),, but with a connection to an SQL Azure database you'll need to establish your table-relationships again. Up until now the reason for this is not clear to me, but any suggestions are welcome!

To do so, click on "Diagramweergave" or "Schema-view"

In the next screen your tables will be presented and you'll need to re-establish the relationships.

In this example I 've selected the primary key (1) of the CustomerStatus table (CustomerStatusID) and dragged it towards the CustomerStatusID in the Table Company.(2)

Do the same for the other relationships between the tables, the end result looks like the screen below. Notice that there's now a connection of four tables in four powerpivot-worksheet, combined by relationships and with the data from an external database! A perfect starting point for your reports and analysis.

The next step is to select the button "Draaitabel"("PivotTable") and pick the option of your choice, in this case simply the "Draaitabel" ("PivotTable"). Be sure to do this within the PowerPivot-window and NOT in the normal Excel-worksheet, where there's also a button called "Draaitabel" !

Now you can proceed in making a PowerPivot PivotTable in the opened window. You''ll see your tables on the right side (1) and you can drag them to the position-placeholders below as in a normal PivotTable.

Depending on your choices (1), the result(2) looks something like this:

You can add a graph by selecting your PivotTable-data and then the buttons Insert (1), Column (2), Cilinder (3)

For your graph you can choose a nice design-pattern (1) and the result (2) will show like this:

This completes the steps for making a PowerPivot Table, based on a LIghtSwitch-application hosted in the cloud on Windows Azure. And at no extra costs ! To keep your information up to date you can refresh your data, by clicking on the Refresh button.

This is just a very simple example of bringing cloud-hosted-data from your LightSwitch application to Excel. But PowerPivot itself has a lot more to offer! You really can create your own business intelligence solutions, right in MS Excel, analyzing milions of rows of data! A excellent book on this matter is  "PowerPivot for Excel 2010, give your data meaning", from the authors Marco RUsso & Alberto Ferrari and published by Microsoft Press. So:....Happy analyzing your data !!

Posted in: Reporting
Actions: E-mail | Permalink | Comments (3) RSS comment feed

Post Rating


paul van bladel
Sunday, September 30, 2012 12:33 PM
Hi Ruud,
Good post !
Definitely, for an admin role, is connecting directly to the database probably an excellent choice. For a non-admin this is a less relevant option, unless you give the database password to all your users...

I'm wondering... did you try also to connect to the ODATA Service? This should be possible from powerpivot. The advantage is that all your row level security rules are respected when the user to the server. For example, you have a CRM app, and a rule says: a user can only edit "customers" where the app user is related to as customer relationship manager. When a user connects via powerpivot to the ODATA service, this rule is respected as well. When he would connect directly to the db, he will see them all.

Take care
Anonymous User
# Anonymous User
Wednesday, October 10, 2012 7:15 PM
LightSwitch Community & Content Rollup–September 2012
Anonymous User
# Anonymous User
Thursday, October 11, 2012 3:02 AM
LightSwitch 社区&内容汇总 – 2012年9月

Post Comment

Name (required)

Email (required)


Enter the code shown above: