Remember that thing Microsoft referred to as Project Crescent a couple of years ago? Well, it got an official name when SQL Server 2012 was released back in March of this year. It is now called Power View. People that want to use it right away, can do it by installing the SQL Server 2010 Reporting Services Add/in for SharePoint, as well as the PowerPivot for SharePoint component. No need to upgrade the entire backend database to 2012. You would then have to go and deploy the PowerPivot solutions to the SharePoint farm, and install the Reporting Services Service Application using PowerShell. In my mind, that’s a lot of trouble for nothing, especially when you know what’s coming with Excel 2013.You’ve guessed it right, Excel 2013 now has the Power View module built-in. Ok, I hear you guys complain already about the fact that Excel 2013 is just in the preview stage and that it won’t hit RTM until another good 6 to 8 months, but who cares, the product is pretty solid even in preview stage, and I just like to live on the bleeding edge of technology, that’s just how I am.
Now, for those of you who are totally lost, and that don’t know what I’m talking about when I mention Power View, think of it as a way to dynamically generate interactive dashboard to allow end-users to view data in a graphical way. The following post will go into the details of creating a very simple Power View dashboard that takes its data from a Custom list stored in SharePoint 2010. Let’s imagine the following scenario, we are responsible for a school board and keep a list of data about the various schools we manage. Our list includes information such as:
-
School’s Name
-
School’s Annual Allocated Budget
-
# of Students
-
School’s Location
-
# of Students who got Passing Marks
Our list look s like the following:
The first thing we want to do in order to generate a Power View report directly from Excel, is export the data from our custom SharePoint list directly into Excel 2013. In order to do this, select the List tab in the ribbon, and click on the Export to Excel button.
If prompted to enable the macros in the file, click Enable. Create a new Excel column of type percentage and let its result be the percentage of student who got the passing marks (#of students / # of students who passed). Now, all you have to do is select any cell inside the data table in Excel, and under the Insert tab, click on the Power View button. This will automatically generate a new Power View Sheet inside the current Excel workbook.
The newly created Power View Sheet is automatically displayed in the Excel client. Move your cursor on the border of the data table. Click on its border to select the table, and then press delete on the keyboard to remove it from the View. You will now have a blank page.
In the right Panel, expand the Table_owssrv tree and check the box beside the field City. This will automatically add a new table in the view. Click on the newly generated table, and in the Ribbon, click on the Map button. This will automatically generate a map of the various schools we manage, all having equal sized circles.
We now want to add more meaning to our diagram, in the right-hand panel, drag the # of Students field into the Size parameter. This will increase the cities bubble size proportionally. Your map diagram should now look like the following:
Let us now explore another type of diagram, in the ribbon, click on Bar Chart > Clustered Bar. In the Tile By parameter, put the City field, in the Values parameter insert the Annual Budget field, and in the Legend parameter, insert Name field. Your graph should now allow you to view, by city, the amounts of money invested per school.
As you can see from my little example, you can build something that is extremely “eye-candy” with no or very little effort. Now if only this could be published as HTML 5…. 😛
