How do I add pictures to a Power View report using Power Pivot as a data source?

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.

clip_image002

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.

clip_image004

 

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.

clip_image005

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.

clip_image006

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.

clip_image008 

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.

 

clip_image010

 

 

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.

clip_image011

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.

 

 

How to Create a BISM file (SSAS Tabular mode) in SharePoint so you can create Power View reports.

So you’ve got SQL Server 2012 SSRS integrated with SharePoint, and an Analysis Services Tabular model deployed.  So how do you go about creating reports with the new Power View tool?

This walkthrough will take you through the steps, so let’s get Started.

First of all we’ll need to create a .BISM (or Business Intelligence Semantic Model) file in one of our SharePoint libraries that points to our SSAS Tabular model.  In this example I’m going to use a PowerPivot library that I’ve already got hanging around, but before we can create the .BISM file we need to add the content type to our library.  We’ll do so by opening our library and clicking on the library tab at the top of the screen.

clip_image001

From there we’ll want to look to the far right in the “Settings” group and click on “Library Settings”.

clip_image002

In our Library settings screen click on “Advanced settings” under the “General Settings” group.

clip_image003

Our objective here is to switch “Allow management of content types?”  from “No” to “Yes”.  Make it so, as shown below and then click OK.   Once you click OK you should be directed back to the main Library settings screen.

clip_image004

Now that we’re able to manage our Content Types, we’ll need to add the BISM content type to our library.  From our current screen we’ll  need to click on “Add from existing site content types”

clip_image005

We want to add the “BI Semantic Model Connection”.  Do so by locating it as shown below, highlighting it, clicking add, and then click OK at the bottom.  That should be it, our new content type should be ready to rock and roll.

clip_image006

Now we need to actually create our BISM file.  Navigate back to your library, then click on the “Documents” tab.  Under the “New Documents” dropdown, you should now see our new “BI Semantic Model Connection” content type.  Let’s go ahead and create a new document using this content type.

clip_image007

This screen should be pretty straightforward, simply name your connection and then provide the server name and database name of where your tabular model resides, then click OK.

clip_image008

And that’s it!  You should now see something similar to the following in your library.  As a final test, we can throw together a quick Power View report using the icon shown below at the top far right.

clip_image010

With very little effort we can throw together a quick report in Power View just to prove that everything is working correctly.

clip_image012

I just recently got my SharePoint 2010/SQL Server 2012 personal development environment set up,  so I haven’t spent much time with Power View yet, however I  can say I really like what I’m seeing so far.  It’s really easy to use, visually appealing, and highly interactive.  I think that probably wraps it up for this post.  Hope this helps.

 

 

 

My SQL Server Management Studio (SSMS) shortcut is missing!?

A few months back I logged into a machine that I had been assured SSMS had been installed on, but I couldn’t find the shortcut anywhere.  I figured that more than likely the shortcut didn’t get pushed out to all the profiles, but if I could dig up where the executable was, I could just create my own shortcut.  Sure enough with a little digging I found the .exe here:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

This was for SQL Server 2008R2, but I imagine other versions are not far off the same path. 

From there it’s just a matter of creating a shortcut, and since this post needs some pictures, let’s go ahead and show how that’s done.

Just right click on your desktop and go to NewàShortcut

clip_image001[4]

Then slap in the path to ssms.exe

clip_image002[4]

And finally name that guy something you’ll remember

clip_image003[4]

And that’s all there is to it, we have our SSMS Shortcut

 

clip_image004[4]

Hope this helps.