Welcome to Day 3 of our effort to visualize the Olympics using Power View in Excel. In yesterday’s post, we looked at Tiles, pie charts in maps, and dabbled a bit in the filter pane. Today, we are going to introduce the PowerPivot add-in and one of the features of the modeling experience in PowerPivot that will allow you to add images to your reports.
First things first. You need to download the Excel 2013 Consumer Preview from here. Once you do that, you can download the resulting workbook from today’s exercise here.
Now that you have the workbook, the next thing you will want to do is enable the PowerPivot add-in if it is not already enabled. To do that, simply go to File –> Options –> Add-ins –> COM Add-ins and
then make sure the Microsoft Office PowerPivot for Excel 2013 add-in is enabled.
Back in Excel, we can launch the PowerPivot window from the ribbon. Go to PowerPivot –> Manage. Once PowerPivot is launched, you will see a set of six tables. One for Country, Sport, Medal Images, Countries, Sports, and Athletes. Three of these tables are linked tables and two of these tables came from an external database. The three linked tables are just like the tables that we have been working on for the last two sessions. They are simply links to the Excel tables that are in the workbook. The external tables, on the other hand, are based on a query to a SQL Server database set up. So why did I do that? Well, that is the focus of today’s session.
Let’s focus on the Sport Table. Here you will notice that there is a field called “SportImage” that contains Binary Data.
These are the actual images that are the pictograms that represent the sport. In order to take advantage of these images in Power View, go to the ribbon in PowerPivot and ensure that Advanced Mode is enabled.
Once Advanced Mode is enabled, click on the Advanced Tab and you should see in the Reporting Categories chunk of the ribbon that the Data Category is set to “Image”. This is the property that allows Power View to display the contents of this field as an image.
So how do we get this data into this table in the first place? First we need a set of images, which I have stored on my local hard drive and then we need a SQL Server script to loop through a table that contains the name of the sports and then does an import of the image into SQL Server. Below is the syntax I used for this operation.
Now that I have the data in SQL Server, I can choose to import that data into PowerPivot. To do that I just walked through the PowerPivot import data from SQL Server option and chose the option to import all the data from this table.
For those of you who already familiar with the ability to use images in Power View, you may be asking why did I decide to actually import them into the model instead of simply referring to an external image URL that could also work? There are a couple of reasons for why I opted to actually embed the images. First, once the images are in the model in the workbook, I have a very portable solution. I can hand this workbook to someone else and I don’t have to worry about whether or not they will be able to see the images. The second reason is that if I plan to use this workbook in Sharepoint or Office 365 based scenarios (by the way, you can try out the Office 365 scenario with the Consumer Preview), the Office 365 scenario does not support access to external images.
With our images now in the Power Pivot model, we can return to Power View and make a change to the report we built yesterday. When we left the report yesterday, it contained a Tiles container with a map in it. To add the images to the report, you can simply switch the current Tile By field from Sport Name to Sports Image. That will give you the following map.
Tiles are not the only place where you can add these images. In future posts, we’ll cover additional ways that you can use them in your reports. Note that I have also inserted into the workbook images to support displaying country flags and medals. The process to upload those into the model was exactly the same. See you tomorrow!