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 !!