Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Power View meet Microsoft Excel 2013 Part 2

Posted by denglishbi on July 25, 2012

Last week I did my initial post of going over using Power View inside of Excel 2013 – Power View meet Microsoft Excel 2013.  In that post I went over how I converted the PowerPivot model of the Pan-Geo Hospitality & Travel that was used in our Visualizing Data with Microsoft Power View book (well at least a Tabular SSAS version was used, I recreated the entire model in PowerPivot).  I upgraded that model to use the Excel 2013 version of PowerPivot and then showcased some of the new features such as:

  • Background Images
  • Pictures
  • Themes
  • Relationships
  • Pie Charts
  • Map Visualizations (Bing Maps)
  • Key Performance Indicators

So what other key features are that that might be of interest?  Hmmm, good question.  And yes, there are more features.  Have you been wanting support for hierarchies?  What about drill up and down capabilities?  Well guess what, you get them both:)

You might have noticed in the previous post that you saw some hierarchies listed in the field list:

image

Let’s take a quick look at using the Country Hierarchy and see how this looks in a table, matrix, and a chart.

Table

image

Matrix

image

Chart

image

So in the table and matrix no change other than the fact that we can see our hierarchies in the field list and select them.  This allows us to quickly add multiple fields to the canvas.  What about the chart though?  I picked the country hierarchy, but I am only seeing the first level of the hierarchy which are the continents.  Where are the countries?

Well if you double click on the columns in the chart it will actually drill down into that continent and display the data for the countries!

image

You will also see that the sort by and title text changed to display country and there is a new icon available when you hover over the chart, an arrow that provides drill up functionality.  So the drill up will bring you back up to continent. 

Now the neat thing about the drill up and down functionality is that you can expand on this.  The fields do not necessarily need to be part of a hierarchy, you can actually build out the drill up and down capabilities by adding fields into the field well.  So in this example I will add more fields into the chart below the country:

image

So let’s double click a few times to see what we get:

image

image

Now the one thing is that we really are not sure what we are looking at, meaning that what continent, country, and chain type did we select previously?  I can see based on the sort the trail that I have navigated, but what were the values associated with them.  That would be a nice feature.

The drill down also works for the map reports as well.

image

So if we double click on the China pie chart we will go down to the city level based on the navigation defined in the field well:

image

And you can use the zooming capabilities of the map as well.

image

Pretty cool and you can do more with the map with the layout settings in the Ribbon:

image

In addition to hierarchy, drill up & down, and cool map capabilities there is another minor addition that was added and that was support for hyperlinks in a text box.

image

In this example I have a text box that includes the words ‘Microsoft BI’ and below that a hyperlink to the web site.  If I hold the CTRL key and click on the hyperlink it goes to the web site:

image

They hyperlink simply gets added after you type in the URL and press a space, hit enter, or click out of the text box.

Well that covers the majority of the new enhancements to Power View, for the complete list checkout the links at the bottom of my previous posting – Power View meet Microsoft Excel 2013.

I will continue to work with the new features and post any additional findings I might come across like using images from the data models and the issues you might encounter along with using these files in SharePoint.

About these ads

13 Responses to “Power View meet Microsoft Excel 2013 Part 2”

  1. Deja Voran said

    How do I add multiple charts in single sheet in excel powerview 2013?

    • denglishbi said

      Just click on a blank spot on the canvas and start adding additional items from the Field List. If you want to add addition views/reports you will need to use the insert Power View option and it will add an additional sheet for you.

      • Noel said

        to confirm, it sounds like the answer to this question is No. Too bad, to me a serious limitation in the ability to build a dashboard using Power View for Excel.

      • denglishbi said

        You can add multiple charts, but if you want to add multiple views like you can in the SharePoint Power View version then you need to add an additional worksheet in Excel. Having multiple charts, tables, slicers, etc. in a single worksheet is definitely an option.

      • Noel said

        Thanks for clarifying, pretty cool feature … bit of a Tableau killer … of course for good reason as some of the features look a lot like Tableau.

  2. Rachael Anderson said

    Can you start with your own template that includes a logo and special footer when creating a power view report in excel 2013? How about in the share point version?

    • denglishbi said

      Not quite the same way you might create and use templates with SSRS report files (RDLs) like in BIDS or SSDT. You could create base files that already contain an initial report that has a starting point for you to work with. You could do this in Excel 2013 and in an RDLX file in SharePoint. Users could start with that file and then simply do a ‘Save As’ option when they are done.

  3. Hi,

    Can we add hyperlinks to a data field? I’ve a link which is working in excel cell, but it’s not working when i add to Power View sheet. Thanks.

    Sam.

    • denglishbi said

      Yes you can. If the field is displaying text and you have a hyperlink added to it then that will not work from what I have seen when you import this into your model, only the text portion will be pulled in. If you have the hyperlink in a separate field then the reporting property data category setting for that column should be set to Web URL (in Advanced tab). When you bring this into Power View it will then display as a hyperlink and the users can click on it.

  4. Dave said

    Dan,

    Can the PowerView reports created in Excel 2013 be saved to a network folder and then opened/viewed by other users that have Excel 2013? Are there other options for “deploying” Excel 2013 PowerView reports to allow others to view/use them? Not including SharePoint :)

    Thanks,
    Dave Maurer

    • denglishbi said

      The file share would work as long as the other users have Excel 2013 and Silverlight installed. Ideally to share you would want to look at using SharePoint or Office 365 so that the solutions can be properly monitored.

  5. [...] from here. Dan English (@denglishbi) wrote an article titled PowerView meet Excel 2013 part1 and part2. That helps you get started. Dan also wrote a book on PowerView you may to check out. I haven’t [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: