Tag Archives: Excel

More iPad support for SharePoint: Dashboards, PowerPivot, & Excel!

It’s nice to see Microsoft playing nice with others. The cumulative update 2011-12 (CU Dec 2011, or Feb 2012) includes improvements around the iPad. Specifically, users can now access dashboards, Excel services, and PowerPivot services via their iPad!

For complete details, check out http://technet.microsoft.com/en-us/library/hh697482.aspx. There’s a great video on that page that walks through how well it works.

Taking my PowerPivot walk through I posted about previously, I accessed it via my iPad and found it worked very well. Check out the screen shots below.

Initial screen after opening the document

Same experience and messages as using on a PC

Quickly and easily selecting a slicer, see the data update

Going deeper into the data, fast and easy!

Tried opening in Excel, fair enough!

Loading SharePoint lists from Excel using PowerShell

One of my biggest headaches with developing and testing in SharePoint is my lack of test data to work with. Sometimes I need to generate a load of test data so I can perform searches or test performance on custom code. Like in this blog post, I needed to load up a bunch of fake data so I could walk through an example.

To resolve this issue I made  a quick PowerShell script which will read a CSV file and import the data I need. The script needs to be customized each time, but it’s pretty easy. You can download the script called Import-DataIntoSharePoint from my CodePlex project.

This script can also be used to automate importing data into SharePoint. Assuming you have an external system which is exporting to CSV, this script can be scheduled via Windows Tasks to run and import the data on a periodic basis.

First, create your CSV file. How? Open Excel, and load your columns and rows of data. It’s easiest to make the first row your column titles, and have the column titles match the fields in the SharePoint list you’re importing into. It’s ideal not to use spaces in the names as well.

Now load up your data! The values have to be the same as SharePoint would expect. For most fields that’s pretty easy. Text and numbers can be entered as is. Dates should include the time, i.e. 1/20/2012 11:00 AM. Choice fields work like text fields. User and lookup fields are unique, those should be formatted like ID;#Title, i.e. for a user 2;#David Lozzi, or for a lookup 34;#Request for PO. This part can be tricky. Check out my post on Using PowerShell to play with SharePoint Items to better understand the data format.

After you’ve loaded up your data, save the Excel file as a CSV file, and put it in the same folder as the downloaded script.

Open the script up in NotePad (I prefer NotePad++). Make the following modifications as needed

Import Data into SharePoint via PowerShell

  1. Set your web’s URL, i.e. http://servername, or http://servername/site/site
  2. Set your lists’s name, i.e. Calendar, Tasks, My List
  3. Set your CSV file name
  4. Add all fields you wish to add. The $new[ is your SharePoint field name, the $i. is your CSV file column name.

Now save it and run it! With any luck you’ll get a nice response like

Importing data into SharePoint with PowerShell

PowerPivot Walkthrough Using SharePoint Lists

Before we can just walk through using PowerPivot and SharePoint, we need to make sure things are in order. Some prerequisites

I am using the Northwind sample data provided by Chris Beckett, you can download it here. If you don’t want to load up the Northwind sample, target a list or two of data you want to work with. Having two which are related (using a lookup) is preferred.

First thing, open Excel and create a new workbook. Click PowerPivot tab then PowerPivot Window.

You’ll get a nice empty window. Now, you would think there would be a simple button that says Import SharePoint List. But there isn’t. Actually, what we’re going to use is the list data feed option.

If PowerPivot is installed on your farm correctly, you can simply go to your SharePoint list, click the List tab in the ribbon, then click the Export to Data Feed button.

This will prompt you to open or download a file. Save the file locally, then open it. When you open it, PowerPivot will ask where you want to import the feed. Select Book1, or another book name if you’re using an existing book.

That will then send you to the Table Import Wizard in PowerPivot.

  • On the first page, name it something meaningful, like the list name you’re working with.
  • Click Next.
  • On the next page you can preview and filter your data, if you don’t want to pull it all in, you can filter out data. Only filter out data that your users will absolutely not want (like from 5 years ago).
  • Click Finish.

That’ll take a little time, but it’ll load up your list into PowerPivot. Continue the same steps for all lists you want to import.

Once you have your lists in, we need to setup a relationship between them. In the PowerPivot window, click the Design tab, then click Manage Relationships.

You should get an empty window with a button Create. Go ahead and click Create. In my two tables, Customers and Orders, there is a standard SharePoint lookup between the two. The Orders list has a lookup into the Customer list. When the data is imported into PowerPivot, it saves the ID of the Customer record, so the field doesn’t have the name of the Customer, like SharePoint does.

In the Create Relationship window, first specify the daughter list and field, and then specify the parent list and field.

Click Create, then click Close on the Manage Relationships window.

That does it! You have SharePoint lists in PowerPivot. Up until this point, we’ve been very specific with SharePoint lists. If you are working with different data sources (SQL Database, Excel file, etc.) you can import them and then follow along for the rest of this post.

Now let’s get some of this data into Excel. Click the Excel button in the PowerPivot Window Title bar.

In the PowerPivot ribbon, click the PivotTable button, and select PivotTable.

Select Existing Worksheet when prompted. Click OK.

Your Excel window now has a whole lot of more going on.

The right pane is all PowerPivot related, and creates the report and slicers.

From here, I want to create a report that lists all Customers with total number of orders, with a slicer for selecting order date’s month, quarter and year; and a slicer for country, and a slicer for region.

In the top window on the right, the PowerPivot Field List, check off all the fields you want to display. I suggest showing only one for now. I am going to select CompanyName. You’ll see that the data is populated and the field was added to the Row Labels at the bottom.

To create slicers, now we can drag the fields down into the either the Slicers Vertical or Slicers Horizontal boxes. Let’s start with the Customer fields. So I am going to drag Country and Region down into the Vertical box. Your new slicers will appear on the left.

Go ahead and try them. Click a value in either slicer box. When I click Canada, two things happen. The Region slicer and the list of Customers both filter. Pretty cool eh?

Click the Clear Filter at any point to reset the filter.

To get the date slicer in there, we need to manipulate the data a little. If you were to drag the Order Date field into the Horizontal box, it would load up every date. That doesn’t help. So now we’ll go back to PowerPivot and create our Month, Quarter and Year values. To go back to PowerPivot, click the PowerPivot Window button in the ribbon.

On my Orders list, I am going to scroll all the way to the right and click Add Column. In the box where the value would normally be, we can enter in a formula. So I am going to enter =MONTH([OrderDate]), which will get me the month value. Right click the column header to rename to OrderMonth. Getting the Quarter is a little more difficult, we’re going to use the formula =INT((([OrderMonth]-1)/3)+1). And I’ll throw in another one for the Year, using =YEAR([OrderDate]).

Now let’s jump back into Excel. Excel will automatically prompt you to Refresh the data, go ahead and click Refresh.

And now let’s drag down the three new fields into the Horizontal box. As you do, the slicers will appear above our list.

And finally, lets add the count of orders, drag the ID field of the Orders list to the Values box. Then click on the field in the box and select Summarize By > Count.

You can rename the field as well by selecting Edit Measure from the previous menu.

Now you can use any combination of slicers to analyze your data.

And now let’s add a chart!

In Excel, move to the right a little, and click the cell where you want the chart to start, top left.

Click the PivotTable button in the ribbon, then click PivotChart. Select Existing Worksheet, click OK.

I want to see total sales based on country and selected time frame. Drag Country into the Axis Fields box, drag the ID of the Orders list into the Values box, and again change it from Sum to Count. We now have a little bar chart.

By default, none of the slicers affect this chart. I now want the date slicers to filter the data in this chart. To add slicers to a chart, right click the slicer and select PivotTable Connections. You’ll see two pivot tables (real unique), but since there’s only two here, check off the 2nd one.

Do the same for each date slicer. Now when you filter your dates, your customer list and the chart will update.

And let’s do the same steps above, but I’m going to throw in one more chart to view order totals based on year, by selected country.

Now when I select a Country or Region, the company list and By Year chart updates. Selecting a Date updates Company and By Country chart.

Now that we have it ready to go, browse to a PowerPivot Gallery on SharePoint and upload this document. Once uploaded, you can now browse and interact with your PowerPivot within SharePoint.

PowerPivot in SharePoint