PerformancePoint: Adding help to web parts

Did you know that you can add hyperlinks to your web parts that PerformancePoint creates for you? This makes it so you can redirect to either an online location or to more descriptive information about what that dashboard represents. For example, imagine that you have an executive portal of your BI site that contains summary level information for all of your divisions. On your PerformancePoint dashboard, users aren’t quite sure what they can do with the dashboard so you create a nice walk through tutorial of all the different things you can do with the web part.  You want to modify your web part so that when users click “Help” they are directed to this document to provide step by step instructions on how to use this BI tool.

To accomplish this, navigate to your SharePoint site and open up your PerformancePoint dashboard. For this post, I’m going to use a local VM I have with SP2013 on it, and a dashboard showing sales for AdventureWorks:

image

 

Next, pick which web part you want to add your hyperlink to. In the top right corner, click the down arrow and select “Edit Web Part”:

image

 

Notice that any sort of “Help” option is missing. Let’s change that. Under Edit Web Part, expand the Advanced section, and find the property marked Help URL. For this post, I’ve just created an empty Word document with a title, and saved it a Documents library in another location of the site. In the document library, I’ve clicked on the options for the document and copied the URL. Take the copied URL and paste it into this property. Note that I’ve just chosen a step by step walkthrough document that I’ve “created”, but you could paste any URL you want. Any web site, blog post, or other location inside your SharePoint intranet is fair game. Leave the Help Mode property on Navigate, and click OK. When you click OK, it will take you back to the home page of the site. Go back into your dashboard/web part, and select the drop down carat at the top right again:

image

Now we have a Help button! Let’s click it. (it is a button on a web page after all). Our help file opens! AWESOME-SAUCE!

Save an Excel table as a SharePoint List

Quick post today, but really handy. I recently had a list of values that I needed to save as a list on a SharePoint BI site. The good news is that this is much easier than I originally thought to accomplish.

To demonstrate, I’ll start by opening up Excel and creating a table. I’m just going to use a table of some clubs at a university. My excel table looks like so:

 

image

Make sure your list is formatted as a table, by going to Home->Format As Table if it’s not already. Next, on the Design tab in the ribbon when you have the table selected got to Export->Export Table to SharePoint list.

image

 

Specify the URL of your SharePoint site, and give your list a name.

image

Click Finish, and excel will begin generating the list to SharePoint. It may take a few minutes, depending on the size of your list. Once it’s finished, excel will give you a message saying that it’s complete and give you a link to the list location it created:

image

 

Click on the link, and your list is generated! If you want it to show on the Quick Launch bar, in SharePoint go to List->List Settings. Under List General Information, click on List Name, Description, and Navigation. The last option on the page is Display this list on the Quick Launch?. Change it to yes and click Save.

image

 

Awesome-sauce!

SharePoint: Performing a site collection backup

SharePoint sites, like databases, need to be backed up in case of issues that may arise during production. This way, if your SharePoint server unexpectedly crashes, you won’t lose your site with it. The good news is that it is really simple to backup a site collection in SharePoint 2013.

 

To start, open Central Administration. On the right hand column, about halfway down, is the Backup and Restore section of Central Administration.

 

image

Click on “Perform a site collection backup”, and the Site Collection Backup screen will appear. Select the site collection you wish to backup, and then specify the full file path and location that you want to backup your site to. At the end of the filepath, you want to put the name of the backup file you want to call it, and append it with the backup extension, .bak. The completed screen would appear like the below:

 

image

If the file already exists, check the “Overwrite existing file” option. Then click the Start Backup option at the bottom of the page on the right. Once the backup job status screen appears, you can refresh it periodically and ensure that your backup is successful.

 

image

 

There we have it, a successful site collection backup in SharePoint!

Power View: Creating Maps

Power View, in addition to GeoFlow Power Maps, has the ability to take geospatial data and graph it to view data from a different perspective. You can’t create tours and some of the features that you can in Power Maps, but you can still demonstrate performance across a geographical location relatively simply. In this blog post, I’m going to take some geographical data around the Adventure Works database and graph it in Power View.

 

Initially, I’m going to create a connection to my Adventure Works tabular database and select Power View report as my import connection type. Under Geography, I’m going to select the StateProvinceName field, along with the Sales Amount field. My initial table looks like:

 

image

 

Up in the ribbon, on the Design tab, the fourth icon from the left for my report is the Map icon. When I click this, Power View is going to open up a connection with Bing maps, and attempt to graph my data geographically. Clicking on it, I get a very simple map showing my sales amount by State or Province around the world.

 

image

 

There are now a couple of different options that I can do. If I look at my field list, I can tile by something, create vertical or horizontal multiples, or drop in longitude and latitude, if I have it for example. I can also create a pie chart in each State/Province showing the breakdown of an additional category. If, for example, I take the Product Subcategory column and drop it in the Color section of the Field list, I get a map showing a Pie chart breakdown of each state/province’s sales:

image

 

Zooming in, I can see each countries breakdown by state or province. The only issue I see with this report is that everything is so spread out, it’s hard to see each country or region’s detail and keep the whole picture in focus. However, if I drag Region into the Vertical Multiples section, each Region becomes it’s own sub-map inside of the main Power View map:

 

image

 

There we go, much better. Now I have a nice visual Power View map that breaks it down but still keeps it easy to see the whole picture as well. One thing to note though, if I zoom in on France, there is some strange stuff going on with the province’s in the country. The same issue could be encountered with cities. The most accurate representation that we can use in mapping is Long/Lat, which if we had the fields available, should be what we use. If you don’t have Lat/Long (which, unfortunately, is more common than actually having it), we can pass concatenated field to Power View by setting them up in our model appropriately. The reason for this is that the same city, state, or province could happen multiple times throughout the world. How does bing maps know that we mean London, UK as opposed to London, Kentucky or London, Ohio? Back in the tabular model, I’ve created a concatenated field for “City State Country” and one for “State Country” by using the following DAX expressions:

City:

=[City]&","&[State Province Name]&","&[Country Region Name]

State:

=[State Province Name]&","&[Country Region Name]

 

Build and deploy, and refreshing my Power View model and replacing the old State field with my new State Country field fixes this inaccuracy issue and gives me a much clearer picture of, in this case, my sales in France, thereby leading to a much cleaner report:

image

 

In summary, while not as geographically amazing as Power Maps, creating maps in Power View is an excellent way to get some quick geographical reporting and visualization for a presentation, especially if the data model has already been built and is available in a tabular database or Power Pivot. However, when giving data to Power View to give to bing, ensure that you give Power View the most accurate geographical value of the data as possible.

Power View: Using the Play Axis

One of the most talked about features of Power View that I always hear client’s rave about is the play axis. For those that aren’t familiar with it, the play axis in a Power View report allows you to make a scatter chart about some key metrics (number of sales, dollar amount, etc.) and show it’s progression over time across 3 or 4 different axes. For this blog post, let’s walk through the different features of the chart, and how we can configure them. I will be using Excel 2013 Professional for this demo, but if you’re developing a Power View report in a SharePoint environment it would be identical.

 

To start, open up an Excel workbook with your Office 2013 Data Model. I’m going to be using the Adventure Works 2012 tabular database for this demo. My initial Power View worksheet looks like this:

image

 

Next I’m going to create a very quick table showing the amount of my internet sales, the number of units sold, along with the date that they sold and the sales territory region. My resulting table looks like this:

image

 

You can see from the picture, not very much exciting about it in its current format. Let’s change that. Up at the top of the page, under “Other charts”, select the scatter option to turn it into a scatter chart.

 

image

 

Once you have a scatter chart up, your initial picture should look something like this:

 

image

 

Down on the bottom right hand corner, you will see a list of all of the different fields that are available to you:

 

image

 

This is what we can use to configure the chart. Below is a breakdown of each type:

 

X Value: The horizontal value across the bottom of the report to generate the size of the x axis.

Y value: The vertical value across the top of the report to generate the size of the y axis.

Size: The size of each detail circle.

Details: Each category or attribute that you would want to see data reported on.

Color: You can use this field to identify what color you want to use. For example, you can use Product Sub Category as the details attribute and the Product Category as the color.

Play Axis: This is the date field that is used to configure and move the report accordingly.

 

For this report, my configuration area looks like the below:

 

image

 

Once my configuration looks like the above, my resulting Power View report will look like the below. Click the play axis, and WHAMMY! One awesome report that will get the whole company talking about some data!

 image

 

 

 

 

 

 

 

 

 

 

 

Power View: Using Slicers and Tiles

Previously I introduced what Power View is, how to enable it, and how easy it is to create awesome visualizations in just minutes. However, Power View offers so much more in terms of functionality for what you can do with your data, from just adding slicers to creating rich, colorful tiles that can be used to visually browse your data. In this blog post, I intend to show how to create slicer and tiles in your own Power View reports. For this demo, I’m going to use the AdventureWorks tabular database demo available on codeplex, connected to the Inventory perspective.

 

Slicers exist in Power View reports, exactly as they do in Excel. As an example, I’m going to use this chart as my base that I want to be able to report on, showing total units in inventory and the total inventory value by product class:

 

image

 

Now let’s say that I want to add Product Category as my slicer. I’m going to click on Product Category in the fields list on the right, and drag it into a new field list, as shown below:

 

image

Now we have the base we can use for our slicer. Unfortunately, it’s not automatically a slicer, we have to tell Power View that that’s what we want. If we just click on any Product Category right now, none of the other tables will update:

 

image

 

Up at the top in the Excel ribbon there is a button called “Slicer”. This will turn our list of Product Categories into the slicers that we want to see. Click anywhere in the Product Category table and then go up into the ribbon and find Slicer, then click it:

image

 

Once you click it, you’ll notice that each of the items in the Product Category list now have a little blue square next to them. This indicates that they’ve been turned into a slicer:

 

image

 

Click one and you’ll notice that now our table updates with each Product Category we select:

 

image

 

Nifty! However, there is something even more exciting in Power View, in a feature called tiles. Tiles allow you to create slicers, but to turn your slicer into something more visually compelling. To start, click on the Product Category name on the right hand side and delete it from your list, by selecting “Remove field”. Then click on the original report that we created to the left showing value by Product Class:

 

image 

 

On the right hand side under the field list section, you’ll notice an empty area above the fields we’re using called “Tile By”. Find Product Category, and drag it into that section:

 

image

 

Now we can browse this particular section of the report by each category, with a nice rolo-dex style tile list at the top of the page. Additionally, if we wanted to change how our tiles look, we can go up to the “Tile Type” section in the ribbon and change it appropriately:

 

image

 

Changing this report to “Tile Flow” from “Tile Strip” changes our tile list to look like this:

 

image

What’s nice about the tile flow option is that you get the nice same functionality that the Power Pivot Carousel gallery in SharePoint gives you, so it’s very visually appealing to browse. Sweet!

 

In conclusion, if you want the ability to slice the entire report by a certain attribute, slicers are probably you’re better way to go, as they affect everything on the page. But if you just want the ability to change the parameter in a given “sub-report”, use tiles.

Creating your first Power View report

In the Microsoft Office 2013 Professional release, we have a new tool on the market that can really help to allow us to get good visualization into our data. It’s called Power View, and was created by the Microsoft team responsible for SQL Server Reporting Services. I admit, I was skeptical when I first heard that, as I’m not the world’s biggest SSRS fan, but I was pleasantly surprised with how much fun it is to use. I can see using this on top of  Power Pivot workbook that you use to personally analyze your data, and then use Power View to get a really cool report that you can personally use wherever and whenever you want.

First, we need to enable PowerView in Excel. Assuming you’re starting from a blank workbook, go up to File-> Options, and then select the Add-Ins tab on the left:

 

image

 

Once your on the Add-Ins tab, at the bottom of the tab under Manage, select COM Add-Ins, and click Go:

 

image

 

When the COM Add-Ins management screen appears, find “Power View” and check the box, then click OK:

image

Once you click OK, you’ll be back to a blank workbook. Your Power View icon is on the Insert tab, about half-way across on the page:

 

image

Now that we have Power View enabled, let’s get some data! For this example, I have the Adventure Works tabular data solution deployed on my local machine. This is available on codeplex for download here.

 

Go up to Data, and under the Get External Data section select the “From Other Sources” button, and then click on Analysis Services. Tabular databases are accessed the same way that multidimensional databases are, if you are used to that. Type in the name of your server, and click Next:

 

image

 

Select the name of the cube or perspective that you want to browse, and click Next. I’m going to leave it on “Internet Operation”

image 

Review and Click Finish:

 

image

 

Now once you click finish, on the next page we get a new option, added specifically for Power View:

 

image

 

Select “Power View Report”, and then click Ok. Excel will think for a few minutes and then give us a blank worksheet canvas to work with. For those curious, SharePoint Power View looks identical to this. What’s nice about it is that you can get a SharePoint/SQL Server Enterprise feature on your local Excel 2013 Professional install! This is really cool if you want to show what you can do if you’re building a POC for a business intelligence project. The blank canvas will look like this:

image

 

On the right hand side you’ll see all of your fields that are available from your data source. To start, let’s create a report that shows the Gross Profit for each product category by year, and give our Power View report a title. To give it a title, just click at the top and start typing what you want your title to be, and to create a new “report”, just click on the field you want to add and drag it into the Fields section underneath or anywhere on the white canvas to the left:

image

 

Now let’s add Product Category and Year, and we wind up with something that looks like the below:

 

image

 

Easy to create with the click and drag interface, but hardly anything I’d want to go running through the halls about yet! Let’s give our report some flair (I think the minimum is 6 pieces, Office Space aficionados?) Click anywhere in the table, and then at the top under Design, let’s change the chart type to something more visually appealing:

 

image 

 

Select Other Charts, and then click Line. Power View automagically changes our data into a line chart that we can use. Now we have a small chart that we can use, but we can’t see anything as it’s pretty small:

 

image

 

Fortunately, Power View was built with dashboards in mind, and at the top right-hand corner of the containment box there is an option called Pop-Out, which blows the small chart up to the size of the entire screen.

image

 

Another thing you’ll notice is that down at the bottom-right hand corner, our options have updated to give us the choice on what we want to see under each axis:

 

image

 

Since this chart isn’t showing us very much, let’s move some of these around. To move something, just click and drag from the location where it is, to the location where you want it. Power View will automatically update your graph. Our new graph looks a bit better:

image

 

With this, we can visually see what our gross profit margin is for each Product Category by year. What’s more is that it only took us about 5 minutes to create this report! Cool!