One of the great new features of SQL Server 2012 is Power View. In this walkthrough I’ll be creating a very simple Power View report using Power Pivot as the datasource. The goal of this report will be to demonstrate how you can pull in images into your report to give the user a nice visual…so let’s get started.
1. Get your pictures ready: I simply took some quick pictures of items laying around on my desk, then I uploaded them to a SharePoint library. These pictures are a little less than visually stunning, but you get the idea. Just make sure to remember the URL, as we’ll be pulling our pictures in from this library.
2. Now we need to build ourselves a Power Pivot workbook. I’m going to keep this real simple with only two tables, and we’re really only going to talk about one of them….the “Items” table shown below. There are a few things to note about the table below.
o Each entry in the Item column is named after one of our pictures…that makes it easy to construct what the URL to our picture is
o The “ImageURL” column is a calculated column which is simply the URL to the document library where my pictures are located. (you can see the formula in the fx bar in the image) . The key point here, is that you need to have the full URL to your picture somewhere in your Power Pivot book.
3. Now that we’ve got some data with URLs to some images, we need to tell Power Pivot that our “ImageURL” columns is actually an image. To do this we’ll want to click on our main menu in the Power Pivot window and select “Switch to Advanced Mode” as shown in the picture below.
This should make a new “Advanced” tab available. From here we want to highlight our “ImageURL” column, and then check the “ImageURL” checkbox on the advanced tab as shown below highlighted in yellow.
4. As a finishing touch let’s let Power Pivot know that our image column is actually a picture of the entry in our “Item” Column. This will allow us to show both the item text and the picture in the same tile when we go to make our report in Power View. To do this we need to click on “Table Behavior” under the “Advanced” tab (shown in the image above highlighted in yellow). Doing so will open the “Table Behavior” window shown below.
On this screen we need want to do the following:
· For “Row identifier” select the column we want to work with, in our case this is the “Item” column.
· For “Keep Unique Rows” select columns you don’t want to aggregate on (you don’t need to do this for our example, but it’s a good practice to get into.) In our case, the “Item” columns is unique in our data.
· For “Default Label” select what we want the label to be when we create our tiles. In our case this is the actual text in the “item” column.
· For “Default Image” select the column that the URL to our picture resides in. This will be our “ImageURL “ column.
Once that’s finish click OK, and the save and close the workbook.
5. Now that we’ve created our Power Pivot data set we just need to upload it to our Power Pivot Gallery in SharePoint, and then create a new Power View report by clicking on the Power View Link as shown highlighted in yellow below.
If I select “Item”, “description”, and “Sales Total” (sales total was the other table we didn’t talk about, but it was just a bunch of numbers I made up and related to our first table) and then click the “Tiles” button from within Power View it’ll look something like this.
So there we have it, a very quick/simple report example using images in Power View. In addition to showing images in our tiles, we can also show our images in tables or on cards. I think that about wraps this walkthrough up, hope you found it helpful.