Connecting Excel 2013 Power View to SharePoint 2010

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…. 😛

What’s New in Visio Services 2013

Unless you’ve been living under a rock for the past 2 months, you’ve probably heard of something Microsoft released back in July called the SharePoint 2013 preview. Having been busy for the past couple of weeks, I haven’t had the chance to play with its bits as much as I would have loved too. However, I did have a chance to review the new features of the Visio Services 2013 components. If you were to ask me what is my favorite SharePoint feature of all time, it’d have to be this. In all fairness, there hasn’t been any mind boggling changes in the 2013 version compared to the 2010 one. We now have a new file format for Visio diagrams, which allows user to view them in SharePoint directly which is nice. If you remember correctly, in 2010, we had to first save the visio diagram into a VDW file in order for it to be viewed on the web. This new file format eleminates the need to convert file before publishing them on our SharePoint sites.

 

Another nice feature they’ve added is the ability to add comments to a diagram and associated them with specific shapes (see picture below).

BlogVisio.png
Shapes having comments associated with them will have the little dialog buble showing on their top right corner. Clicking on a shape that has associated comments will automatically highlight the comments in grey on the right-hand panel. Please note that this is only available when viewing the diagram in full screen mode. Comments won’t be enabled when viewing a diagram using the Visio Web Access web part.
Now, the coolest new addition to the 2013 version, in my mind, is the support for BCS. You can now connect your shapes to data contained in external lists. In previous versions, you could only connect to either a database or to a SharePoint list. Connecting to BCS allows you to do an hybrid of the two, and reflect data on your diagram that was either changed in the SharePoint interface or in the backend database by a 3rd party app.
I don’t believe we will be given any new toys to play with on the Visio services front when SharEPoint 2013 hits RTM, but I do expect a bunch of cool new opportunities to start appearing with the new Office app model….