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!

Advertisements

Excel: Using Inquire

Quietly, Microsoft released a new feature with Excel 2013 Professional called Inquire. Inquire allows you to do all kinds of things at an excel level, making it much easier to analyze workbooks for formulas, compare dependencies across the environment from both the workbook and worksheet level. In this post, I’d like to walk through the 8 icons in the Inquire tab in the ribbon, and what each of them do. First, to enable the inquire tab, go to File->Options, and select Add-Ins, then COM Add-Ins:

image

Check the box next to Inquire, and select OK. A tab in the ribbon will appear for it, with the following icons:

image

Workbook Analysis – the workbook analysis icon investigates everything about the current workbook: connections, hidden sheets, formulas, even links to other workbooks. To demonstrate, I’ve created a very simple workbook that connects to another workbook on my machine, and has some fruit and numbers on it.

image

Save the workbook, and click the Workbook Analysis icon A report window will appear with a detailed analysis of everything in the sheet:

image

You can see we can break down formulas into their various categories, unique, numeric, datetime, nested IF’s, etc. This is very useful for trying to break down some complex enterprise workbooks, especially ones used for financial analysis.

Workbook Relationship – The workbook relationship tool analyzes the workbook for any dependencies to other workbooks across the environment. It is smart enough to trace through the entire relationship until it reaches the last child of the chain. For example, let’s say I created a link to another workbook in my environment, say Exotic fruits, with a reference that returns the value in that workbook. This is really handy for big messy corporate environments that can have links pointing to other documents all over the enterprise. Dashboards that are self-created by end users, perhaps?

image

Worksheet Relationship – The worksheet relationship analyzer takes the workbook relationship to a finer level of granularity, by specifying the actual worksheet that contains the relationship, starting at the current workbook level. This can be used to gain an even deeper insight into where the relationships exist. Also, if you notice at the bottom of the screenshot I also have the option to print this, so I can provide it to others in the organization if I need to publicize the relationships.

 

image

Cell Relationship – Cell relationship takes the last 2 to the finest level of detail possible. Due to this, I can specify how deep I want to go on the configuration screen to prevent the tool from going crazy in an enterprise environment. But if I do want to analyze down to the cell level, I can do that. The one caveat to this tool is that I need to select the cell I want to start with, that has at least the initial hyperlink. Running the tool against my same two workbooks yields this screenshot:

image

Compare Files – the compare files tool lets you compare two workbooks against each other and evaluate every difference between them. The immediate thing that jumps to mind for me is all of the copies of data dictionaries that get generated during BI implementations. This could be very useful for comparing them against each other to find the most complete copy. Simply click the icon, then select the two that you want to compare:

image

This then gives a complete view of every difference down to the cell level. Additionally, at the bottom it breaks down the history for the sheet, and you can select options if you want to compare down to a formula, structural, or naming level:

image

Clean Excess Cell Formatting – Over time, workbooks get bigger and bigger as cells are formatted in the workbook. The workbook becomes bigger and bigger, until eventually it takes 10 minutes or more to open up the workbook. Clean excess cell formatting lets you remove all of this additional formatting in the workbook, reducing the size to a manageable chunk that can still be opened and worked with. When you click on the icon, the tool will simply ask you if you want to apply the cleaner to all sheets or active sheets and then do its scrub:

image 

Workbook Passwords –  The workbook passwords screen lets you manage passwords for workbooks you want to analyze with the inquire tool. This way you can import or add all of the passwords for the workbooks and the tool will bring those workbooks in for you.

image

Overall, Inquire is a very nice feature in Excel 2013 Professional and I can see myself starting to use it during assessments and client engagements to get a better feel for environments.

Resources: For more information on using Inquire in Excel, see the Microsoft support article here or my colleague Devin Knight’s post on it here.

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!